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
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.javapublic 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
Statementobject 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. - 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 theexecuteBatch()as shown below - Finally commit or roll-back the transaction
- Code Snippet as below
batchUpdateUsingStatement() methodpublic 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
PreparedStatementobject
PreparedStatementalso exposes two methods for adding statements to the Batch and executing them as those offered byStatementobject. But it allows reusing the SQL query by just substituting the parameters in each query. It promises a better performance than the simpleStatementobject. 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() methodpublic 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.
Download the full source code of this example here: JdbcBatchUpdateExample
Failed records list? Successful records list?