java jdbc连接各种数据库的方式大全 批量插入 存储过程调用

3/3/2017来源:C/C++教程人气:1804

java Database Connectivity (JDBC),用来与数据库打交道,本文主要总结了 jdbc与MySQL,Oracle,postgresql 连接的方式,并总结了 Statement, PReparedStatement , CallableStatement 以及事务 JDBC Transaction 的处理 JDBC连接 MYSQL 数据库  响应mysql jdbc的jar 包下载:http://dev.mysql.com/downloads/connector/j/ 程序代码 程序代码 Class.forName("com.mysql.jdbc.Driver"); Connection conn = null; conn = DriverManager.getConnection("jdbc:mysql://hostname:port/dbname","username", "passWord"); conn.close(); JDBC连接ORACLE 相关jar 包下载地址:http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html 程序代码 程序代码 Class.forName("org.postgresql.Driver"); Connection connection = null; connection = DriverManager.getConnection(     "jdbc:oracle:thin:@localhost:1521:yihaomen","username","password"); connection.close(); JDBC连接Postgresql 需要下载postgresql 的jdbc jar包:http://jdbc.postgresql.org/download.html 程序代码 程序代码 Class.forName("org.postgresql.Driver"); Connection connection = null; connection = DriverManager.getConnection(    "jdbc:postgresql://hostname:port/dbname","username", "password"); connection.close(); 上面介绍了 jdbc 连接几种主流数据库的方式,下面介绍各种SQL语句的处理方式, 用jdbc写程序要特别注意的是connection 的关闭等,因此要注意try catch等的作用,一般用如下模板得到connection,并处理SQL语句,以oracle连接为例子 程序代码 程序代码 import java.sql.DriverManager; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import java.text.DateFormat; import java.text.SimpleDateFormat; public class JDBCStatementInsertExample {     private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";     private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:yihaomen";     private static final String DB_USER = "user";     private static final String DB_PASSWORD = "password";     private static final DateFormat dateFormat = new SimpleDateFormat(             "yyyy/MM/dd HH:mm:ss");     public static void main(String[] argv) {         try {             insertRecordIntoDbUserTable();         } catch (SQLException e) {             System.out.println(e.getMessage());         }     }     private static void insertRecordIntoDbUserTable() throws SQLException {         Connection dbConnection = null;         Statement statement = null;         String insertTableSQL = "Insert INTO DBUSER"                 + "(USER_ID, USERNAME, CreateD_BY, CreateD_DATE) " + "VALUES"                 + "(1,'mkyong','system', " + "to_date('"                 + getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";         try {             dbConnection = getDBConnection();             statement = dbConnection.createStatement();             System.out.println(insertTableSQL);             // execute insert SQL stetement             statement.executeUpdate(insertTableSQL);             System.out.println("Record is inserted into DBUSER table!");         } catch (SQLException e) {             System.out.println(e.getMessage());         } finally {             if (statement != null) {                 statement.close();             }             if (dbConnection != null) {                 dbConnection.close();             }         }     }     private static Connection getDBConnection() {         Connection dbConnection = null;         try {             Class.forName(DB_DRIVER);         } catch (ClassNotFoundException e) {             System.out.println(e.getMessage());         }         try {             dbConnection = DriverManager.getConnection(                                DB_CONNECTION, DB_USER,DB_PASSWORD);             return dbConnection;         } catch (SQLException e) {             System.out.println(e.getMessage());         }         return dbConnection;     }     private static String getCurrentTimeStamp() {         java.util.Date today = new java.util.Date();         return dateFormat.format(today.getTime());     } } 上面的例子是插入数据库操作,其他的更新,增加等,都可以采用类似的方式。 程序代码 程序代码 Statement statement = dbConnection.createStatement(); // 执行插入语句 statement.executeUpdate(insertTableSQL); 或者用如下PreparedStatement方式 程序代码 程序代码 String insertTableSQL = "Insert INTO DBUSER"         + "(USER_ID, USERNAME, CreateD_BY, CreateD_DATE) VALUES"         + "(?,?,?,?)"; PreparedStatement preparedStatement = dbConnection.prepareStatement(insertTableSQL); preparedStatement.setInt(1, 11); preparedStatement.setString(2, "yihaomen"); preparedStatement.setString(3, "system"); preparedStatement.setTimestamp(4, getCurrentTimeStamp()); // execute insert SQL stetement preparedStatement .executeUpdate(); 执行删除语句 程序代码 程序代码 String deleteSQL = "Delete DBUSER Where USER_ID = ?"; PreparedStatement preparedStatement = dbConnection.prepareStatement(deleteSQL); preparedStatement.setInt(1, 1001); preparedStatement.executeUpdate(); 执行更新语句 程序代码 程序代码 String updateTableSQL = "Update DBUSER SET USERNAME = ? Where USER_ID = ?"; PreparedStatement preparedStatement = dbConnection.prepareStatement(updateTableSQL); preparedStatement.setString(1, "mkyong_new_value"); preparedStatement.setInt(2, 1001); preparedStatement .executeUpdate(); 得到List  记录,得到多条记录: 程序代码 程序代码 String selectSQL = "Select USER_ID, USERNAME FROM DBUSER Where USER_ID = ?"; PreparedStatement preparedStatement = dbConnection.prepareStatement(selectSQL); preparedStatement.setInt(1, 1001); ResultSet rs = preparedStatement.executeQuery(selectSQL ); while (rs.next()) {     String userid = rs.getString("USER_ID");     String username = rs.getString("USERNAME");     } 批量执行SQL语句,要启用 事务 程序代码 程序代码 dbConnection.setAutoCommit(false);  statement = dbConnection.createStatement(); statement.addBatch(insertTableSQL1); statement.addBatch(insertTableSQL2); statement.addBatch(insertTableSQL3);  statement.executeBatch();  dbConnection.commit(); 或者采用如下preparedStatement 方式 程序代码 程序代码 dbConnection.setAutoCommit(false);//commit trasaction manually String insertTableSQL = "Insert INTO DBUSER"             + "(USER_ID, USERNAME, CreateD_BY, CreateD_DATE) VALUES"             + "(?,?,?,?)";                 PreparedStatement = dbConnection.prepareStatement(insertTableSQL); preparedStatement.setInt(1, 101); preparedStatement.setString(2, "mkyong101"); preparedStatement.setString(3, "system"); preparedStatement.setTimestamp(4, getCurrentTimeStamp()); preparedStatement.addBatch(); preparedStatement.setInt(1, 102); preparedStatement.setString(2, "mkyong102"); preparedStatement.setString(3, "system"); preparedStatement.setTimestamp(4, getCurrentTimeStamp()); preparedStatement.addBatch(); preparedStatement.executeBatch(); dbConnection.commit(); jdbc调用存储过程 1. 只有输入参数的情况 程序代码 程序代码 String insertStoreProc = "{call insertDBUSER(?,?,?,?)}"; callableStatement = dbConnection.prepareCall(insertStoreProc); callableStatement.setInt(1, 1000); callableStatement.setString(2, "mkyong"); callableStatement.setString(3, "system"); callableStatement.setDate(4, getCurrentDate()); callableStatement.executeUpdate(); 2.调用存储过程,存储过程有返回值的情况 程序代码 程序代码 //getDBUSERByUserId is a stored procedure String getDBUSERByUserIdSql = "{call getDBUSERByUserId(?,?,?,?)}"; callableStatement = dbConnection.prepareCall(getDBUSERByUserIdSql); callableStatement.setInt(1, 10); callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR); callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR); callableStatement.registerOutParameter(4, java.sql.Types.DATE); // execute getDBUSERByUserId store procedure callableStatement.executeUpdate(); String userName = callableStatement.getString(2); String createdBy = callableStatement.getString(3); Date createdDate = callableStatement.getDate(4); 需要注意的是:通过 CallableStatement.registerOutParameter(index,sqlType) 注册返回的参数,最后通过CallableStatement.getDataType(index) 取得结果 3. 如果是 oracle 数据库,而存储过程返回的是 cursor 游标的处理方式 程序代码 程序代码 String getDBUSERCursorSql = "{call getDBUSERCursor(?,?)}"; callableStatement = dbConnection.prepareCall(getDBUSERCursorSql); callableStatement.setString(1, "mkyong"); callableStatement.registerOutParameter(2, oracleTypes.CURSOR); // execute getDBUSERCursor store procedure callableStatement.executeUpdate(); // get cursor and cast it to ResultSet rs = (ResultSet) callableStatement.getObject(2); // loop it like normal while (rs.next()) {     String userid = rs.getString("USER_ID");     String userName = rs.getString("USERNAME"); } 首先通过 CallableStatement.registerOutParameter(index,OracleTypes.CURSOR). 注册要返回的类型,最后用 callableStatement.getObject(index). 获取结果. 掌握了这些之后,几乎所有与jdbc操作相关的东西,都懂了,增删改查到事务,游标,存储过程的处理都懂了。