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

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 PreparedStatement 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 PreparedStatement object using the prepareStatement() method of the Connection interface. Pass a query (insert) to it with place holders (?) in it.

• Set values to the place holders in the above created statement using the setter methods of the PreparedStatement 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    |       |
+-------------------+--------------+------+-----+---------+-------+

Following program inserts data into this table using batch processing (with prepared statement object).

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class BatchProcessing_PreparedStatement {
   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......");
      //Setting auto-commit false
      con.setAutoCommit(false);
      //Creating a PreparedStatement object
      PreparedStatement pstmt = con.prepareStatement("INSERT INTO Dispatches VALUES (?, ?, ?, ?, ?)");
      pstmt.setString(1, "Keyboard");
      pstmt.setString(2, "Amith");
      pstmt.setString(3, "January");
      pstmt.setInt(4, 1000);
      pstmt.setString(5, "Hyderabad");
      pstmt.addBatch();
      pstmt.setString(1, "Earphones");
      pstmt.setString(2, "Sumith");
      pstmt.setString(3, "March");
      pstmt.setInt(4, 500);
      pstmt.setString(5,"Vishakhapatnam");
      pstmt.addBatch();
      pstmt.setString(1, "Mouse");
      pstmt.setString(2, "Sudha");
      pstmt.setString(3, "September");
      pstmt.setInt(4, 200);
      pstmt.setString(5, "Vijayawada");
      pstmt.addBatch();
      //Executing the batch
      pstmt.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     |
+--------------+------------------+-------------------+-------+----------------+

Daniol Thomas

Published on 25-Mar-2019 06:26:36

• Related Questions & Answers
• Write an example JDBC program demonstrating the batch processing with statement object?
• Write an example JDBC program demonstrating the batch processing with CallableStatement object?
• What is batch processing in JDBC?
• What is Parameterized Batch Update in JDBC? Explain with an example?
• What is PreparedStatement in JDBC?
• Write a C program demonstrating strlen library function
• Write a C program demonstrating examples on pointers
• Batch Inserts Using JDBC Statements
• What are the advantages and limitations of JDBC PreparedStatement?
• Batch Inserts Using JDBC Prepared Statements
• Write an JDBC example to retrieve Clob value from a table using the getCharacterStream() method?
• Write an JDBC example for inserting value for Blob datatype into a table?
• How to process SQL statements with JDBC explain with an example?
• What are batch updates in JDBC? Explain?
• Write an example program on structure using C language