Batch Inserts Using JDBC Prepared Statements

Grouping a set of INSERT Statements and executing them at once is known as batch insert.

Batch inserts using PreparedStatement object

To execute a batch of insert statements using the PreparedStatement object −

• Create a PreparedStatement − Create a PreparedStatement object using the prepareStatement() method. Pass the Insert query with place holders “?” instead of values as a parameter to this method.

PreparedStatement pstmt = con.prepareStatement("INSERT INTO Sales VALUES (?, ?, ?, ?, ?)");

• Set the values to the place holders − Using the setXXX() methods (setInt(). SetString(), setFloat() etc…) set the values to the place holders in the PrepareStatement as −

pstmt.setString(1, "KeyBoard");
pstmt.setString(2, "Amith");
pstmt.setString(3, "January");
pstmt.setInt(4, 1000);
pstmt.setString(5, "Hyderabad");

• Add statements to the batch − After setting the values of a record to the place holders add it to the batch using the addBatch() method of the PreparedStatement interface as −

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, 500);
pstmt.setString(5, "Vishakhapatnam");
pstmt.addBatch();

• Execute the batch − Finally execute the batch using the executeBatch() method of the PreparedStatement interface.

pstmt.executeBatch();

Using batch inserts, we can reduce the communication overhead and increase the performance of our Java application.

Note − Before adding statements to the batch you need to turn the auto commit off using the con.setAutoCommit(false) and, after executing the batch you need to save the changes using the con.commit() method.

Example

Let us create a table with name Dispatches in the MySQL database using the CREATE statement as shown below −

CREATE table Dispatches (
   Product_Name, varchar(255)
   Name_Of_Customer, varchar(255)
   Month_Of_Dispatch, varchar(255)
   Price, int(11)
   Location, varchar(255)
);

Following JDBC program tries to execute a bunch of INSERT statements at once as a batch, using a Statement object.

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
public class BatchInserts_PreparedStatement {
   public static void main(String args[])throws Exception {
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/testDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating a Statement object
      Statement stmt = con.createStatement();
      //Setting auto-commit false
      con.setAutoCommit(false);
      PreparedStatement pstmt = con.prepareStatement("INSERT INTO Sales 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, 500);
      pstmt.setString(5, "Vishakhapatnam");
      pstmt.addBatch();
      //Executing the batch
      stmt.executeBatch();
      //Saving the changes
      con.commit();
      System.out.println("Records inserted......");
   }
}

Output

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

If you verify the contents of the table, you can find the newly inserted record 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     |
+--------------+------------------+-------------------+-------+----------------+