Practice Programs on Batch Processing -

Program 1: Batch Update of Salaries for Multiple Employees

This program updates the salaries of several employees in a single batch. The same UPDATE SQL structure is reused with different employee IDs and salary values using PreparedStatement.

import java.sql.*;

public class BatchSalaryUpdate {

    public static void main(String[] args) {

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

        String sql =
            "UPDATE employee SET salary = ? WHERE employee_id = ?";

        try {
            Connection con =
                DriverManager.getConnection(url, username, password);

            PreparedStatement pstmt =
                con.prepareStatement(sql);

            // Employee 101
            pstmt.setDouble(1, 60000);
            pstmt.setInt(2, 101);
            pstmt.addBatch();

            // Employee 102
            pstmt.setDouble(1, 52000);
            pstmt.setInt(2, 102);
            pstmt.addBatch();

            int[] result = pstmt.executeBatch();

            System.out.println("Salary batch completed.");

            for (int i = 0; i < result.length; i++) {
                System.out.println(
                    "Employee " + (101 + i) +
                    " : " + result[i] + " row updated"
                );
            }

            pstmt.close();
            con.close();

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

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" BatchSalaryUpdate.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" BatchSalaryUpdate
Salary batch completed.
Employee 101 : 1 row updated
Employee 102 : 1 row updatedCode language: CSS (css)

Program 2: Batch Delete of Employees from a Department

This program removes multiple employees belonging to a particular department. The employee IDs are supplied as parameters and all delete operations are executed as one batch.

import java.sql.*;

public class BatchEmployeeDelete {

    public static void main(String[] args) {

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

        String sql =
            "DELETE FROM employee WHERE employee_id = ?";

        int[] employeeIds = {104, 105};

        try {
            Connection con =
                DriverManager.getConnection(url, username, password);

            PreparedStatement pstmt =
                con.prepareStatement(sql);

            for (int id : employeeIds) {

                pstmt.setInt(1, id);
                pstmt.addBatch();
            }

            int[] result = pstmt.executeBatch();

            int totalDeleted = 0;

            for (int count : result) {
                totalDeleted += count;
            }

            System.out.println(
                "Total employees deleted: " + totalDeleted
            );

            pstmt.close();
            con.close();

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

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" BatchEmployeeDelete.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" BatchEmployeeDelete
Total employees deleted: 2Code language: CSS (css)

Program 3: Batch Update of Employee Departments

This program changes the departments of multiple employees. A loop is used to assign different department names and employee IDs to a single PreparedStatement.

import java.sql.*;

public class BatchDepartmentUpdate {

    public static void main(String[] args) {

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

        String sql =
            "UPDATE employee " +
            "SET department = ? " +
            "WHERE employee_id = ?";

        String[] departments = {
            "Development",
            "Testing",
            "Management"
        };

        int[] employeeIds = {
            101, 102, 103
        };

        try {
            Connection con =
                DriverManager.getConnection(url, username, password);

            PreparedStatement pstmt =
                con.prepareStatement(sql);

            for (int i = 0; i < employeeIds.length; i++) {

                pstmt.setString(1, departments[i]);
                pstmt.setInt(2, employeeIds[i]);

                pstmt.addBatch();
            }

            int[] result = pstmt.executeBatch();

            System.out.println(
                "Department update batch completed."
            );

            System.out.println(
                "Operations executed: " + result.length
            );

            pstmt.close();
            con.close();

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

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" BatchDepartmentUpdate.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" BatchDepartmentUpdate
Department update batch completed.
Operations executed: 3Code language: CSS (css)

Program 4: Executing Different SQL Operations in One Statement Batch

A Statement batch does not have to contain only one type of SQL operation. This program combines UPDATE, INSERT, and DELETE statements in the same batch.

import java.sql.*;

public class MixedStatementBatch {

    public static void main(String[] args) {

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

        try {
            Connection con =
                DriverManager.getConnection(url, username, password);

            Statement stmt = con.createStatement();

            // Update
            stmt.addBatch(
                "UPDATE employee " +
                "SET salary = 65000 " +
                "WHERE employee_id = 101"
            );

            // Insert
            stmt.addBatch(
                "INSERT INTO employee " +
                "(employee_id, employee_name, department, salary) " +
                "VALUES (109, 'Deepak', 'Support', 36000)"
            );

            // Update
            stmt.addBatch(
                "UPDATE employee " +
                "SET department = 'Operations' " +
                "WHERE employee_id = 102"
            );

            int[] result = stmt.executeBatch();

            System.out.println(
                "Mixed batch executed successfully."
            );

            System.out.println(
                "Total operations: " + result.length
            );

            stmt.close();
            con.close();

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

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" MixedStatementBatch.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" MixedStatementBatch
Mixed batch executed successfully.
Total operations: 3Code language: CSS (css)

Program 5: Batch Processing with Transaction Management

This program demonstrates batch processing together with a database transaction. All operations are committed together when the batch succeeds; otherwise, the transaction is rolled back.

import java.sql.*;

public class TransactionBatchExample {

    public static void main(String[] args) {

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

        String sql =
            "UPDATE employee SET salary = salary + ? " +
            "WHERE employee_id = ?";

        try {
            Connection con =
                DriverManager.getConnection(url, username, password);

            con.setAutoCommit(false);

            PreparedStatement pstmt =
                con.prepareStatement(sql);

            pstmt.setDouble(1, 5000);
            pstmt.setInt(2, 101);
            pstmt.addBatch();

            pstmt.setDouble(1, 3000);
            pstmt.setInt(2, 102);
            pstmt.addBatch();

            pstmt.setDouble(1, 4000);
            pstmt.setInt(2, 104);
            pstmt.addBatch();

            int[] result = pstmt.executeBatch();

            con.commit();

            System.out.println(
                "Batch executed and transaction committed."
            );

            System.out.println(
                "Operations completed: " + result.length
            );

            pstmt.close();
            con.close();

        } catch (SQLException e) {

            System.out.println(
                "Batch failed. Transaction rolled back."
            );

            e.printStackTrace();
        }
    }
}

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" TransactionBatchExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" TransactionBatchExample
Batch executed and transaction committed.
Operations completed: 3Code language: CSS (css)

Program 6: Using clearBatch() Before Executing

The clearBatch() method removes all SQL commands currently stored in a batch. This program first adds unwanted operations, clears them, and then adds the required operations before execution.

import java.sql.*;

public class ClearBatchExample {

    public static void main(String[] args) {

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

        try {
            Connection con =
                DriverManager.getConnection(url, username, password);

            Statement stmt = con.createStatement();

            // These operations will not be executed
            stmt.addBatch(
                "UPDATE employee SET salary = 10000 " +
                "WHERE employee_id = 101"
            );

            stmt.addBatch(
                "UPDATE employee SET salary = 10000 " +
                "WHERE employee_id = 102"
            );

            // Remove the previous operations
            stmt.clearBatch();

            // Add the correct operations
            stmt.addBatch(
                "UPDATE employee SET salary = 70000 " +
                "WHERE employee_id = 101"
            );

            stmt.addBatch(
                "UPDATE employee SET salary = 60000 " +
                "WHERE employee_id = 102"
            );

            int[] result = stmt.executeBatch();

            System.out.println(
                "Unwanted batch operations cleared."
            );

            System.out.println(
                "Correct operations executed: " +
                result.length
            );

            stmt.close();
            con.close();

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

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" ClearBatchExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" ClearBatchExample
Unwanted batch operations cleared.
Correct operations executed: 2Code language: CSS (css)

Program 7: Dynamic Batch Processing Using an Array

This program demonstrates how batch operations can be created dynamically from arrays. Employee IDs and salary increments are stored in arrays and processed using a loop.

import java.sql.*;

public class DynamicBatchExample {

    public static void main(String[] args) {

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

        int[] ids = {101, 102, 106};

        double[] increments = {
            2000,
            3000,
            2500
        };

        String sql =
            "UPDATE employee " +
            "SET salary = salary + ? " +
            "WHERE employee_id = ?";

        try {
            Connection con =
                DriverManager.getConnection(url, username, password);

            PreparedStatement pstmt =
                con.prepareStatement(sql);

            for (int i = 0; i < ids.length; i++) {

                pstmt.setDouble(1, increments[i]);
                pstmt.setInt(2, ids[i]);

                pstmt.addBatch();
            }

            int[] result = pstmt.executeBatch();

            System.out.println(
                "Dynamic batch completed."
            );

            for (int i = 0; i < result.length; i++) {

                System.out.println(
                    "Employee " + ids[i] +
                    " updated: " + result[i]
                );
            }

            pstmt.close();
            con.close();

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

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" DynamicBatchExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" DynamicBatchExample
Dynamic batch completed.
Employee 101 updated: 1
Employee 102 updated: 1
Employee 106 updated: 1Code language: CSS (css)

Program 8: Batch Processing with Result Status

This program examines the result returned by executeBatch(). The result values are used to determine whether each individual operation affected a row.

import java.sql.*;

public class BatchResultStatusExample {

    public static void main(String[] args) {

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

        String sql =
            "UPDATE employee " +
            "SET salary = salary + 1000 " +
            "WHERE employee_id = ?";

        int[] ids = {101, 102, 999};

        try {
            Connection con =
                DriverManager.getConnection(url, username, password);

            PreparedStatement pstmt =
                con.prepareStatement(sql);

            for (int id : ids) {

                pstmt.setInt(1, id);
                pstmt.addBatch();
            }

            int[] result = pstmt.executeBatch();

            for (int i = 0; i < result.length; i++) {

                if (result[i] > 0) {
                    System.out.println(
                        "Employee " + ids[i] +
                        " updated successfully."
                    );
                } else {
                    System.out.println(
                        "Employee " + ids[i] +
                        " was not found."
                    );
                }
            }

            pstmt.close();
            con.close();

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

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" BatchResultStatusExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" BatchResultStatusExample
Employee 101 updated successfully.
Employee 102 updated successfully.
Employee 999 was not found.Code language: CSS (css)

Program 9: Batch Processing in Groups

When a very large number of records must be processed, it is useful to divide them into smaller batches. This program processes employee salary updates in groups of two records at a time instead of keeping all operations in one large batch.

import java.sql.*;

public class BatchInGroupsExample {

    public static void main(String[] args) {

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

        int[] employeeIds = {
            101, 102, 106, 107, 108
        };

        String sql =
            "UPDATE employee " +
            "SET salary = salary + 1500 " +
            "WHERE employee_id = ?";

        final int BATCH_SIZE = 2;

        try {
            Connection con =
                DriverManager.getConnection(url, username, password);

            PreparedStatement pstmt =
                con.prepareStatement(sql);

            int count = 0;
            int batchNumber = 1;

            for (int id : employeeIds) {

                pstmt.setInt(1, id);
                pstmt.addBatch();

                count++;

                if (count == BATCH_SIZE) {

                    pstmt.executeBatch();

                    System.out.println(
                        "Batch " + batchNumber +
                        " completed."
                    );

                    pstmt.clearBatch();

                    count = 0;
                    batchNumber++;
                }
            }

            // Execute remaining records
            if (count > 0) {

                pstmt.executeBatch();

                System.out.println(
                    "Batch " + batchNumber +
                    " completed."
                );
            }

            pstmt.close();
            con.close();

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

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" BatchInGroupsExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" BatchInGroupsExample
Batch 1 completed.
Batch 2 completed.
Batch 3 completed.Code language: CSS (css)

java.sql package

Introduction to java.sql package

JDBC Basics

Connection and DataSource(Demonstration with mysql database)

Statement and PreparedStatement(Demonstration with mysql database)

ResultSet(Demonstration with mysql database)

Batch Processing(Demonstration with mysql database)

Transactions(Demonstration with mysql database)

CallableStatement(Demonstration with mysql database)

Metadata(Demonstration with mysql database)

RowSet(Demonstration with mysql database)

Savepoint(Demonstration with mysql database)

Handling SQL Exceptions

Connecting JDBC Application with Different Databases

Oracle Database

MySQL Database

PostgresSQL Database

SQLite Database

SQLServer Database

H2 Database

MongoDB Database

Practice Programs on Batch Processing

Program 1: Batch Update of Salaries for Multiple Employees

Program 2: Batch Delete of Employees from a Department

Program 3: Batch Update of Employee Departments

Program 4: Executing Different SQL Operations in One Statement Batch

Program 5: Batch Processing with Transaction Management

Program 6: Using clearBatch() Before Executing

Program 7: Dynamic Batch Processing Using an Array

Program 8: Batch Processing with Result Status

Program 9: Batch Processing in Groups

java.sql package

Introduction to java.sql package

JDBC Basics

Connection and DataSource(Demonstration with mysql database)

Statement and PreparedStatement(Demonstration with mysql database)

ResultSet(Demonstration with mysql database)

Batch Processing(Demonstration with mysql database)

Transactions(Demonstration with mysql database)

CallableStatement(Demonstration with mysql database)

Metadata(Demonstration with mysql database)

RowSet(Demonstration with mysql database)

Savepoint(Demonstration with mysql database)

Handling SQL Exceptions

Connecting JDBC Application with Different Databases

Oracle Database

MySQL Database

PostgresSQL Database

SQLite Database

SQLServer Database

H2 Database

MongoDB Database

Program 1: Batch Update of Salaries for Multiple Employees

Program 2: Batch Delete of Employees from a Department

Program 3: Batch Update of Employee Departments

Program 4: Executing Different SQL Operations in One Statement Batch

Program 5: Batch Processing with Transaction Management

Program 6: Using clearBatch() Before Executing

Program 7: Dynamic Batch Processing Using an Array

Program 8: Batch Processing with Result Status

Program 9: Batch Processing in Groups

Related Posts