JDBC Batch Update Example

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 using Statement and PreparedStatement objects. Though the example revolves around update; delete and insert operations can also be ‘batched’. Of course, batching select 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 the Connection 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 objectThe Statement object offers two methods: addBatch() and executeBatch() 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 an int[] 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.
• Use addBatch()
  Add as many statements as required to the Batch using this method.
• Execute the Batch using executeBatch()
  Then execute the Batch of statements by invoking the executeBatch() as shown below
• Finally commit or roll-back the transaction
• Code Snippet as below
   
  batchUpdateUsingStatement() method

  	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 off
  			String SQL = "update person set firstName='New First Name', lastName='New Last Name' where id=1";
  			stmt.addBatch(SQL); // add statement to Batch
  			SQL = "update person set firstName='First Name',lastName='Last Name' where id=2";
  			stmt.addBatch(SQL); // add second statement to Batch
  			result = stmt.executeBatch(); // execute the Batch
  			connection.commit(); // commit
  		} catch (SQLException e) {
  			connection.rollback(); // rollBack in case of an exception
  			e.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 a PreparedStatement object
  PreparedStatement also exposes two methods for adding statements to the Batch and executing them as those offered by Statement object. But it allows reusing the SQL query by just substituting the parameters in each query. It promises a better performance than the simple Statement 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 parameter
  		try {
  			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 Batch
  			stmt.setString(1, "Xyz");
  			stmt.setString(2, "Uvw");
  			stmt.setInt(3, 2);
  			stmt.addBatch(); // Add second query to the Batch
  			result = stmt.executeBatch(); // execute the Batch and commit
  			connection.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 both Statement and PreparedStatement. As promised, the example code has been shared below.