JDBC 批处理

JDBC教程 - JDBC批处理

批处理可以将SQL语句分组为一个块，并通过对数据库的一次调用传递它们。

批处理过程减少了通信开销量并提高了性能。

我们可以使用DatabaseMetaData.supportsBatchUpdates()方法以检查数据库是否支持批量更新处理。

Statement，PreparedStatement，和 CallableStatement 的 addBatch()用于向批处理添加单个语句。

executeBatch()用于执行批处理，并返回一个整数数组。数组的每个元素表示相应update语句的更新计数。

我们可以使用clearBatch()方法删除使用addBatch()方法添加的语句。

以下代码显示如何使用Statement对象进行批量更新。

Statement stmt = conn.createStatement();
conn.setAutoCommit(false);

String SQL = "INSERT INTO Employees VALUES(2,"name")";
stmt.addBatch(SQL);
SQL = "INSERT INTO Employees VALUES(2,"new name")";
stmt.addBatch(SQL);
SQL = "UPDATE Employees SET age = 5 WHERE id = 1";
stmt.addBatch(SQL);
int[] count = stmt.executeBatch();
conn.commit();

使用PrepareStatement对象进行批处理

以下代码显示如何使用PreparedStatement对象执行批量更新

String SQL = "INSERT INTO Employees (id, firstName)VALUES(?, ?)";
PreparedStatemen pstmt = conn.prepareStatement(SQL);

//Set auto-commit to false
conn.setAutoCommit(false);

// Set the variables
pstmt.setInt( 1, 101 );
pstmt.setString( 2, "name" );
// Add it to the batch
pstmt.addBatch();

// Set the variables
pstmt.setInt( 1, 102 );
pstmt.setString( 2, "new name" );
// Add it to the batch
pstmt.addBatch();

//add more batches
//...
//Create an int[] to hold returned values
int[] count = stmt.executeBatch();

//Explicitly commit statements to apply changes
conn.commit();

例子

以下代码是一个完整的可运行示例，显示如何在JDBC中执行批处理。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Main {
  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase";
  private static final String DB_USER = "user";
  private static final String DB_PASSWORD = "password";

  public static void main(String[] argv) throws Exception {
    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;
    Class.forName(DB_DRIVER);
    dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
        DB_PASSWORD);

    String insertTableSQL = "INSERT INTO Person"
        + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
        + "(?,?,?,?)";
    preparedStatement = dbConnection.prepareStatement(insertTableSQL);

    dbConnection.setAutoCommit(false);

    java.util.Date today = new java.util.Date();

    preparedStatement.setInt(1, 101);
    preparedStatement.setString(2, "101");
    preparedStatement.setString(3, "system");
    preparedStatement.setTimestamp(4,new java.sql.Timestamp(today.getTime()));
    preparedStatement.addBatch();

    preparedStatement.setInt(1, 102);
    preparedStatement.setString(2, "102");
    preparedStatement.setString(3, "system");
    preparedStatement.setTimestamp(4,new java.sql.Timestamp(today.getTime()));
    preparedStatement.addBatch();

    preparedStatement.setInt(1, 103);
    preparedStatement.setString(2, "103");
    preparedStatement.setString(3, "system");
    preparedStatement.setTimestamp(4,new java.sql.Timestamp(today.getTime()));
    preparedStatement.addBatch();

    preparedStatement.executeBatch();

    dbConnection.commit();

    preparedStatement.close();
    dbConnection.close();

  }
}