Write an example JDBC program demonstrating the batch processing with CallableStatement object?

JDBCJava 8ProgrammingObject Oriented Programming

Grouping related SQL statements into a batch and executing/submitting them at once is known as batch processing. The Statement interface provides methods to perform batch processing such as addBatch(), executeBatch(), clearBatch().

Follow the steps given below to perform batch updates using the CallableStatement object:

  • Register the driver class using the registerDriver() method of the DriverManager class. Pass the driver class name to it, as a parameter.

  • Connect to the database using the getConnection() method of the DriverManager class. Passing URL (String), username (String), password (String) as parameters to it.

  • Set the auto-commit to false using setAutoCommit() method of the Connection interface.

  • Create a CallableStatement object using the prepareCall() method of the Connection interface. Pass a query (procedure call) to it with place holders (?) in it (to pass input parameters to the procedure).

  • Set values to the place holders in the above created statement using the setter methods of the CallableStatement interface.

  • Add the required statements to the batch using the addBatch() method of the Statement interface.

  • Execute the batch using the executeBatch() method. of the Statement interface.

  • Commit the changes made, using the commit() method of the Statement interface.

Example

Suppose we have created a table named Dispatches with the description

+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| Product_Name      | varchar(255) | YES  |     | NULL    |       |
| Name_Of_Customer  | varchar(255) | YES  |     | NULL    |       |
| Month_Of_Dispatch | varchar(255) | YES  |     | NULL    |       |
| Price             | int(11)      | YES  |     | NULL    |       |
| Location          | varchar(255) | YES  |     | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+

And we have created a procedure named myProcedure which stores values in the above created table as shown below:

Create procedure myProcedure (
   IN Product_Name VARCHAR(255),
   IN Name_Of_Customer VARCHAR(255),
   IN Month_Of_Dispatch VARCHAR(255),
   IN Price INT, IN Location VARCHAR(255))
BEGIN
   insert into Dispatches values ();
END//
   Query OK, 0 rows affected (0.00 sec)

Following program calls a procedure named myProcedure which inserts data into the Dispatches table. We are setting values to the callable statement using batch updates.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class BatchProcessing_CallableStatement {
   public static void main(String args[])throws Exception {
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //CREATE TABLE Dispatches( Product_Name VARCHAR(255), Name_Of_Customer
      VARCHAR(255), Month_Of_Dispatch VARCHAR(255), Price INT, Location VARCHAR(255));
      //Setting auto-commit false
      con.setAutoCommit(false);
      //Creating a PreparedStatement object
      CallableStatement cstmt = con.prepareCall("{call myProcedure(?, ?, ?, ?, ?)}");
      cstmt.setString(1, "Keyboard");
      cstmt.setString(2, "Amith");
      cstmt.setString(3, "January");
      cstmt.setInt(4, 1000);
      cstmt.setString(5, "Hyderabad");
      cstmt.addBatch();
      cstmt.setString(1, "Earphones");
      cstmt.setString(2, "Sumith");
      cstmt.setString(3, "March");
      cstmt.setInt(4, 500);
      cstmt.setString(5,"Vishakhapatnam");
      cstmt.addBatch();
      cstmt.setString(1, "Mouse");
      cstmt.setString(2, "Sudha");
      cstmt.setString(3, "September");
      cstmt.setInt(4, 200);
      cstmt.setString(5, "Vijayawada");
      cstmt.addBatch();
      //Executing the batch
      cstmt.executeBatch();
      //Saving the changes
      con.commit();
      System.out.println("Records inserted......");
   }
}

Output

Connection established......
Records inserted......

If you verify the contents of the Dispatches table, you can observe the inserted records as:

+--------------+------------------+-------------------+-------+----------------+
| Product_Name | Name_Of_Customer | Month_Of_Dispatch | Price | Location       |
+--------------+------------------+-------------------+-------+----------------+
| KeyBoard     | Amith            | January           | 1000  | Hyderabad      |
| Earphones    | SUMITH           | March             | 500   | Vishakhapatnam |
| Mouse        | Sudha            | September         | 200   | Vijayawada     |
+--------------+------------------+-------------------+-------+----------------+
raja
Published on 25-Mar-2019 11:07:51
Advertisements