JDBC教程 - JDBC批處理

批處理可以將SQL語句分組為一個塊，并通過對數(shù)據(jù)庫的一次調(diào)用傳遞它們。

批處理過程減少了通信開銷量并提高了性能。

我們可以使用DatabaseMetaData.supportsBatchUpdates()方法以檢查數(shù)據(jù)庫是否支持批量更新處理。

Statement，PreparedStatement，和 CallableStatement 的 addBatch()用于向批處理添加單個語句。

executeBatch()用于執(zhí)行批處理，并返回一個整數(shù)數(shù)組。數(shù)組的每個元素表示相應update語句的更新計數(shù)。

我們可以使用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對象執(zhí)行批量更新

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中執(zhí)行批處理。

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();

  }
}