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

2019-11-06 08:59:23

字体：大中小

来源：转载

供稿：网友

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 stetementpreparedStatement .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 manuallyString 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 procedureString 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 procedurecallableStatement.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 procedurecallableStatement.executeUpdate();// get cursor and cast it to ResultSetrs = (ResultSet) callableStatement.getObject(2);// loop it like normalwhile (rs.next()) { String userid = rs.getString("USER_ID"); String userName = rs.getString("USERNAME");}首先通过 CallableStatement.registerOutParameter(index,OracleTypes.CURSOR). 注册要返回的类型，最后用 callableStatement.getObject(index). 获取结果.掌握了这些之后，几乎所有与jdbc操作相关的东西，都懂了，增删改查到事务，游标，存储过程的处理都懂了。

上一篇：在java中获取ServletContext的常见方法

下一篇：JAVA 单例模式与hashmap结合实现缓存