Transactions with JDBC

Transactions JDBC

Transactions should have ACID properties. In a transaction, all the operations on the database should succeed; even one operation fails, all the operations in the transaction should be rolled back. This is the motto of Transactions JDBC.

Think there is a transaction where a cheque is credited (amount added to my bank account) and a cheque given by me to some party is to be debited (amount deducted). If the credit operation fails, the debit should not take place. That is, both should get succeeded or failed. This is how to do in JDBC.

Let us apply the above idea to our Employee table. I would like to insert a record, update a record and delete a record. Either all should be executed or even one fails all operations to be aborted (rolled back). Observe the following code.

stmt.executeUpdate("insert into Employee values(100, 'S N Rao'. 5500.50)"); 
stmt.executeUpdate("update Employee set empsal=8000.50 where empid=101");
stmt.executeUpdate("delete from Employee where empid=103");

The above 3 statements are without transactions applied. The disadvantage is even 103 record is not deleted insert and update operations will proceed.

Let us write the above three statements with transaction success guaranteed code.

Observe the following operations on Transactions JDBC

import java.sql.*;
public class FileInsert
{
  public static void main(String args[])
  {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:snrao", "scott", "tiger");
    Statement stmt = con.createStatement();

    con.setAutoCommit(false);
    try
    {
      stmt.executeUpdate("insert into Employee values(100, 'S N Rao'. 5500.50)"); 
      stmt.executeUpdate("update Employee set empsal=8000.50 where empid=101");
      stmt.executeUpdate("delete from Employee where empid=103");

      con.commit();                                // Transactions JDBC, commit the operation

      stmt.close();
      con.close();
    }
    catch(Exception e)
    {
      con.rollback();                              // Transactions JDBC, rollback the operation
      System.out.println("All the JDBC operations are rolled back " + e);
    }
  }
}

The best place for close operations is finally block. In realtime, the Hibernate framework takes care of all this along with connection pooling and cache mechanisms.

con.setAutoCommit(false);

First do not allow any auto commit transactions. For this reason, set the auto commit to false.

con.commit();

If no SQLException (handled by super class Exception) is raised, all the operations are committed.

con.rollback();

If any one fails, all are aborted with rollback() method in catch block. Observe, commit() is called at the end of all the operations.