JDBC Batch Processing Example

Batch processing mechanism provides a way to create group of related database statements in to a batch and execute them with a call to the database.

Batch processing mechanism provides a way to create group of related database statements in to a batch and execute them with a call to the database.

JDBC Batch Processing Example:

Batch processing mechanism provides a way to create group of related database statements in to a batch and execute them with a call to the database. By using batch processing you can reduce the extra communication overhead and improve application performance.

In this example, you can learn about Batch processing and these few methods. The addBatch() method used to add individual statement in to the batch and executeBatch() method used to execute batch and the setAutoCommit() method used to set auto commit false or true.

Now we will create a java class JDBCBatchProcess.java and create connection with MySql data source as:



Connection connection = 
null;

Class.forName("com.mysql.jdbc.Driver");

connection = DriverManager.getConnection
("jdbc:mysql://localhost:3306/roseindia_jdbc_tutorials","root","root");

Now we will create Statement and set auto commit false as:



Statement stmt = connection.createStatement();
connection.setAutoCommit(false);

Now we will create SQL statements and add these statements in the batch as:



stmt.addBatch(query);

Now we will execute batch as:



stmt.executeBatch();

And last we will commit the connection as:



connection.commit();

The code of the JDBCBatchProcess.java class is:

import java.sql.*;
public class JDBCBatchProcess {
  public static void main(String[] args) {
    try{
     Connection connection = null;
     Class.forName("com.mysql.jdbc.Driver");
     connection = DriverManager.getConnection(
         "jdbc:mysql://localhost:3306/roseindia_jdbc_tutorial",
         "root","root");
          try{
            // Create statement object
            Statement stmt = connection.createStatement();
            // Set auto-commit to false
            connection.setAutoCommit(false);
            // create table query
            String query1 = "CREATE TABLE user(user_id integer, user_name varchar(20))";
            stmt.addBatch(query1);
            // insert query
            String query2 = "INSERT INTO user (user_id, user_name) VALUES(1,'Brijesh')";
            stmt.addBatch(query2);
            // insert query
            String query3 = "INSERT INTO user (user_id, user_name) VALUES(2,'Raj Singh')";
            stmt.addBatch(query3);
            // insert query
            String query4 = "INSERT INTO user (user_id, user_name) VALUES(3,'Ankit')";
            stmt.addBatch(query4);
            // insert query
            String query5 = "INSERT INTO user (user_id, user_name) VALUES(4,'Raj')";
            stmt.addBatch(query5);
            // Update row value query
            String query6 = "UPDATE user SET user_name = 'Ravi' WHERE user_id = 1";
            stmt.addBatch(query6);
            // delete record from the table query
            String query7 = "Delete from user where user_id=3";
            stmt.addBatch(query7);
            stmt.executeBatch();
            System.out.println("Batch processing is done" );
            // connection commited
            connection.commit();
        }
        catch (SQLException s){
          System.out.println("SQL Exception " + s);
        }
      }
      catch (Exception e){
        e.printStackTrace();
      }
    }
}

In this example, we have used create table, insert, update, delete SQL statements and execute with batch. Now we will run this example on eclipse.

Program output:

The console output is: