What are batch updates in JDBC? Explain?

Grouping a set of INSERT or, UPDATE or, DELETE commands (those produce update count value) and execute them at once this mechanism is known as a batch update.

Adding statements to the batch

The statement, PreparedStatement, and CallableStatement objects hold a list (of commands) to which you can add related statements (those return update count value) using the addBatch() method.

stmt.addBatch(insert1);
stmt.addBatch(insert2);
stmt.addBatch(insert3);

Executing the batch

After adding the required statements, you can execute a batch using the executeBatch() method of the Statement interface.

stmt.executeBatch();

Using batch updates, 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

Assume we have created a table named Sales in the database with the following 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    |       |
+-------------------+--------------+------+-----+---------+-------+

This Example tries to insert a set of statements into the above mentioned table using batch update.

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 inserted records in it 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 |
+--------------+------------------+-------------------+-------+----------------+