Does a Database Support Batching? JDBC

With batch updating, a set of SQL statements is assembled and then sent to the database for execution. Batch updating can improve performance if you send lots of update statements to the database. According to Sun’s JDBC Tutorial , “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.”

Determine Whether a Database Supports Batching

Making Batch Updates

Next I’ll provide an example that will perform batch updates. This example will be accomplished in several steps:

Step 1: Setting up the database
Step 2: Developing a sample program for batch updating
Step 3: Running the sample program
Step 4: Verifying the database results
Step 5: Discussing the solution

Step 1: Setting up the Database

Let’s create a simple table, which will perform batch updates.

Step 2: Developing a Sample Program for Batch Updating

Here is the solution for batch updates. For discussion purposes, I have added line numbers.


Step 3: Running the Sample Program

Step 4:Verifying the Database Results

Step 5:Discussing the Solution

Let’s look at this solution in detail:

Lines 1–6: Import required classes and interfaces from the java.sql package.

Lines 10–17: The getConnection() method loads the JDBC driver, and then creates and returns a new database Connection object.

Lines 24–35: With the JDBC 2.0 API, Statement, PreparedStatement, and CallableStatement objects have the ability to maintain a list of SQL 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(). In lines 32–33, the stmt object sends the three 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 database server will execute the SQL commands in the order in which they were added to the list of commands.

Lines 36–43: The ResultSet object is used to retrieve all records from the batch_table.The ResultSet object is iterated to get information from all of the rows.

Lines 45–61: There are two exceptions that can be thrown during a batch update operation: SQLException and BatchUpdateException. If a batch update fails, then BatchUpdateException will be thrown by the JDBC driver. If there are other database problems, then SQL Exception will be thrown.

Lines 62–65: Finally, if there is any other exception, java.lang.Exception will be thrown.

Lines 66–70: This code closes all database resources. It releases the database and JDBC resources immediately instead of waiting for them to be automatically released.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

JDBC Topics