Jdbc : batch update using PreparedStatement

In this post, I am going to explain how to perform batch operations using jdbc. A batch update is sending number of sql queries together to database for execution. Sending batch of queries to database is faster than sending one-by-one.

There are two ways to execute batch statements.

1.   Using Statement class

2.   Using PreparedStatement class.

Let’s say I had two tables “employee” and “employee_backup”. Let us assume “employee” table has 10000 records and I want to copy all the records to “employee_backup” table.

“employee” table looks like below.

Id	firstName	lastName

Create tables

CREATE TABLE employee(id int,firstName varchar(30),lastName varchar(30));

CREATE TABLE employee_backup(id int,firstName varchar(30),lastName varchar(30));

Below program copies all the records from “employee” table into employee_backup table. 

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class BatchExecutionEx {

    static int BATCH_RECORDS_COUNT = 100;

    /* Update username, password and driver details here */
    static String URL = "jdbc:mysql://localhost/sample1";
    static String USERNAME = "root";
    static String PASSWORD = "tiger";
    static Connection conn = null;

    /* Get database connection */
    static Connection getConnection() throws ClassNotFoundException, SQLException {
        if (conn == null) {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        }
        return conn;
    }

    /* Update employee_backup table with data from employee table */
    public static void updateEmployeeBackupTable() {
        int count = 0;

        String insertTableSQL = "INSERT INTO employee_backup (id, firstName, lastName) VALUES (?,?,?)";
        String getAllEmployees = "select * from employee";
        Connection conn = null;
        Savepoint savePoint = null;

        try {
            conn = getConnection();

            if (conn != null) {
                conn.setAutoCommit(false);
                Statement stmt = conn.createStatement();
                PreparedStatement insertIntoArchive = conn.prepareStatement(insertTableSQL);

                ResultSet rs = null;

                rs = stmt.executeQuery(getAllEmployees);

                while (rs.next()) {
                    count++;

                    int id = rs.getInt("id");
                    String firstName = rs.getString("firstName");
                    String lastName = rs.getString("lastName");

                    insertIntoArchive.setInt(1, id);
                    insertIntoArchive.setString(2, firstName);
                    insertIntoArchive.setString(3, lastName);

                    insertIntoArchive.addBatch();

                    if (count == BATCH_RECORDS_COUNT) {
                        count = 0;
                        int result1[] = insertIntoArchive.executeBatch();

                        for (int i : result1) {
                            if (i < 0) {
                                Logger.getLogger(ArchiveUserData.class.getName()).log(Level.INFO, "record is not inserted into backup table", result1[i]);
                            }
                        }
                        conn.commit();
                        savePoint = conn.setSavepoint();
                    }
                }
                
                stmt.close();
                insertIntoArchive.close();
                conn.close();
            }

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ArchiveUserData.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            if (conn != null) {
                try {
                    conn.rollback(savePoint);
                    conn.close();
                } catch (SQLException ex1) {
                    Logger.getLogger(ArchiveUserData.class.getName()).log(Level.SEVERE, null, ex1);
                }
            }

            Logger.getLogger(ArchiveUserData.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /* Insert 10000 records into employee table */
    static void insertIntoEmployeeTable() throws ClassNotFoundException, SQLException {
        String insertTableSQL = "INSERT INTO employee (id, firstName, lastName) VALUES (?,?,?)";
        Connection conn1 = getConnection();
        PreparedStatement stmt = conn1.prepareStatement(insertTableSQL);

        for (int i = 0; i < 10000; i++) {
            stmt.setInt(1, i);
            stmt.setString(2, "firstName" + i);
            stmt.setString(3, "lastName" + i);
            stmt.execute();
        }

        stmt.close();
    }

    public static void main(String args[]) throws ClassNotFoundException, SQLException {
        insertIntoEmployeeTable();
        updateEmployeeBackupTable();
    }
}

insertIntoEmployeeTable()

Method inserts 10000 records into employee table.

updateEmployeeBackupTable()

Method copies all recors from employee table to employee_backup table.

static int BATCH_RECORDS_COUNT = 100;

Application creates 100 records as one batch and executes.

Previous                                                 Next                                                 Home