Making batch queries in JDBC applications

The IBM® Data Server Driver for JDBC and SQLJ provides a IBM Data Server Driver for JDBC and SQLJ-only DB2PreparedStatement interface that lets you perform batch queries on a homogeneous batch.

Procedure

To make batch queries using a single statement with several sets of input parameters, follow these basic steps:

  1. Invoke the prepareStatement method to create a PreparedStatement object for the SQL statement with input parameters.
  2. For each set of input parameter values:
    1. Execute PreparedStatement.setXXX methods to assign values to the input parameters.
    2. Invoke the PreparedStatement.addBatch method to add the set of input parameters to the batch.
  3. Cast the PreparedStatement object to a DB2PreparedStatement object, and invoke the DB2PreparedStatement.executeDB2QueryBatch method to execute the statement with all sets of parameters.
  4. In a loop, retrieve the ResultSet objects:
    1. Retrieve each ResultSet object.
    2. Retrieve all the rows from each ResultSet object.

Example

In the following code fragment, two sets of parameters are batched. A SELECT statement that takes one input parameter is then executed twice, once with each parameter value. The numbers to the right of selected statements correspond to the previously described steps.
java.sql.Connection con = java.sql.DriverManager.getConnection(url, properties);
java.sql.Statement s = con.createStatement();
// Clean up from previous executions
try {
  s.executeUpdate ("drop table TestQBatch");
}
catch (Exception e) {
}

// Create and populate a test table
s.executeUpdate ("create table TestQBatch (col1 int, col2 char(10))");
s.executeUpdate ("insert into TestQBatch values (1, 'test1')");
s.executeUpdate ("insert into TestQBatch values (2, 'test2')");
s.executeUpdate ("insert into TestQBatch values (3, 'test3')");
s.executeUpdate ("insert into TestQBatch values (4, 'test4')");
s.executeUpdate ("insert into TestQBatch values (1, 'test5')");
s.executeUpdate ("insert into TestQBatch values (2, 'test6')");

try {
  PreparedStatement pstmt =                              1 
    con.prepareStatement("Select * from TestQBatch where col1 = ?");
  pstmt.setInt(1,1);                                     2a 
  pstmt.addBatch();                                      2b 
  // Add some more values to the batch
  pstmt.setInt(1,2);
  pstmt.addBatch();
  pstmt.setInt(1,3);
  pstmt.addBatch();
  pstmt.setInt(1,4);
  pstmt.addBatch();
  ((com.ibm.db2.jcc.DB2PreparedStatement)pstmt).executeDB2QueryBatch();
                                                         3 
} catch(BatchUpdateException b) {
  // process BatchUpdateException
}
ResultSet rs;
while(pstmt.getMoreResults()) {                          4 
  rs = pstmt.getResultSet();                             4a 
  while (rs.next()) {                                    4b 
    System.out.print (rs.getInt (1) + "  ");
    System.out.println (rs.getString (2));
  }
  System.out.println();
  rs.close ();
}
// Clean up
s.close ();
pstmt.close ();
con.close ();