14.1 Overview of JDBC batch processing

In earlier chapters we discussed how to execute the Queries on a database using Statement and Prepared Statements. When we execute any query to database, there are certain operations take place at backend like opening connection/ closing connection which we used to handle but opening and closing of connections are expensive.

Also sometimes we would need to execute several statements in a batch which means we can save the database calls. Both Statement and PreparedStatement supports batch updates.

By default the auto-commit mode of a JDBC Connection is true which means database commits everything to database after every Data manipulations statement.

Whenever we execute batch statements, we must set the auto-commit mode to false.

14.2 JDBC Batch Processing API

As mentioned above, both Statement and PreparedStatements supports batch processing. To do so, there are below methods-

1. addBatch(String Sql) – This is an API for Statement and it takes an String which is SQL which need to be fired to database and will be added to batch.
2. addBatch() – This is an API for PreparedStatement
3. int[ ] executeBatch() – This is an API for both PreparedStatement and Statement and is used to execute the SQL statements added in a batch. As a result, this method returns an array of integers which contains number of rows affected corresponding to each batch statement.
4. clearBatch() – This API is used to clear all the batch statements added.

14.3 JDBC Batch Examples

We will use the MySQL database .

1.  Use the below sql statement to create a schema

        Create schema JDBCTutorial;

2. Create table User with 4 columns using below SQL

      CREATE  TABLE `User` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NULL ,  `age` VARCHAR(45) NULL ,  `email` VARCHAR(45) NULL ,  PRIMARY KEY (`id`) );

14.3.1 – Write and Example to insert the new Users in a batch processing using Statement.

Solution –

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

public class BatchInsertExample {
 
    public static void main(String args[])
    {
        try
        {
           Connection conn= getConnection();

           Statement stmt = conn.createStatement();

           conn.setAutoCommit(false);

           String sql = "insert into User (name, age , email) values ('User10', '23','u10@gmail.com')";

           stmt.addBatch(sql);

           sql = "insert into User (name, age , email) values ('User11', '24','u11@gmail.com')";
            stmt.addBatch(sql);
           sql = "insert into User (name, age , email) values ('User12', '24','u12@gmail.com')";
            stmt.addBatch(sql);
           sql = "insert into User (name, age , email) values ('User13', '24','u13@gmail.com')";
            stmt.addBatch(sql);

           stmt.executeBatch();

           conn.commit();
            stmt.close();
            conn.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    private static Connection getConnection() {

        Connection con = null;
        String dbDriver = "com.mysql.jdbc.Driver";
        String dbUsername = "root";
        String dbPassword="password";
        String dbHostname="localhost";
        String dbPort="3306";
        String schema="JdbcTutorial";
        try {
               String     url = "jdbc:mysql://"+dbHostname+":"+dbPort+"/"+schema;
               Class.forName(dbDriver);

               con = DriverManager.getConnection(url, dbUsername, dbPassword);
        }
        catch (ClassNotFoundException ex1)
        {
            System.out.println("Failed to find driver class " + ex1.getMessage());
            System.exit(1);
        }
        catch (SQLException ex2) {
            System.out.println("Connection failed " + ex2.getMessage());
            System.exit(2);
        }
        return con;
    }
}

On Running the above program, it will insert four new users. Refer below

14.3.2 – Write and Example to insert the new Users in a batch processing using Prepared Statement.

Solution –

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

public class BatchInsertExample {

    public static void main(String args[])
    {
        try
        {

           Connection conn= getConnection();
           String sql = "insert into User (name, age , email) values (?,?,?)";
           PreparedStatement stmt = conn.prepareStatement(sql);

           conn.setAutoCommit(false);

           stmt.setString(1,"User10");
           stmt.setString(2,"23");
           stmt.setString(3,"u10@gmail.com");

           stmt.addBatch();

           stmt.setString(1,"User11");
           stmt.setString(2,"24");
           stmt.setString(3,"u11@gmail.com");
 
           stmt.addBatch();
           stmt.setString(1,"User12");
           stmt.setString(2,"24");
           stmt.setString(3,"u12@gmail.com");

           stmt.addBatch();
           stmt.setString(1,"User13");
           stmt.setString(2,"24");
           stmt.setString(3,"u13@gmail.com");
           stmt.addBatch();

           stmt.executeBatch();

           conn.commit();
           stmt.close();
           conn.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    private static Connection getConnection() {

       Connection con = null;
       String dbDriver = "com.mysql.jdbc.Driver";
       String dbUsername = "root";
       String dbPassword="password";
       String dbHostname="localhost";
       String dbPort="3306";
       String schema="JdbcTutorial";
       try {

              String     url = "jdbc:mysql://"+dbHostname+":"+dbPort+"/"+schema;
             Class.forName(dbDriver);

             con = DriverManager.getConnection(url, dbUsername, dbPassword);
        }
        catch (ClassNotFoundException ex1)
        {
            System.out.println("Failed to find driver class " + ex1.getMessage());
            System.exit(1);
        }
        catch (SQLException ex2) {
            System.out.println("Connection failed " + ex2.getMessage());
            System.exit(2);
        }
        return con;
    }
}

On running the above program, it will insert four new users. Refer below