Batch Processing using Prepared Statement in Java

Batch processing is a feature in JDBC used to club multiple SQLs and send it to database in one call.

You can get more details on Batch Processing here

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 Prepared Statement

package com.kscodes.sampleproject.jdbc;

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

public class PreparedBatchExample {

	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 insertSql = "INSERT INTO employee_details"
				+ " (id,first_name,last_name,designation,salary) "
				+ " VALUES (?,?,?,?,?)";

		try (Connection connection = getConnection();
				PreparedStatement preparedStatement = connection.prepareStatement(insertSql)) {
			connection.setAutoCommit(false);
			
			preparedStatement.setInt(1, 1);
			preparedStatement.setString(2, "Steve");
			preparedStatement.setString(3, "JJ");
			preparedStatement.setString(4, "Sr Developer");
			preparedStatement.setInt(5, 5000);
			preparedStatement.addBatch();
			
			preparedStatement.setInt(1, 2);
			preparedStatement.setString(2, "John");
			preparedStatement.setString(3, "K");
			preparedStatement.setString(4, "Sr Developer");
			preparedStatement.setInt(5, 5000);
			preparedStatement.addBatch();

			int[] returnvalues = preparedStatement.executeBatch();

			System.out.println("Executed the batch Successfully. The return values for the SQL's 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.PreparedStatement;

import java.sql.SQLException;

public class PreparedBatchExample {

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 insertSql = "INSERT INTO employee_details"

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

+ " VALUES (?,?,?,?,?)";

try (Connection connection = getConnection();

PreparedStatement preparedStatement = connection.prepareStatement(insertSql)) {

connection.setAutoCommit(false);

preparedStatement.setInt(1, 1);

preparedStatement.setString(2, "Steve");

preparedStatement.setString(3, "JJ");

preparedStatement.setString(4, "Sr Developer");

preparedStatement.setInt(5, 5000);

preparedStatement.addBatch();

preparedStatement.setInt(1, 2);

preparedStatement.setString(2, "John");

preparedStatement.setString(3, "K");

preparedStatement.setString(4, "Sr Developer");

preparedStatement.setInt(5, 5000);

preparedStatement.addBatch();

int[] returnvalues = preparedStatement.executeBatch();

System.out.println("Executed the batch Successfully. The return values for the SQL's 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 SQL's are::
1
1

Connection created Successfully.

Executed the batch Successfully. The return values for the SQL's are::