JDBC transactions

Let's consider that we're working on a program which works on two tables STUDENT, STUDENT_ADDRESS. A student should definitely have an address associated. So, we should insert both STUDENT table records and STUDENT_ADDRESS table record as part of a single transaction. i.e.; if there is an error while inserting the STUDENT_ADDRESS, we should rollback the STUDENT table record as well.

JDBC allows us to handle this either by using setAutoCommit(false) or by using batch updates.

1. setAutoCommit(false):

setAutoCommit() is a method which has been defined as part of Connection interface to allow programmer to specify whether to commit the changes automatically or not. By default, it has been configured to set as true. So, if you'll have to use this method, when you want to go for transaction concept. But please be aware that you should invoke commit method explicitly.

TransactionDemo

/**
 * A demo class to explain setAutoCommit method
 * @author Santhosh Reddy Mandadi
 * @since 12-Jun-2012
 * @version 1.0
 */

import java.sql.*;

public class TransactionDemo
{
 public static void main(String[] args) throws Exception
 {
  Class.forName("oracle.jdbc.driver.OracleDriver");
  Connection connection = DriverManager.getConnection("jdbc:oracle:@localhost:1521:xe","scott","tiger");
  String vsql;
  Statement statement = connection.createStatement();
  connection.setAutoCommit(false);
  try
  {
   statement.execute("INSERT INTO STUDENT VALUES(1, 'Ravi', 10)");
   statement.execute("INSERT INTO STUDENT_ADDRESS VALUES(1, 'Addr1', 'Addr2', 'City')");
   connection.commit();
  }
  catch(Exception e)
  {
   connection.rollback();
  }
 }
}

2. Batch updates

We can use batch updates in the situation where we've to execute more than one non select SQL statement in the DB server. This will improve the application performance as well since all the updates will take place in a single shot. Javasoft has defined clearBatch(), addBatch(), and executeBatch() methods as part of the Statement interface to implement batch updates.

TransactionDemo

/**
 * A demo class to explain batch updates
 * @author Santhosh Reddy Mandadi
 * @since 12-Jun-2012
 * @version 1.0
 */

import java.sql.*;

public class TransactionDemo1
{
 public static void main(String[] args) throws Exception
 {
  Class.forName("oracle.jdbc.driver.OracleDriver");
  Connection connection = DriverManager.getConnection("jdbc:oracle:@localhost:1521:xe","scott","tiger");
  String vsql;
  Statement statement = connection.createStatement();
  try
  {
   statement.clearBatch();
   statement.addBatch("INSERT INTO STUDENT VALUES(1, 'Ravi', 10)");
   statement.addBatch("INSERT INTO STUDENT_ADDRESS VALUES(1, 'Addr1', 'Addr2', 'City')");
   statement.executeBatch();
   connection.commit();
  }
  catch(Exception e)
  {
   connection.rollback();
  }
 }
}