Batch Processing in JDBC

JDBC Batch Processing is a feature that allows multiple SQL statements to be grouped together and executed as a single batch. Instead of sending each SQL statement individually to the database, JDBC collects them and sends them in one request, reducing network overhead and improving application performance.

• Improves performance by reducing network round trips.
• Minimizes database communication overhead.
• Supports transaction management for data consistency.

Why Use Batch Processing in JDBC?

Executing SQL statements individually can be slow because every execution requires communication between the Java application and the database server. Batch processing helps to:

• Improve application performance.
• Reduce network traffic.
• Execute bulk database operations efficiently.

Methods Used in JDBC Batch Processing

Different Ways to use Batch processing

JDBC provides multiple ways to perform batch processing depending on the type of SQL operations and application requirements. The most common approaches are given below.

1. Batch Processing Using Statement

Statement is used to execute multiple different SQL statements together as a single batch. It is suitable when each SQL query is different and does not require input parameters.

• Executes different SQL statements in one batch.
• Does not support parameterized queries.
• Best suited for simple or mixed SQL operations.

Syntax:

Statement stmt = connection.createStatement();
stmt.addBatch("INSERT INTO employee VALUES (101, 'Rahul', 50000)");
stmt.addBatch("UPDATE employee SET salary = 60000 WHERE id = 101");
stmt.addBatch("DELETE FROM employee WHERE id = 105");
int[] result = stmt.executeBatch();

2. Batch Processing Using PreparedStatement

PreparedStatement is used to execute the same SQL statement multiple times with different parameter values. It offers better performance and helps prevent SQL injection.

• Supports parameterized SQL queries.
• Faster for bulk insert, update, and delete operations.
• Recommended for large-scale batch processing.

Syntax:

String sql = "INSERT INTO employee(id, name, salary) VALUES (?, ?, ?)";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, 101);
pstmt.setString(2, "Rahul");
pstmt.setDouble(3, 50000);
pstmt.addBatch();
pstmt.setInt(1, 102);
pstmt.setString(2, "Amit");
pstmt.setDouble(3, 60000);
pstmt.addBatch();
int[] result = pstmt.executeBatch();

3. Batch Processing Using CallableStatement

CallableStatement is used to execute stored procedures in a batch. It is commonly used when database-side business logic is implemented using stored procedures.

• Executes stored procedures in batch mode.
• Supports input and output parameters.
• Suitable for enterprise applications using database procedures.

Syntax:

CallableStatement cstmt =
connection.prepareCall("{call addEmployee(?, ?, ?)}");
cstmt.setInt(1, 101);
cstmt.setString(2, "Rahul");
cstmt.setDouble(3, 50000);
cstmt.addBatch();
cstmt.setInt(1, 102);
cstmt.setString(2, "Amit");
cstmt.setDouble(3, 60000);
cstmt.addBatch();
int[] result = cstmt.executeBatch();

Example To Showing JDBC Batch Processing

In this example, we will insert multiple employee records into a MySQL database using JDBC Batch Processing with PreparedStatement.

Step 1: Create the Employee Table

Create table in your database for storing the data.

CREATE TABLE employee(
id INT PRIMARY KEY,
name VARCHAR(50),
salary DOUBLE
);

Step 2: Execute The Related Program

Run The below program to inserting a multiple Employee record in table using Batch Processing.

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

public class EmployeeBatchExample {

    public static void main(String[] args) {

        String url = "jdbc:mysql://localhost:3306/company";
        String username = "root";
        String password = "root";

        String sql =
                "INSERT INTO employee(id, name, salary) VALUES (?, ?, ?)";

        try {

            // Step 1: Establish Connection
            Connection con = DriverManager.getConnection(
                    url,
                    username,
                    password
            );

            // Step 2: Create PreparedStatement
            PreparedStatement pstmt = con.prepareStatement(sql);

            // Step 3: Add First Record
            pstmt.setInt(1, 101);
            pstmt.setString(2, "Rahul");
            pstmt.setDouble(3, 45000);
            pstmt.addBatch();

            // Step 4: Add Second Record
            pstmt.setInt(1, 102);
            pstmt.setString(2, "Amit");
            pstmt.setDouble(3, 55000);
            pstmt.addBatch();

            // Step 5: Add Third Record
            pstmt.setInt(1, 103);
            pstmt.setString(2, "Priya");
            pstmt.setDouble(3, 65000);
            pstmt.addBatch();

            // Step 6: Execute Batch
            int[] result = pstmt.executeBatch();

            // Step 7: Print Result
            System.out.println(
                    "Total Records Inserted : " + result.length
            );

            // Step 8: Close Resources
            pstmt.close();
            con.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Output:

Total Records Inserted : 3

Comparison of Different Batch Processing Approaches