Making batch updates in JDBC applications

With batch updates, instead of updating rows of a 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.

About this task

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.

Procedure

To make batch updates, follow one of the following sets of steps.

  • 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:
      1. 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.
      2. 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® Data Server 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. If the batched statement is a searched UPDATE, searched DELETE, or MERGE statement, set the autocommit mode for the connection to false.
    2. Invoke the prepareStatement method to create a PreparedStatement object.
    3. For each set of input parameter values:
      1. Execute setXXX methods to assign values to the input parameters.
      2. Invoke the addBatch method to add the set of input parameters to the batch.
    4. Invoke the executeBatch method to execute the statements with all sets of parameters.
    5. If no errors occurred:
      1. Get the number of rows that were updated by each execution of the SQL statement from the array that the executeBatch invocation returns. The number of affected rows does not include rows that were affected by triggers or by referential integrity enforcement.

        If the following conditions are true, the IBM Data Server Driver for JDBC and SQLJ returns Statement.SUCCESS_NO_INFO (-2), instead of the number of rows that were affected by each SQL statement:

        • The application is connected to a subsystem that is in DB2® for z/OS® Version 8 new-function mode, or later.
        • The application is using Version 3.1 or later of the IBM Data Server Driver for JDBC and SQLJ.
        • The IBM Data Server Driver for JDBC and SQLJ uses multi-row INSERT operations to execute batch updates.

        This occurs because with multi-row INSERT, the database server executes the entire batch as a single operation, so it does not return results for individual SQL statements.

      2. 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 Data Server Driver for JDBC and SQLJ adds a commit method at the end of the batch.

      3. If the PreparedStatement object returns automatically generated keys, call DB2PreparedStatement.getDBGeneratedKeys to retrieve an array of ResultSet objects that contains the automatically generated keys.

        Check the length of the returned array. If the length of the returned array is 0, an error occurred during retrieval of the automatically generated keys.

    6. If errors occurred, process the BatchUpdateException.

Example

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 preps = conn.prepareStatement(    
    "UPDATE DEPT SET MGRNO=? WHERE DEPTNO=?");           2 
  ps.setString(1,mgrnum1);                               3a 
  ps.setString(2,deptnum1);
  ps.addBatch();                                         3b 

  ps.setString(1,mgrnum2);
  ps.setString(2,deptnum2);
  ps.addBatch();
  int [] numUpdates=ps.executeBatch();                   4 
  for (int i=0; i < numUpdates.length; i++) {            5a 
    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");
  }
  conn.commit();                                         5b 
} catch(BatchUpdateException b) {                        6 
  // process BatchUpdateException
}

In the following code fragment, a batched INSERT statement returns automatically generated keys.

import java.sql.*;
import com.ibm.db2.jcc.*;
…
Connection conn;
…
try {
…
  PreparedStatement ps = conn.prepareStatement(          2 
    "INSERT INTO DEPT (DEPTNO, DEPTNAME, ADMRDEPT) " +
    "VALUES (?,?,?)",
    Statement.RETURN_GENERATED_KEYS);
  ps.setString(1,"X01");                                 3a 
  ps.setString(2,"Finance");
  ps.setString(3,"A00");
  ps.addBatch();                                         3b 
  ps.setString(1,"Y01");                          
  ps.setString(2,"Accounting");
  ps.setString(3,"A00");
  ps.addBatch();                                  

  int [] numUpdates=preps.executeBatch();                4 

  for (int i=0; i < numUpdates.length; i++) {            5a 
    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");
  }
  conn.commit();                                         5b 
  ResultSet[] resultList = 
    ((DB2PreparedStatement)ps).getDBGeneratedKeys();     5c 
  if (resultList.length != 0) {
    for (i = 0; i < resultList.length; i++) {
        while (resultList[i].next()) {
          java.math.BigDecimal idColVar = rs.getBigDecimal(1);
                              // Get automatically generated key
                              // value
          System.out.println("Automatically generated key value = " 
            + idColVar);
        }
    }
  }
  else {
    System.out.println("Error retrieving automatically generated keys");
  }
} catch(BatchUpdateException b) {                        6 
  // process BatchUpdateException
}

In the following code fragment, a batched UPDATE statement returns automatically generated keys. The code names the DEPTNO column as an automatically generated key, updates two rows in the DEPT table in a batch, and retrieves the values of DEPTNO for the updated rows. The numbers to the right of selected statements correspond to the previously described steps. 

import java.sql.*;
import com.ibm.db2.jcc.*;
…
Connection conn;
…
String[] agkNames = {"DEPTNO"};
try {
…
  conn.setAutoCommit(false);                             1 
  PreparedStatement ps = conn.prepareStatement(          2 
    "UPDATE DEPT SET  DEPTNAME=? " +
    "WHERE DEPTNO=?",agkNames);
  ps.setString(1,"X01");                                 3a 
  ps.setString(2,"Planning");
  ps.addBatch();                                         3b 
  ps.setString(1,"Y01");                          
  ps.setString(2,"Bookkeeping");
  ps.addBatch();                                  

  int [] numUpdates=ps.executeBatch();                   4 

  for (int i=0; i < numUpdates.length; i++) {            5a 
    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");
  }
  conn.commit();                                         5b 
  ResultSet[] resultList = 
    ((DB2PreparedStatement)ps).getDBGeneratedKeys();     5c 
  if (resultList.length != 0) {
    for (i = 0; i < resultList.length; i++) {
        while (resultList[i].next()) {
          String deptNoKey = rs.getString(1);
                              // Get automatically generated key
                              // value
          System.out.println("Automatically generated key value = " 
            + deptNoKey);
        }
    }
  }
  else {
    System.out.println("Error retrieving automatically generated keys");
  }
} 
catch(BatchUpdateException b) {                          6 
  // process BatchUpdateException
}