|
|
Start of Tutorial > Start of Trail > Start of Lesson |
Search
Feedback Form |
A batch update is a set of multiple update statements that is submitted to the database for processing as a batch. Sending multiple update statements to the database together as a unit can, in some situations, be much more efficient than sending each update statement separately. This ability to send updates as a unit, referred to as the batch update facility, is one of the features provided with the JDBC 2.0 API.
In the JDBC 1.0 API,
Statementobjects submit updates to the database individually with the methodexecuteUpdate. MultipleexecuteUpdatestatements can be sent in the same transaction, but even though they are committed or rolled back as a unit, they are still processed individually. The interfaces derived fromStatement,PreparedStatementandCallableStatement, have the same capabilities, using their own version ofexecuteUpdate.With the JDBC 2.0 API,
Statement,PreparedStatement, andCallableStatementobjects have the ability to maintain a list of commands that can be submitted together as a batch. They are created with an associated list, which is initially empty. You can add SQL commands to this list with the method addBatch, and you can empty the list with the method clearBatch. You send all of the commands in the list to the database with the method executeBatch. Now let's see how these methods work.Let's suppose that our coffee house proprietor wants to start carrying flavored coffees. He has determined that his best source is one of his current suppliers, Superior Coffee, and he wants to add four new coffees to the table
COFFEES. Because he is inserting only four new rows, a batch update may not improve performance significantly, but this is a good opportunity to demonstrate batch updates. Remember that the tableCOFFEEShas five columns: columnCOF_NAMEof typeVARCHAR(32), columnSUP_IDof typeINTEGER, columnPRICEof typeFLOAT, columnSALESof typeINTEGER, and columnTOTALof typeINTEGER. Each row he inserts will have values for the five columns in order. The code for inserting the new rows as a batch might look similar to this:con.setAutoCommit(false); Statement stmt = con.createStatement(); stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto', 49, 9.99, 0, 0)"); stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut', 49, 9.99, 0, 0)"); stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)"); stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)"); int [] updateCounts = stmt.executeBatch();Now let's examine the code line by line.
con.setAutoCommit(false);This line disables auto-commit mode for the
Connectionobjectconso that the transaction will not be automatically committed or rolled back when the methodexecuteBatchis called. (If you do not recall what a transaction is, you should review the sections Disabling Auto-commit Mode and Committing a Transaction .) To allow for correct error handling, you should always disable auto-commit mode before beginning a batch update.Statement stmt = con.createStatement();This line of code creates the
Statementobjectstmt. As is true of all newly-createdStatementobjects,stmthas a list of commands associated with it, and that list is empty.stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto', 49, 9.99, 0, 0)"); stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut', 49, 9.99, 0, 0)"); stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)"); stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");Each of these lines of code adds a command to the list of commands associated with
stmt. These commands are allINSERTINTOstatements, each one adding a row consisting of five column values. The values for the columns COF_NAME and PRICE are self-explanatory. The second value in each row is 49 because that is the identification number for the supplier, Superior Coffee. The last two values, the entries for the columnsSALESandTOTAL, all start out being zero because there have been no sales yet. (SALESis the number of pounds of this row's coffee sold in the current week;TOTALis the total of all the cumulative sales of this coffee.)int [] updateCounts = stmt.executeBatch();In this line,
stmtsends the four SQL commands that were added to its list of commands off to the database to be executed as a batch. Note thatstmtuses the methodexecuteBatchto send the batch of insertions, not the methodexecuteUpdate, which sends only one command and returns a single update count. The DBMS will execute the commands in the order in which they were added to the list of commands, so it will first add the row of values for Amaretto, then add the row for Hazelnut, then Amaretto decaf, and finally Hazelnut decaf. If all four commands execute successfully, the DBMS will return an update count for each command in the order in which it was executed. The update counts, which indicate how many rows were affected by each command, are stored in the array ofint,updateCounts.At this point
updateCountsshould contain four elements of typeint. In this case, eachintwill be1because an insertion affects one row. The list of commands associated withstmtwill now be empty because the four commands added previously were sent to the database whenstmtcalled the methodexecuteBatch. You can at any time empty this list of commands with the methodclearBatch.
There are two exceptions that can be thrown during a batch update operation:
SQLExceptionandBatchUpdateException.All methods in the JDBC API will throw an
SQLExceptionobject when there is a database access problem. In addition, the methodexecuteBatchwill throw anSQLExceptionif you have used the methodaddBatchto add a command that returns a result set to the batch of commands being executed. Typically a query (aSELECTstatement) will return a result set, but some methods, such as some of theDatabaseMetaDatamethods can also return a result set.Just using the method
addBatchto add a command that produces a result set does not cause an exception to be thrown. There is no problem while the command is just sitting in aStatementobject's command list. But there will be a problem when the methodexecuteBatchsubmits the batch to the DBMS to be executed. When each command is executed, it must return an update count that can be added to the array of update counts returned by theexecuteBatchmethod. Trying to put a result set in an array of update counts will cause an error and causeexecuteBatchto throw anSQLException. In other words, only commands that return an update count (commands such asINSERTINTO,UPDATE,DELETE,CREATETABLE,DROPTABLE,ALTERTABLE, and so on) can be executed as a batch with theexecuteBatchmethod.If no
SQLExceptionwas thrown, you know that there were no access problems and that all of the commands produce update counts. If one of the commands cannot be executed for some other reason, the methodexecuteBatchwill throw aBatchUpdateException. In addition to the information that all exceptions have, this exception contains an array of the update counts for the commands that executed successfully before the exception was thrown. Because the update counts are in the same order as the commands that produced them, you can tell how many commands were successful and which commands they are.
BatchUpdateExceptionis derived fromSQLException. This means that you can use all of the methods available to anSQLExceptionobject with it. The following code fragment prints theSQLExceptioninformation and the update counts contained in aBatchUpdateExceptionobject. BecausegetUpdateCountsreturns an array ofint, it uses aforloop to print each of the update counts.try { // make some updates } catch(BatchUpdateException b) { System.err.println("SQLException: " + b.getMessage()); System.err.println("SQLState: " + b.getSQLState()); System.err.println("Message: " + b.getMessage()); System.err.println("Vendor: " + b.getErrorCode()); System.err.print("Update counts: "); int [] updateCounts = b.getUpdateCounts(); for (int i = 0; i < updateCounts.length; i++) { System.err.print(updateCounts[i] + " "); } }For the complete Batch Update program, see
BatchUpdate.java. The code puts together the code fragments from previous sections to make a complete program. One thing you might notice is that there are two
catchblocks at the end of the application. If there is aBatchUpdateExceptionobject, the firstcatchblock will catch it. The second one will catch anSQLExceptionobject that is not aBatchUpdateExceptionobject.
|
|
Start of Tutorial > Start of Trail > Start of Lesson |
Search
Feedback Form |
Copyright 1995-2004 Sun Microsystems, Inc. All rights reserved.