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

  

 

import java.sql.*;

public class BatchUpdate
{
 public static void main(String sss[])
 {
  try
  {
   Class.forName("com.mysql.jdbc.Driver");
   Connection con = DriverManager.getConnection("jdbc:mysql://localhost/sqldatabase", "amitabh", "amitabh");
   con.setAutoCommit(false);
   Statement s = con.createStatement();
   String u1 = "update employee set emp_name ='Ajay Garg' where emp_id='1'";
   String u2 = "update employee set emp_name = 'Robert connings' where emp_id='2'";
   String u3 = "update employee set emp_name = 'Sandeep Ahuja' where emp_id='3'";
   s.addBatch(u1);
   s.addBatch(u2);
   s.addBatch(u3);
   s.executeBatch();
   ResultSet rs = s.executeQuery("select * from employee");
   while (rs.next())
   {
    System.out.println("id " + rs.getString(1));
    System.out.println("name: " + rs.getString(2));
    System.out.println("address: " + rs.getString(3));
   }
   rs.close();
   s.close(); // close the Statement to let the database know we're done with it
   con.close(); // close the Connection to let the database know we're done with it
  }
  catch (Exception err)
  {
   err.printStackTrace();
  }
 }
}

After executing this program we get the following output on the console window

  

  

After executing this program the employee table is