The Java TM Tutorial

| Start of Tutorial > Start of Trail > Start of Lesson | Search

Trail: JDBC Database Access
Lesson: New Features in the JDBC 2.0 API

## Making Batch Updates

> 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.

### Using Statement Objects for Batch Updates

> In the JDBC 1.0 API, `Statement` objects submit updates to the database individually with the method `executeUpdate` . Multiple `executeUpdate` 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 from `Statement` , `PreparedStatement` and `CallableStatement` , have the same capabilities, using their own version of `executeUpdate` .

> With the JDBC 2.0 API, `Statement`, `PreparedStatement`, and `CallableStatement` 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 table `COFFEES` has five columns: column `COF_NAME` of type `VARCHAR` (32), column `SUP_ID` of type `INTEGER` , column `PRICE` of type `FLOAT` , column `SALES` of type `INTEGER` , and column `TOTAL` of type `INTEGER` . 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` object `con` so that the transaction will not be automatically committed or rolled back when the method `executeBatch` 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` object `stmt` . As is true of all newly-created `Statement` 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 all `INSERT` `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 columns `SALES` and `TOTAL` , 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 salesof 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 that `stmt` uses the method `executeBatch` to send the batch of insertions, not the method `executeUpdate` , 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 of `int` , `updateCounts` .

> At this point `updateCounts` should contain four elements of type `int` . In this case, each `int` will be `1` because an insertion affects one row. The list of commands associated with `stmt` will now be empty because the four commands added previously were sent to the database when `stmt` called the method `executeBatch` . You can at any time empty this list of commands with the method `clearBatch` .

### Batch Update Exceptions

> There are two exceptions that can be thrown during a batch update operation: `SQLException` and `BatchUpdateException` .

> All methods in the JDBC API will throw an `SQLException` object when there is a database access problem. In addition, the method `executeBatch` will throw an `SQLException` if you have used the method `addBatch` to add a command that returns a result set to the batch of commands being executed. Typically a query (a `SELECT` statement) will return a result set, but some methods, such as some of the `DatabaseMetaData` 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 a `Statenment` object's command list. But there will be a problem when the method `executeBatch` 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 the `executeBatch` method. Trying to put a result set in an array of update counts will cause an error and cause `executeBatch` to throw an `SQLException` . In other words, only commands that return an update count (commands such as `INSERT` `INTO` , `UPDATE` , `DELETE` , `CREATE` `TABLE` , `DROP` `TABLE` , `ALTER` `TABLE` , and so on) can be executed as a batch with the `executeBatch` 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 method `executeBatch` will throw a `BatchUpdateException` . 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 from `SQLException` . This means that you can use all of the methods available to an `SQLException` object with it. The following code fragment prints the `SQLException` information and the update counts contained in a `BatchUpdateException` object. Because `getUpdateCounts` returns an array of `int` , it uses a `for` 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 two `catch` blocks at the end of the application. If there is a `BatchUpdateException` object, the first `catch` block will catch it. The second one will catch an `SQLException` object that is not a `BatchUpdateException` object.

| Start of Tutorial > Start of Trail > Start of Lesson | Search