JDBC Batch updates

If you shopped for your groceries by going to the store, purchasing one item from your list, returning home, then going back to the store for the next item, repeating the cycle until you had purchased everything on the list, you would say that the process was pretty inefficient and silly. Yet, this is essentially what you do with the usual style of JBDC calls. You go to the database with a request, get the answer, and then go back with another. All the way through your "shopping list" of SQL statements.
Batch processing is a way to give the database a collection of SQL commands as a group. This cuts down on those "back and forth" trips your program otherwise might make. As you might guess, this is more efficient than making a large number of those single trips.

Batch processing in JDBC allows multiple SQL statements to be executed together as a batch, which can provide significant performance improvements for certain types of operations. To use batch processing to execute JDBC statements, follow these general steps:

1. Create a PreparedStatement object for the SQL statement to be executed.
2. Set the parameter values for the statement as needed.
3. Add the statement to a batch using the addBatch() method on the PreparedStatement object.
4. Repeat steps 1-3 for each SQL statement to be executed in the batch.
5. Execute the batch using the executeBatch() method on the PreparedStatement object.
6. Process the results of the batch, if needed.

Here's an example of how batch processing can be used to execute multiple INSERT statements in JDBC:

Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO my_table (col1, col2) VALUES (?, ?)");

for (int i = 0; i < 10; i++) {
    pstmt.setInt(1, i);
    pstmt.setString(2, "Value " + i);
    pstmt.addBatch();
}

int[] updateCounts = pstmt.executeBatch();
conn.commit();

pstmt.close();
conn.close();

In this example, a single PreparedStatement object is used to execute 10 INSERT statements in a batch. The addBatch() method is called for each statement to add it to the batch, and the executeBatch() method is called to execute the batch. The executeBatch() method returns an array of integers indicating the number of rows affected by each statement in the batch. Finally, the transaction is committed and the PreparedStatement object and Connection object are closed.
Batch processing can be especially useful when performing large numbers of repetitive or similar operations, as it can reduce the overhead of repeatedly executing individual statements. However, it's important to be aware of the potential risks of batch processing, such as increased memory usage and the potential for partial completion of the batch.

The addBatch() method on a Statement (or any Statement descendant) adds the SQL statement provided as an argument to the current group of batched statements. It allows you to collect a list of SQL statements and then send them off all together for processing. The method executeBatch() submits the collected list, and clearBatch() empties it without sending to the database. Not every database driver implements batch processing, you can use DatabaseMetaData.supportsBatchUpdates() to check a driver to see if it does.

Error handling and reporting is a little more complicated for batch processing. executeBatch() returns an array. The individual elements of the array are a count of the updates for the batch statements. The order of the elements corresponds to the order of the batch statements--the first statement in the batch has its results listed first in this array. If all goes well and the database is able to process all the requests in the batch, the counts are greater than or equal to zero. The counts indicate how many rows were affected by the particular SQL statement. If there are errors, the return value might be one of two settings, depending on the driver. The driver has the option of either stopping processing or continuing processing. If the driver continues processing after an error is found, then the return value in the array will be the constant EXECUTE_FAILED for the statement that failed. If the driver does stop processing, the SUCCESS_NO_INFO constant will be in the array. The driver will also raise a BatchUpdateException. The array that would otherwise be returned (if the exception hadn't been raised) can be obtained with the method BatchUpdateException.getUpdateCounts().
The following Slide Show illustrates an example that adds a phone number column to the hospital table and then updates two of the rows in the table:

Click the Exercise link to check your ability to use batch updates and PreparedStatements to create an additional table.
Batch Updates - Exercise

Click the Quiz link below to test your knowledge of batch updates and prepared statements.
Batch Updates Prepared Statements - Quiz