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 quaries will 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 record 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 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 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 console window



 After executing this program the employee table is