JDBC Batch Processing

JDBC Batch Processing

JDBC Batch Processing basically permits the user to group related SQL statements into a batch and then submit them with only one call to the database.

In batch processing, whenever the programmer sends several SQL statements to the database at once then the programmer will reduce the amount of communication overhead that will result in the enhanced performance.

• The JDBC drivers are basically not required to support this feature. The programmer must use the DatabaseMetaData.supportsBatchUpdates() method in order to determine in case the target database supports batch update processing. In case the programmer's JDBC driver supports this feature this method will return true.
• The addBatch() method of the Statement, PreparedStatement, and CallableStatement is generally used in order to add the individual statements to the batch. The executeBatch() is usually used in order to start the execution of all the statements that have been grouped together.
• The executeBatch() is the method that is used to return an array of integers and each of the element of the array basically represents the update count that is for the respective update statement.
• Similarly to the process by which the programmer can add statements to a batch for processing the programmer can remove them with the use of the clearBatch() method. This method is generally known to remove all the statements that has been added by the programmer with the addBatch() method. However, the programmer cannot selectively choose the statement that they want to remove.

Batching with Statement Object

Here is a typical sequence of the steps that can be used to use Batch Processing with the Statement Object as followed:

• The user have to create a Statement object by the help of either createStatement() methods.
• Then the user have to set auto-commit to false by the help of setAutoCommit().
• After the previous step, the programmer can add as many as SQL statements as they like into the batch by the help of addBatch() method on created statement object.
• Now the user have to just execute all the SQL statements by the help of executeBatch() method on created statement object.
• At the last step, the programmer just have to commit all the changes by the help of commit() method.

Here is an example of the above mentioned concept that will depict the process of batch update using Statement object:

//First Create statement object
Statement stmt = conn.createStatement();

//Here Set auto-commit to false
conn.setAutoCommit(false);

//Create SQL statement
String SQL = "INSERT INTO Students(id, firstname, lastname, age) " +
             "VALUES(100,'abhi', 'kumar', 30)";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);

// Create one more SQL statement
String SQL = "INSERT INTO Students (id, firstname, lastname, age) " +
             "VALUES(101,'Rexx', 'Singh', 35)";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);

// Create one more SQL statement
String SQL = "UPDATE Students SET age = 35 " +
             "WHERE id = 100";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);

// Create an int[] to hold returned values
int[] count = stmt.executeBatch();

//Explicitly commit statements to apply changes
conn.commit();

Batching with PrepareStatement Object

Here is a typical sequence of the steps that can be used to use Batch Processing with the PrepareStatement Object as followed:

• The programmers have to create SQL statements with the placeholders in the first step.
• In the second step, the programmer have to create PrepareStatement object by the help of either prepareStatement() methods.
• After the previous step the user have to set auto-commit to false by the help of setAutoCommit().
• In this step, the programmer can just add as many as SQL statements as the like into the batch by the help of addBatch() method on created statement object.
• This very step involves the execution of0 all the SQL statements by the help of executeBatch() method on created statement object.
• At the last step the programmer have to commit all the changes with the help of commit() method.

Here is an example of the above mentioned concept that will be depicting the process of a batch update using PrepareStatement object:

// Create SQL statement
String SQL = "INSERT INTO Students (id, firstname, lastname, age) " +
             "VALUES(?, ?, ?, ?)";

// Create PrepareStatement object
PreparedStatemen pstmt = conn.prepareStatement(SQL);

//Set auto-commit to false
conn.setAutoCommit(false);

// Set the variables
pstmt.setInt( 1, 100 );
pstmt.setString( 2, "Abhi" );
pstmt.setString( 3, "kumar" );
pstmt.setInt( 4, 30 );
// Add it to the batch
pstmt.addBatch();

// Set the variables
pstmt.setInt(1, 101 );
pstmt.setString(2, "Rexx" );
pstmt.setString(3, "Singh" );
pstmt.setInt(4, 35 );
// Add it to the batch
pstmt.addBatch();

//add more batches
.
.
.
//Create an int[] to hold returned values
int[] count = stmt.executeBatch();

//Explicitly commit statements to apply changes
conn.commit();