Transactions in Database Using JDBC API


Transactions are important part of any database oriented program. It is really necessary to keep the consistency of the DB. So Java, through the excellent JDBC API provide support for the atomic statement executions which we also know as Transactions

Transaction is a set of steps which must be executed in a whole. There can't be any type of inconsistency afforded in these steps. For example consider James wants to transfer the funds from his bank account to Kenny's account. Say $500 is to be transfer so the steps will be in the following steps.

Step 1: Deduct 500 from James's account
Step 2: Credit 500 in Kenny's account

So you see there are two steps, now consider the amount was deducted from James's account but before it was credited in the Kenny's account, Some error occurred and system crashed or say, the system denied the access for the Kenny's account. Now the problem is that, James's account was debited but Kenny's Account was not credited so $500 was lost. So this raise the need for the transaction.

The principle of Transaction is atomicity. That means either both statements will execute(debit+credit) or none will occur. This can be achieve through a special ability, most of DBMS provide and JDBC provide support for this feature. The feature is Commit and Rollback. first of all we will turn off the auto commit option of Java, that will prevent us from doing committing any changes we made, in case any exception occur we will be able to roll back the changes.

To demonstrate we are using a Database by the name of Banking, in which we have create a table by the name of Accounts which has two entries.

account_number 	Name 	account_type 	Balance
 	1 	James 	Savings 	1000
 	2 	Kenny 	Savings 	600

So we will be running two update statements on this table to debit James's account and credit the Kenny's Account. Firs we will show you how the inconsistency may arise.


package jdbc;

import java.sql.*;

public class Transaction {
    
    public void printResutlset(Connection con) throws SQLException{
        PreparedStatement ps = con.prepareStatement("select * from Account");
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            System.out.println();
            System.out.print("Account Number: "+rs.getInt("account_number")+" *** Name: "+rs.getString("name")+" *** Balance: "+rs.getInt("balance"));
            System.out.println();
        }
    }
    
    public static void main(String[] args){
        Connection conn = null;
        PreparedStatement debit,credit;
        debit = credit = null;
        Transaction t = new Transaction();
        try{
            //create connection
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost/Banking","root","");
            
            //show the table state
            t.printResutlset(conn);
            
            //execute debit
            debit = conn.prepareStatement("update Account set Balance=Balance-500 where account_number=1");
            int r = debit.executeUpdate();
            System.out.println("James's Account debited");
            
            //show tabel state again
            t.printResutlset(conn);
            
            //cause an exception
            int x = 12/0;
            
            credit = conn.prepareStatement("update Account set Balance=Balance+500 where account_number=2");
            int s = credit.executeUpdate();
            System.out.println("Kenny's Account Credited");
            
        }
        catch(Exception e){
            System.out.println(e);
        }
        finally{
            
        }
    }
}

First We will show you the result and then we will explain.

Account Number: 1 *** Name: James *** Balance: 1000

Account Number: 2 *** Name: Kenny *** Balance: 600
James's Account debited

Account Number: 1 *** Name: James *** Balance: 500

Account Number: 2 *** Name: Kenny *** Balance: 600
java.lang.ArithmeticException: / by zero

So you see, the output it never went to update the Kenny's Account and the James's account has been debited. So this loses $500 to James. In the program we have created a utility method printResultSet(Connection conn), which prints the entries from table in the way they are. And in program first we show you the original result set, then we execute the debit query and an exception which is purposefully created to deflect the program to create inconsistency in Database.

Now let us modify this program to ensure its atomicity. We will reset the database values to original before we proceed. In this program this is what we usually do:

1. set auto commit option to false, which is by default true.
2. run the statements under try catch blocks, so that in case of exception we can manage the situation.
3. do rollback() in the catch block, so that if any exception occurs we can roll back to the original values.
4. in finally block set auto commit to its default value again and close the connection.


package jdbc;

import java.sql.*;

public class Transaction {
    
    public void printResutlset(Connection con) throws SQLException{
        PreparedStatement ps = con.prepareStatement("select * from Account");
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            System.out.println();
            System.out.print("Account Number: "+rs.getInt("account_number")+" *** Name: "+rs.getString("name")+" *** Balance: "+rs.getInt("balance"));
            System.out.println();
        }
    }
    
    public static void main(String[] args){
        Connection conn = null;
        PreparedStatement debit,credit;
        debit = credit = null;
        Transaction t = new Transaction();
        try{
            //create connection
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost/Banking","root","");
            
            //show the table state
            t.printResutlset(conn);
            
            //turn off auto commit
            conn.setAutoCommit(false);
            
            //execute debit
            debit = conn.prepareStatement("update Account set Balance=Balance-500 where account_number=1");
            int r = debit.executeUpdate();
            System.out.println("James's Account debited");
            
            //show table state again
            t.printResutlset(conn);
            
            //cause an exception
            int x = 12/0;
            
            credit = conn.prepareStatement("update Account set Balance=Balance+500 where account_number=2");
            int s = credit.executeUpdate();
            System.out.println("Kenny's Account Credited");
            
	    //commit the changes
	    conn.commit();
        }
        catch(Exception e){
            System.out.println("Exception Caught: "+e);
            try{
                if(conn!=null){
                    System.out.println("doing rollback now");
                    conn.rollback();
                    
                    //show state again
                    t.printResutlset(conn);
                }
            }
            catch(SQLException se){
                System.out.println("Exception occurred during rollback: "+se);
            }
            
        }
        finally{
            try{
                if(conn!=null){
                    //set auto commit to default value
                    conn.setAutoCommit(true);
                    conn.close();
                }
            }
            catch(SQLException se){
                System.out.println("Exception occurred while closing connection: "+se);
            }
        }
    }
}

The result of this program goes as follows:

Account Number: 1 *** Name: James *** Balance: 1000

Account Number: 2 *** Name: Kenny *** Balance: 600
James's Account debited

Account Number: 1 *** Name: James *** Balance: 500

Account Number: 2 *** Name: Kenny *** Balance: 600
Exception Caught: java.lang.ArithmeticException: / by zero
doing rollback now

Account Number: 1 *** Name: James *** Balance: 1000

Account Number: 2 *** Name: Kenny *** Balance: 600

As you can see after the exception occurred we rolled back and the values in database restored themselves. This is the basic and nearly quite much to know about the Transactions. And of course if there would have been no exception occurred at all in that case commit() would have finalized the results.