JDBC Batch Processing

Why

There can be requirement where multiple times DML (commands such as INSERT INTO, UPDATE, DELETE) or DDL (such as CREATE TABLE, DROP TABLE, ALTER TABLE) operations are needed to be performed with database. In such case hitting database again and again to execute queries might cause database performance bottle neck.

What

To overcome such problems JDBC API supports batch processing that allows us to perform related operation all together in one shot. Means similar type of database operations can be clubbed/grouped to form a batch and that batch can be submitted to database as single command. This helps improving database performance.

Batch processing can be carried out only on commands that return an update count (commands such as INSERT INTO, UPDATE, DELETE) or that return 0 (such as CREATE TABLE, DROP TABLE, ALTER TABLE) can be successfully executed as a batch with the executeBatch method. You should not add a query (a SELECT statement) to a batch of SQL commands because the method executeBatch, which returns an array of update counts, expects an update count from each SQL statement that executes successfully.

How

Batch processing in Statement

Batch processing in Statement is two step process

  1. Add related DMLs in a batch, to achieve this addBatch(String sql) can be invoked on Statement object
  2. Execute the batch, to achieve this executeBatch() can be invoked on Statement object

Modifier and Type Method and Description

  • void addBatch(String sql) - Adds the given SQL command to the current list of commands for this Statement object.
  • void clearBatch() - Empties this Statement object's current list of SQL commands.
  • int[] executeBatch() - Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.
  • default long[] executeLargeBatch() - Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import com.atuldwivedi.jee.jdbc.util.connection.ConnectionFactory;

public class BatchProcessingUsingStatement {

	static Connection con;
	static Statement stmt;

	public static void main(String[] args) {
		try {
			con = ConnectionFactory.getConnecction();
			stmt = con.createStatement();
			stmt.addBatch("insert into learnjdbc_coffee values('Cof1', 0, 0, 10.10)");
			stmt.addBatch("insert into learnjdbc_coffee values('Cof2', 0, 0, 20.10)");
			int[] i = stmt.executeBatch();
			for (int j = 0; j < i.length; j++) {
				System.out.println(i[j]);
			}
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
	}
}

Batch processing in PreparedStatement

Batch processing in PreparedStatement is two step process
  1. Add set of params to a batch, to achieve this addBatch() can be invoked on PreparedStatementobject
  2. Execute the batch, to achieve this executeBatch() can be invoked on Statement type object

Modifier and Type Method and Description

  • void addBatch() - Adds a set of parameters to this PreparedStatement object's batch of commands.
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.atuldwivedi.jee.jdbc.util.connection.ConnectionFactory;

public class BatchProcessingUsingPreparedStatement {

	static Connection con;
	static PreparedStatement pstmt;

	public static void main(String[] args) {
		try {
			con = ConnectionFactory.getConnecction();
			pstmt = con
					.prepareStatement("insert into learnjdbc_coffee values(?,?,?,?)");
			BufferedReader br = new BufferedReader(new InputStreamReader(
					System.in));
			while (true) {
				System.out.println("Enter coffee name: ");
				pstmt.setString(1, br.readLine());

				pstmt.setInt(2, 0);
				pstmt.setInt(3, 0);

				System.out.println("Enter coffee price: ");
				pstmt.setFloat(4, Float.parseFloat(br.readLine()));
				pstmt.addBatch();

				System.out.println("Do you want to continue? (y/n) ");
				if (br.readLine().equals("n"))
					break;
			}

			int[] i = pstmt.executeBatch();
			for (int j = 0; j < i.length; j++) {
				System.out.println(i[j]);
			}

		} catch (ClassNotFoundException | SQLException | IOException e) {
			e.printStackTrace();
		}
	}
}
 
Footer with Map