sql

JDBC Batch Processing Example

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.

Summary:

Here we tried to understand what is JDBC Batch and how we can the use the same to reduce network traffic and improve our application performance.

Download
You can download the source code of this example here: BatchOperation.zip

Chandan Singh

Chandan holds a degree in Computer Engineering and is a passionate software programmer. He has good experience in Java/J2EE Web-Application development for Banking and E-Commerce Domains.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button