JDBC Batch Processing Example

Chandan SinghJanuary 2nd, 2015Last Updated: March 5th, 2019

0 117 2 minutes read

In this example, we will see how we can use Batch Processing in Java Database Connectivity(i.e.JDBC).

When multiple inserts are to be made to the table in a database, the trivial way is to execute a query per record.
However, this involves acquiring and releasing connection every time a record is inserted, which hampers application performance.

We overcome, this(acquiring and releasing connection every-time) by making use of Batch operations in JDBC.

We set the parameters in the java.sql.PreparedStatement and it to the batch using PreparedStatement.addBatch() method. Then when the batch size reaches a desired threshold, we execute the batch using PreparedStatement.executeBatch() method.

TIP:
Another way to avoid manually releasing/acquiring connection is to use Connection Pooling.

However, when executing Queries in Batch, it is sometimes important to maintain the atomicity of the database. This can be a problem if one the commands in the batch throws some error since the commands after the exception will not be executed leaving the database in an inconsistent state. So we set the auto-commit to false and if all the records are executed successfully, we commit the transaction. This maintains the integrity of the the database.

We will try to understand points explained above with the help of an example :

BatchExample.java:

package com.javacodegeeks.examples;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;


public class BatchExample
{
	public static void main(String[] args)
	{
		try (Connection connection = DBConnection.getConnection())
		{
			connection.setAutoCommit(false);

			try (PreparedStatement pstmt = connection.prepareStatement("Insert into txn_tbl (txn_id,txn_amount, card_number, terminal_id) values (null,?,?,?)");)
			{
				pstmt.setString(1, "123.45");
				pstmt.setLong(2, 2345678912365L);
				pstmt.setLong(3, 1234567L);
				pstmt.addBatch();

				pstmt.setString(1, "456.00");
				pstmt.setLong(2, 567512198454L);
				pstmt.setLong(3, 1245455L);
				pstmt.addBatch();

				pstmt.setString(1, "7859.02");
				pstmt.setLong(2, 659856423145L);
				pstmt.setLong(3, 5464845L);
				pstmt.addBatch();

				int[] arr = pstmt.executeBatch();
				System.out.println(Arrays.toString(arr));

				connection.commit();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
				connection.rollback();
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
}

DBConnection.java:

package com.javacodegeeks.examples;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;


/**
* @author Chandan Singh
*/
public class DBConnection
{
	public static Connection getConnection() throws SQLException, ClassNotFoundException
	{
		Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jcg?rewriteBatchedStatements=true", "root", "toor");

		return connection;
	}
	
}

One important point to note here is the connection URL . The rewriteBatchedStatements=true is important since it nudges the JDBC to pack as many queries as possible into a single network data packet, thus lowering the traffic. Without that parameter, there will not be much performance improvement when using JDBC Batch.

NOTE:
We can use JDBC Batch, when using java.sql.Statement, in similar fashion.

The PreparedStatement.executeBatch() method retruns an int array. Depending upon the values, we can know the status of the each executed queries:

A value greater than zero usually indicates successful execution of query.
A value equal to Statement.SUCCESS_NO_INFO indicates, successful command execution but no record count is available.
A value equal to Statement.EXECUTE_FAILED indicates, command was not executed successfully. It shows up only if the driver continued to execute the commands after the failed command.