Batch Inserts Using JDBC Statements

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

Batch inserts using Statement object

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

• Add statements to the batch − Prepare the INSERT quires one by one and add them to batch using the addBatch() method of the Statement Interface as shown below −

String insert1 = Insert into table_name values(value1, value2, value3, ......);
stmt.addBatch(insert1);
String insert2 = Insert into table_name values(value1, value2, value3, ......);
stmt.addBatch(insert2);
String insert3 = Insert into table_name values(value1, value2, value3, ......);
stmt.addBatch(insert3);

• Execute the batch − After adding the required statements, you need to execute the batch using the executeBatch() method of the Statement interface.

stmt.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.Statement;
public class BatchUpdates {
   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......");
      //Creating a Statement object
      Statement stmt = con.createStatement();
      //Setting auto-commit false
      con.setAutoCommit(false);
      //Statements to insert records
      String insert1 = "INSERT INTO Dispatches VALUES ('KeyBoard', 'Amith', 'January', 1000, 'Hyderabad')";
      String insert2 = "INSERT INTO Dispatches VALUES ('Earphones', 'SUMITH', 'March', 500, 'Vishakhapatnam')";
      String insert3 = "INSERT INTO Dispatches VALUES ('Mouse', 'Sudha', 'September', 200, 'Vijayawada')";
      //Adding the statements to batch
      stmt.addBatch(insert1);
      stmt.addBatch(insert2);
      stmt.addBatch(insert3);
      //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     | 
+--------------+------------------+-------------------+-------+----------------+