1. Introduction

This article presents a simple example of performing JDBC Batch Update. It assumes that the reader is familiar with the JDBC (Java DataBase Connectivity) API which is just one of the tools in Java for connecting to a database from a client. The API provides several simple methods for querying and updating data in a database.
 
 
 
 
 

 
Consider a situation in which hundreds or more database records need to be affected, that is updated, inserted or deleted. Making a network call for each operation could mean more time, more network traffic, more communication overhead which could adversely hit performance. This is when “Batch Update” comes into the picture. ‘Batch Operation’ implies grouping a certain chunk of operations into one unit.

The official page reads: “The batch update facility allows a Statement object to submit a set of heterogeneous SQL statements together as a single unit, or batch, to the underlying data source.” What it means is, if there are say, 200 database operations that need to be performed then instead of each operation hitting the database once we can have the 200 operations ‘batched’ into say, 4 batches of 50 operations each. Thus the database would be hit just 4 times instead of the earlier 200 times. The JDBC specification limits the number of operations in a Batch to a max size of 100 but individual databases may have their own limits.

This article demonstrates the use of JDBC Batch Update operation. It talks about the two ways in which ‘Batch Update’ can be done usingStatement andPreparedStatement objects. Though the example revolves aroundupdate;delete andinsert operations can also be ‘batched’. Of course, batchingselect statements does not make much sense. One thing to be mindful about is that the database executes each operation in the batch separately. So what happens if any one of the operations in the ‘batch’ fails? Well that might leave the database in an inconsistent state. How should that be handled? This is when ‘transactions’ come to the rescue. It is a feature of theConnection object. We are going to run our Batch operations in transactions, which can be thought of as ‘atomic execution units’. So if all operations in a batch succeed, the whole transaction succeeds. But if any one of the operations fails, the whole transaction is made to fail or is rolled back. This ensures that the database state always stays consistent. The complete working example is available at the end of the article for reference.

2. Example Walk-through

2.1 Technologies used in this demonstration

• Java 1.6
• Eclipse IDE
• MySQL 5.0 as the DataBase
• MySql 5.0 jar to connect to the database from Eclipse

2.2 Sample data used in the Example

2.3 Project Structure used

3. Approach 1: Jdbc Batch Update using Statement object

• First create a Connection objectNote: import statements, try..catch etc. have been removed for the sake of brevity.
   
  ConnectionObject.java

  public class ConnectionObject {static String DB_DRIVER = "com.mysql.jdbc.Driver";static String DB_CONNECTION = "jdbc:mysql://localhost:3306/test";static String DB_USER = "userName";static String DB_PASSWORD = "password";public static Connection getConnection() {Connection connection = null;Class.forName(DB_DRIVER);connection = DriverManager.getConnection(DB_CONNECTION, DB_USER,DB_PASSWORD);return connection;}}

• Create a Statement objectTheStatement object offers two methods:addBatch() andexecuteBatch() that we can use. The first method is used to create a ‘batch’ of statements and the latter is used to execute the batch as one unit. It returns anint[] array that indicates the number of records affected by each statement in the Batch. Pretty neat and simple just that the database query needs to be repeated in each statement (refer sample code below).
• Turn auto-commit off
  This is done so that all the Batch statements execute in a single transaction and no operation in the batch is committed individually.
• UseaddBatch()
  Add as many statements as required to the Batch using this method.
• Execute the Batch usingexecuteBatch()
  Then execute the Batch of statements by invoking theexecuteBatch() as shown below
• Finally commit or roll-back the transaction
• Code Snippet as below
   
  batchUpdateUsingStatement() method
  Want to be a JDBC Master ?

  Subscribe to our newsletter and download the JDBCUltimateGuideright now!

  In order to help you master database programming with JDBC, we have compiled a kick-ass guide with all the major JDBC features and use cases! Besides studying them online you may download the eBook in PDF format!

  

  Download NOW!

  Thank you!

  We will contact you soon.

  public void batchUpdateUsingStatement() throws SQLException {// This is to hold the response of executeBatch()int[] result = null;try {Statement stmt = connection.createStatement();connection.setAutoCommit(false); // Setting auto-commit offString SQL = "update person set firstName='New First Name', lastName='New Last Name' where id=1";stmt.addBatch(SQL); // add statement to BatchSQL = "update person set firstName='First Name',lastName='Last Name' where id=2";stmt.addBatch(SQL); // add second statement to Batchresult = stmt.executeBatch(); // execute the Batchconnection.commit(); // commit} catch (SQLException e) {connection.rollback(); // rollBack in case of an exceptione.printStackTrace();} finally {if (connection != null)connection.close(); // finally close the connection}System.out.println("Number of rows affected: " + result.length);}

4. Approach 2: Jdbc Batch Update using PreparedStatement

• Create aPreparedStatement object
  PreparedStatement also exposes two methods for adding statements to the Batch and executing them as those offered byStatement object. But it allows reusing the SQL query by just substituting the parameters in each query. It promises a better performance than the simpleStatement object. Besides, it also helps to check against SQL injection threat. Observe the ‘?’ used to substitute the actual parameter values in the code below. The parameter value is supplied by specifying the appropriate parameter index in the corresponding ‘set’ method.
• Turn Auto-Commit Off
  This to enable Batch processing in a single transaction as explained above
• Set and Add
  Set the values of each parameter into the query and add the statement to the Batch
• Execute the batch
  Finally execute the batch of statements
• Commit or Roll-back
  Then commit or roll-back the transaction as shown in the code below.
• Code Snippet as below
   
  batchUpdateUsingPreparedStatement() method

  public void batchUpdateUsingPreparedStatement() throws SQLException {int[] result = null;String SQL = "update person set firstName=?,lastName=? where id=?"; // '?' is the placeholder for the parametertry {PreparedStatement stmt = connection.prepareStatement(SQL);connection.setAutoCommit(false);stmt.setString(1, "Abc"); // Value for the first parameter, namely 'firstName'stmt.setString(2, "Def"); // Value for the second parameter, namely 'lastName'stmt.setInt(3, 1); // Value for the third parameter, namely 'id'stmt.addBatch(); // Add to Batchstmt.setString(1, "Xyz");stmt.setString(2, "Uvw");stmt.setInt(3, 2);stmt.addBatch(); // Add second query to the Batchresult = stmt.executeBatch(); // execute the Batch and commitconnection.commit();} catch (SQLException e) {connection.rollback();e.printStackTrace();} finally {if (connection != null)connection.close();}System.out.println("Number of rows affected: " + result.length);}

5. Download the source code

This concludes our example of using JDBC Batch Update using bothStatement andPreparedStatement. As promised, the example code has been shared below.

Do you want to know how to develop your skillset to become aJava Rockstar?

Subscribe to our newsletter to start Rockingright now!

To get you started we give you our best selling eBooks forFREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to theTerms andPrivacy Policy

Sign up

Thank you!

We will contact you soon.