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,
Statement
objects submit updates to the database individually with the methodexecuteUpdate
. MultipleexecuteUpdate
statements 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
,PreparedStatement
andCallableStatement
, have the same capabilities, using their own version ofexecuteUpdate
.With the JDBC 2.0 API,
Statement
,PreparedStatement
, andCallableStatement
objects 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 tableCOFFEES
has five columns: columnCOF_NAME
of typeVARCHAR
(32), columnSUP_ID
of typeINTEGER
, columnPRICE
of typeFLOAT
, columnSALES
of typeINTEGER
, and columnTOTAL
of 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
Connection
objectcon
so that the transaction will not be automatically committed or rolled back when the methodexecuteBatch
is 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
Statement
objectstmt
. As is true of all newly-createdStatement
objects,stmt
has 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 allINSERT
INTO
statements, 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 columnsSALES
andTOTAL
, all start out being zero because there have been no sales yet. (SALES
is the number of pounds of this row's coffee sold in the current week;TOTAL
is the total of all the cumulative sales of this coffee.)int [] updateCounts = stmt.executeBatch();In this line,
stmt
sends the four SQL commands that were added to its list of commands off to the database to be executed as a batch. Note thatstmt
uses the methodexecuteBatch
to 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
updateCounts
should contain four elements of typeint
. In this case, eachint
will be1
because an insertion affects one row. The list of commands associated withstmt
will now be empty because the four commands added previously were sent to the database whenstmt
called 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:
SQLException
andBatchUpdateException
.All methods in the JDBC API will throw an
SQLException
object when there is a database access problem. In addition, the methodexecuteBatch
will throw anSQLException
if you have used the methodaddBatch
to add a command that returns a result set to the batch of commands being executed. Typically a query (aSELECT
statement) will return a result set, but some methods, such as some of theDatabaseMetaData
methods can also return a result set.Just using the method
addBatch
to 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 aStatement
object's command list. But there will be a problem when the methodexecuteBatch
submits 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 theexecuteBatch
method. Trying to put a result set in an array of update counts will cause an error and causeexecuteBatch
to throw anSQLException
. In other words, only commands that return an update count (commands such asINSERT
INTO
,UPDATE
,DELETE
,CREATE
TABLE
,DROP
TABLE
,ALTER
TABLE
, and so on) can be executed as a batch with theexecuteBatch
method.If no
SQLException
was 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 methodexecuteBatch
will 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.
BatchUpdateException
is derived fromSQLException
. This means that you can use all of the methods available to anSQLException
object with it. The following code fragment prints theSQLException
information and the update counts contained in aBatchUpdateException
object. BecausegetUpdateCounts
returns an array ofint
, it uses afor
loop 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 twocatch
blocks at the end of the application. If there is aBatchUpdateException
object, the firstcatch
block will catch it. The second one will catch anSQLException
object that is not aBatchUpdateException
object.
Start of Tutorial > Start of Trail > Start of Lesson |
Search
Feedback Form |
Copyright 1995-2005 Sun Microsystems, Inc. All rights reserved.