Batch Processing using Statement in Java

Batch processing is a feature in JDBC used to club multiple SQLs and send it to database in one call.
Imagine you have 100’s of inserts to be fired using JDBC, this will take 100’s of calls to the database resulting in performance degradation. So we can use batch processing to improve performance in such scenarios.

SQL allowed in Batch Processing

All inserts, updates and delete SQL’s are allowed in batch processing.
Selects are create table SQL’s are not allowed in batch processing.

Some Important methods in Batch processing

1. void addBatch(String sql)
2. int[] executeBatch()
3. void clearBatch()

You can get more details on this method in the Java docs – Statement Interface

Advantages of Batch processing

1. Performance
Batch processing can reduce a lot of performance overhead, as all the SQL statements are send in one database call.

2. Transaction Management
Imagine you have 50 SQL statements to be fired and if any one of the SQL fails during execution, then revert all other changes.
This can be achieved using batch processing.

Return value of executeBatch()

executeBatch() returns an array of update counts containing one element for each SQL that was added in the batch according to the order of which they were added.

Now lets see an example of Batch Processing using Statement in Java

Example : Batch Processing using Statement in Java

package com.kscodes.sampleproject.jdbc;

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

public class StatementBatchExample {

	public static String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";
	public static String CONNECTION_URL = "jdbc:mysql://localhost:3306/mysql";
	public static String CONNECTION_USER = "kscodes";
	public static String CONNECTION_PASSWORD = "kscodes";

	public static void main(String[] args) {

		String insertSql1 = "INSERT INTO employee_details"
				+ " (id,first_name,last_name,designation,salary) "
				+ " VALUES (1,'John','Doe','Engineer','10000')";
		String insertSql2 = "INSERT INTO employee_details"
				+ " (id,first_name,last_name,designation,salary) "
				+ " VALUES (2,'Smith','Yo','Engineer','10000')";
		String updateSql = "UPDATE employee_details SET salary = 12000 "
				+ " WHERE designation = 'Engineer'";
		String deleteSql = "DELETE FROM employee_details "
				+ " WHERE designation = 'Engineer'";

		try (Connection connection = getConnection();
				Statement statement = connection.createStatement()) {
			connection.setAutoCommit(false);
			
			statement.addBatch(insertSql1);
			statement.addBatch(insertSql2);
			statement.addBatch(updateSql);
			statement.addBatch(deleteSql);

			int[] returnvalues = statement.executeBatch();

			System.out.println("Executed the batch Successfully. The return values for the statements are::");
			for(int value:returnvalues){
				System.out.println(value);
			}
			
			connection.setAutoCommit(true);

		} catch (SQLException e) {
			System.out.println("An exception occured while executing batch. Exception is :: "
							+ e);
		}

	}

	public static Connection getConnection() {

		try {
			Class.forName(DRIVER_CLASS_NAME);
		} catch (ClassNotFoundException e) {
			System.out.println("Error while registering JDBC driver");
			return null;
		}

		Connection connection = null;
		try {
			connection = DriverManager.getConnection(CONNECTION_URL,
					CONNECTION_USER, CONNECTION_PASSWORD);

		} catch (SQLException e) {
			System.out.println("Failed to create Connection");
			return null;
		}
		System.out.println("Connection created Successfully.");

		return connection;
	}

}

package com.kscodes.sampleproject.jdbc;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

public class StatementBatchExample {

public static String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";

public static String CONNECTION_URL = "jdbc:mysql://localhost:3306/mysql";

public static String CONNECTION_USER = "kscodes";

public static String CONNECTION_PASSWORD = "kscodes";

public static void main(String[] args) {

String insertSql1 = "INSERT INTO employee_details"

+ " (id,first_name,last_name,designation,salary) "

+ " VALUES (1,'John','Doe','Engineer','10000')";

String insertSql2 = "INSERT INTO employee_details"

+ " (id,first_name,last_name,designation,salary) "

+ " VALUES (2,'Smith','Yo','Engineer','10000')";

String updateSql = "UPDATE employee_details SET salary = 12000 "

+ " WHERE designation = 'Engineer'";

String deleteSql = "DELETE FROM employee_details "

+ " WHERE designation = 'Engineer'";

try (Connection connection = getConnection();

Statement statement = connection.createStatement()) {

connection.setAutoCommit(false);

statement.addBatch(insertSql1);

statement.addBatch(insertSql2);

statement.addBatch(updateSql);

statement.addBatch(deleteSql);

int[] returnvalues = statement.executeBatch();

System.out.println("Executed the batch Successfully. The return values for the statements are::");

for(int value:returnvalues){

System.out.println(value);

}

connection.setAutoCommit(true);

} catch (SQLException e) {

System.out.println("An exception occured while executing batch. Exception is :: "

+ e);

}

public static Connection getConnection() {

try {

Class.forName(DRIVER_CLASS_NAME);

} catch (ClassNotFoundException e) {

System.out.println("Error while registering JDBC driver");

return null;

}

Connection connection = null;

try {

connection = DriverManager.getConnection(CONNECTION_URL,

CONNECTION_USER, CONNECTION_PASSWORD);

} catch (SQLException e) {

System.out.println("Failed to create Connection");

return null;

}

System.out.println("Connection created Successfully.");

return connection;

}

Output

Connection created Successfully.
Executed the batch Successfully. The return values for the statements are::
1
1
2
2

Connection created Successfully.

Executed the batch Successfully. The return values for the statements are::