Batch Processing allows you to group related SQL statements into a batch and submit them with one call to the database.

When you send several SQL statements to the database at once, you reduce the amount of communication overhead, thereby improving performance.

• JDBC drivers are not required to support this feature. You should use the DatabaseMetaData.supportsBatchUpdates() method to determine if the target database supports batch update processing. The method returns true if your JDBC driver supports this feature.
• 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.
• The executeBatch() returns an array of integers, and each element of the array represents the update count for the respective update statement.
• Just as you can add statements to a batch for processing, you can remove them with the clearBatch() method. This method removes all the statements you added with the addBatch() method. However, you cannot selectively choose which statement to remove.

Batch Processing with Statement Object:

Here is a typical sequence of steps to use Batch Processing with Statement Object −

• Create a Statement object using either createStatement() methods.
• Set auto-commit to false using setAutoCommit().
• Add as many as SQL statements you like into batch using addBatch()method on created statement object.
• Execute all the SQL statements using executeBatch() method on created statement object.
• Finally, commit all the changes using commit() method.

Example:

import java.sql.*;

public class BatchUpdate

{

public static void main(String[] args)

       { try

      {

      Class.forName(“com.mysql.cj.jdbc.Driver”);  

Connection con=DriverManager.getConnection(“jdbc:mysql://localhost:3306/sydb”,”root”,”pass123″);  

Statement st = con.createStatement();      

con.setAutoCommit(false);

       String query=”insert into account values(111,’King Khan’,25500,’Saving’)”;

       st.addBatch(query);

       query = “insert into account values(112,’Hrithik’,55500,’Saving’)”;

       st.addBatch(query);

query =”update account set accname=’Queen Khan’,balance=60000 where accno=112″;

st.addBatch(query);;

       int[] count = st.executeBatch();

       con.commit();

       st.close();

       con.close();

    }

catch(Exception e)

{

           e.printStackTrace();

    } 

System.out.println(“Goodbye!”);

}

}

Batch Processing with PrepareStatement

Here is a typical sequence of steps to use Batch Processing with PrepareStatement Object −

1. Create SQL statements with placeholders.
2. Create PrepareStatement object using either prepareStatement()methods.
3. Set auto-commit to false using setAutoCommit().
4. Add as many as SQL statements you like into batch using addBatch()method on created statement object.
5. Execute all the SQL statements using executeBatch() method on created statement object.
6. Finally, commit all the changes using commit() method.

Example:

import java.sql.*;

import java.util.*;

class PreparedStBatchUpdate

{

     public static void main(String[] args)

       {

try

      {  

      Class.forName(“com.mysql.cj.jdbc.Driver”);  

Connection con=DriverManager.getConnection(“jdbc:mysql://localhost:3306/sydb”,”root”,”pass123″);  

String query=”insert into account Values(?,?,?,?)”;

PreparedStatement ps=con.prepareStatement(query);

con.setAutoCommit(false);

ps.setInt(1,198);

ps.setString(2,”John”);

ps.setDouble (3,67890);

ps.setString(4,”saving”);

ps.addBatch();

ps.setInt(1,199);

ps.setString(2,”Joyce”);

ps.setDouble (3,56789);

ps.setString(4,”saving”);

ps.addBatch();

int[] count = ps.executeBatch();

       con.commit();

ps.close();

con.close();

System.out.println(“Done!”);

}

catch(Exception ex)

{

System.out.println(ex);

}

}

}