Implementing Batch updates in JDBC

With batch updates, instead of updating rows of a DB table one at a time, you can direct JDBC to execute a group of updates at the same time. Statements that can be included in the same batch of updates are known as batchable statements.
If a statement has input parameters or host expressions, you can include that statement only in a batch that has other instances of the same statement. This type of batch is known as a homogeneous batch. If a statement has no input parameters, you can include that statement in a batch only if the other statements in the batch have no input parameters or host expressions. This type of batch is known as a heterogeneous batch. Two statements that can be included in the same batch are known as batch compatible.
Use the following Statement methods for creating, executing, and removing a batch of SQL updates:
• addBatch
• executeBatch
• clearBatch
Use the following PreparedStatement and CallableStatement method for creating a batch of parameters so that a single statement can be executed multiple times in a batch, with a different set of parameters for each execution.
• addBatch
Restrictions on executing statements in a batch:
• If you try to execute a SELECT statement in a batch, a BatchUpdateException is thrown.
• A CallableStatement object that you execute in a batch can contain output parameters. However, you cannot retrieve the values of the output parameters. If you try to do so, a BatchUpdateException is thrown.
• You cannot retrieve ResultSet objects from a CallableStatement object that you execute in a batch. A BatchUpdateException is not thrown, but the getResultSet method invocation returns a null value.
To make batch updates using several statements with no input parameters, follow these basic steps:
1. For each SQL statement that you want to execute in the batch, invoke the addBatch method.
2. Invoke the executeBatch method to execute the batch of statements.
3. Check for errors. If no errors occurred:
a. Get the number of rows that were affect by each SQL statement from the array that the executeBatch invocation returns. This number does not include rows that were affected by triggers or by referential integrity enforcement.
b. If AutoCommit is disabled for the Connection object, invoke the commit method to commit the changes.
If AutoCommit is enabled for the Connection object, the IBM DB2 Driver for JDBC and SQLJ adds a commit method at the end of the batch.
To make batch updates using a single statement with several sets of input parameters, follow these basic steps:
1. Invoke the createStatement method to create a Statement object.
2. For each set of input parameter values:
a. Execute setXXX methods to assign values to the input parameters.
b. Invoke the addBatch method to add the set of input parameters to the batch.
3. Invoke the executeBatch method to execute the statements with all sets of parameters.
4. Check for errors. If no errors occurred:
a. Get the number of rows that were updated by each execution of the SQL statement from the array that the executeBatch invocation returns.
b. If AutoCommit is disabled for the Connection object, invoke the commit method to commit the changes.
If AutoCommit is enabled for the Connection object, the IBM DB2 Driver for JDBC and SQLJ adds a commit method at the end of the batch.
In the following code fragment, two sets of parameters are batched. An UPDATE statement that takes two input parameters is then executed twice, once with each set of parameters. The numbers to the right of selected statements correspond to the previously-described steps.
try {


PreparedStatement prepStmt = con.prepareStatement(
“UPDATE DEPT SET MGRNO=? WHERE DEPTNO=?”);
prepStmt.setString(1,mgrnum1);
prepStmt.setString(2,deptnum1);
prepStmt.addBatch();

prepStmt.setString(1,mgrnum2);
prepStmt.setString(2,deptnum2);
prepStmt.addBatch();
int [] numUpdates=prepStmt.executeBatch();
for (int i=0; i < numUpdates.length; i++) {
if (numUpdates[i] == SUCCESS_NO_INFO)
System.out.println(“Execution ” + i +
“: unknown number of rows updated”);
else
System.out.println(“Execution ” + i +
“successful: ” numUpdates[i] + ” rows updated”);
}
con.commit();
} catch(BatchUpdateException b) {
// process BatchUpdateException
}

Multi-row INSERT: When you execute multiple INSERT statements in a batch, and the database server supports multi-row INSERT, the IBM DB2 Driver for JDBC and SQLJ uses multi-row INSERT to insert the rows. Multi-row INSERT can provide better performance than individual INSERT statements.
You cannot execute a multi-row insert operation by including a multi-row INSERT statement in your JDBC application.

Scroll to Top