Batch update in JDBC

Batch updates in JDBC

We need to use JDBC batch updates collectively when there is a situation where we have to execute a group of SQL statements simultaneously for a database in one go. Then all the update quarries will be sent to the database in a single request using connection object. 

Need for batch updates 

When the request for any updates arise from the front end to its database then there is a network call responsible for that.
Consider a situation that we are updating ten records in a table, if we do this in normal way then we have to use update query ten times. This causes ten network calls to the database from the front end. Due to this situation, extra traffic hits the network. If there are more than ten records, it means if there are a large number of records to be updated, this causes a database failure. To avoid this, a batch update technique is used. In this technique, we add the queries into a batch and execute the batch query in a single go rather than executing a single SQL statement.

Adding the queries to the batch 

After creating a connection to the back-end (database) we set auto commit false using the method setAutoCommit(false) with the connection object. By using this method we disable the auto-commit. Now we create a statement such as
Statement s = con.createStatement();
Now we add our update query to addUpdate() with the statement object
for example : s.addBatch("update table name set field name= 'value' where field name=' value' ");
After adding all the batches we execute our batch in a single go by using the method executeUpdate() method with statement object.
In this way, we complete our batch update process.

Simple code for batch update

Before executing this program the employee table is
MySQL output before program
  1. import java.sql.*;  
  3. public class BatchUpdate  
  4. {  
  5.  public static void main(String sss[])  
  6.  {  
  7.   try  
  8.   {  
  9.    Class.forName("com.mysql.jdbc.Driver");  
  10.    Connection con = DriverManager.getConnection("jdbc:mysql://localhost/sqldatabase""amitabh""amitabh");  
  11.    con.setAutoCommit(false);  
  12.    Statement s = con.createStatement();  
  13.    String u1 = "update employee set emp_name ='Ajay Garg' where emp_id='1'";  
  14.    String u2 = "update employee set emp_name = 'Robert connings' where emp_id='2'";  
  15.    String u3 = "update employee set emp_name = 'Sandeep Ahuja' where emp_id='3'";  
  16.    s.addBatch(u1);  
  17.    s.addBatch(u2);  
  18.    s.addBatch(u3);  
  19.    s.executeBatch();  
  20.    ResultSet rs = s.executeQuery("select * from employee");  
  21.    while (  
  22.    {  
  23.     System.out.println("id " + rs.getString(1));  
  24.     System.out.println("name: " + rs.getString(2));  
  25.     System.out.println("address: " + rs.getString(3));  
  26.    }  
  27.    rs.close();  
  28.    s.close(); // close the Statement to let the database know we're done with it  
  29.    con.close(); // close the Connection to let the database know we're done with it  
  30.   }   
  31.   catch (Exception err)  
  32.   {  
  33.    err.printStackTrace();  
  34.   }  
  35.  }  
  36. }   
After executing this program we get the following output on the console window
console output after batch update
After executing this program the employee table is
MySQL window after batch update