One of the more advanced features of JDBC 2.0 is the ability to submit multiple update statements to the database for processing as a single unit. This batch updating can be significantly more efficient compared to JDBC 1.0, where each update statement has to be executed separately.
Consider the following code segment demonstrating a batch update:
try {
dbCon.setAutoCommit(false);
Statement stmt= dbCon.createStatement();
stmt.addBatch("INSERT INTO bugs "+"VALUES (1007, 'Server stack overflow', 1,2,{d '1999-01-01'})");
stmt.addBatch("INSERT INTO bugs "+"VALUES (1008,'Cannot load DLL', 3,1,{d '1999-01-01'})");
stmt.addBatch("INSERT INTO bugs "+"VALUES (1009,'Applet locks up',2,2,{d '1999-01-01'})");
int[] updCnt = stmt.executeBatch();
dbCon.commit();
} catch (BatchUpdateException be) {
//handle batch update exception
int[] counts = be.getUpdateCounts();
for (int i=0; I counts.length; i++) {
System.out.println("Statement["+i+"] :"+counts[i]);
}
dbCon.rollback();
} catch (
SQLException e) {
//handle SQL exception
dbCon.rollback();
}
Before carrying out a batch update, it is important to disable the auto-commit mode by calling setAutoCommit(false). This way, you will be able to rollback the batch transaction in case one of the updates fail for any reason. When the Statement object is created, it is automatically associated a "command list", which is initially empty. We then add our SQL update statements to this command list, by making successive calls to the addBatch() method. On calling executeBatch(), the entire command list is sent over to the database, and are then executed in the order they were added to the list. If all the commands in the list are executed successfully, their corresponding update counts are returned as an array of integers. Please note that you always have to clear the existing batch by calling clearBatch() before creating a new one.
If any of the updates fail to execute within the database, a BatchUpdateException is thrown in response to it. In case there is a problem in returning the update counts of each SQL statement, a SQLException will be thrown to indicate the error.
