Batch Processing In Java(JDBC)

Batch Processing  help us to make a group of related  SQl statement into batch and commit them with  one call.

Step 1:

Firstly make connection 

Connection con = null;
        try {
             Class.forName("com.mysql.jdbc.Driver");
            String JDBC_DRIVER = "com.mysql.jdbc.Driver"; 
            String DB_URL = "jdbc:mysql://localhost:3306/DATABASENAME";
            String USER = "root";
             String PASS = "root";
              con=(ConnectionDriverManager.getConnection(DB_URL,USER,PASS);
           System.out.println("connectoin estblish");
          } catch (Exception e) {

        System.out.println("e"+e);
        }
   

Step 2:

         Make auto commit  false

         con.setAutoCommit(false);

Step 3:

• The addBatch() method of Statement, PreparedStatement, and CallableStatement is used to add individual statements to the batch. The executeBatch() is used to start the execution of all the statements grouped together.
• at  last commit use for save data in db.
  
  
  Example : Make a batch processing program in which we have 10 query and make batch of 2 query group and commit each group. and you hit a wrong query and see that the batch group of wrong query group  can not effect in db.But all other batch groups effected in database.
  
  Database Table:
   emailid Cloumn in table is primary key.
  
  
  package com.craterzone.login;
  
  import java.sql.SQLException;
  import java.util.*;
  import com.craterzone.utility.Connect;
  import com.craterzone.utility.Utility;
  import com.mysql.jdbc.Connection;
  import com.mysql.jdbc.Statement;
  
  public class Batch {
  public static void main(String[] args) throws SQLException {
      Connection con=Connect.getConnect();
      con.setAutoCommit(false);
      Statement st = (Statement) con.createStatement();
      ArrayList<String> query=new ArrayList<String>();
  //group 1
  
  query.add("insert into student(tokenid,name,emailid,password,city,country)values('yxz16','shivame','sh@gmai1l1.com','123','sre','india')");
      query.add("insert into student(tokenid,name,emailid,password,city,country)values('yxz17','shivame','sh@gmai1l2.com','123','sre','india')");
      //group 2
      query.add("insert into student(tokenid,name,emailid,password,city,country)values('yxz18','hari','father@gmail.com','123','sre','india')");
      query.add("insert into student(tokenid,name,emailid,password,city,country)values('yxz19','shivame','sh@gmai4l.com','123','sre','india')");
      //group 3
      query.add("insert into student(tokenid,name,emailid,password,city,country)values('yxz20','shivame','sh@gmai5l.com','123','sre','india')");
      query.add("insert into student(tokenid,name,emailid,password,city,country)values('yxz21','shivame','sh@gmai6l.com','123','sre','india')");
      //group 4
      query.add("insert into student(tokenid,name,emailid,password,city,country)values('yxz22','shivame','sh@gmai7l.com','123','sre','india')");
      query.add("insert into student(tokenid,name,emailid,password,city,country)values('yxz23','shivame','sh@gmai8l.com','123','sre','india')");
      //group 5
      query.add("insert into student(tokenid,name,emailid,password,city,country)values('yxz24','sre','father@gmail.com','123','sre','india')");
      query.add("insert into student(tokenid,name,emailid,password,city,country)values('yxz25','shivame','sh@gmai12l.com','123','sre','india')");
  
   int count=0;
      Iterator< String> itr=query.iterator();
       while (itr.hasNext()) {
          String query1 = (String) itr.next();
          st.addBatch(query1);
          count++;
          if (count%Utility.BATCH_SIZE==0) {
              st.executeBatch();
              System.out.println("Commited "+count);
              con.commit();
              con.setAutoCommit(false);
          }
      }
      //con.commit();
      System.out.println("Commited all");
      st.close();
      con.close();
  }
  }
  
  Output:
  
  As   emailid Cloumn in table is primary key.So
  query.add("insert into student(tokenid,name,emailid,password,city,country)values('yxz24','sre','father@gmail.com','123','sre','india')"); this command is not right because father@gmail.com id we have already use and batch group 5 can not commit  in db.