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