Batch Processing In JDBC

Batch Processing allows you to group related SQL statements into a batch and submit them with one call to the database.
When can  improving performance by send several SQL statements to the database at once and  reduce the amount of communication overhead.

Batch Processing Have Three Methods.They are

The addBatch() method of Statement 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.

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.

Batching with Statement Object:
Here is a typical sequence of steps to use Batch Processing with Statment 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 JdbcExecuteStatement {

  public static void main(String args[]) {
  Connection conn = null;
  String SQL="";
  String name="fd";
  String url = "jdbc:mysql://localhost:3306/test";
  String driver = "com.mysql.jdbc.Driver";
  String user = "root";
  String pass = "root";
  try {
 Class.forName(driver);
 conn = DriverManager.getConnection(url, user, pass);
// Create statement object
 Statement stmt = conn.createStatement();
 // Set auto-commit to false
          conn.setAutoCommit(false);
 // Create SQL statement
  SQL = "INSERT INTO Abc (no, name) " +
              "VALUES(200,'Zia')";
 // Add above SQL statement in the batch.
 stmt.addBatch(SQL);
 // Create one more SQL statement
  SQL = "INSERT INTO Abc (no, name) " +
              "VALUES(201,'Raj')";
 stmt.addBatch(SQL);
  // Add above SQL statement in the batch.
   SQL = "INSERT INTO Abc (no, name) " +
              "VALUES(203,'kumar')";
 // Add above SQL statement in the batch.
 stmt.addBatch(SQL);
 // Create one more SQL statement
  SQL = "UPDATE Abc SET name='"+name+"' " +
              "WHERE no = 1";
 // Add above SQL statement in the batch.
 stmt.addBatch(SQL);
 // Create an int[] to hold returned values
 int[] count=  stmt.executeBatch();
 //Explicitly commit statements to apply changes
 //conn.commit();

  } catch (Exception e) {
  System.out.println(e);

  }
  }
}

This above  Program is insert multiple  rows and updating multiple rows at once .