by Dinesh Thakur

A transaction is a group of operations used to perform a particular task. In a transaction if one operation fails then all operations of the transaction gets cancelled. Finally, the transaction status fails.

If all operations in a transaction are successfully executed, then only the transaction status is successful. A transaction follows the principle of -All or Nothing. For example, booking a movie ticket online is a transaction.

Transactions are of two types:

1. Local transaction

2. Distributed transaction (Global Transaction)

                    Type of Transactions

Local Transaction

Local transaction means all operations are executed on a single database. In Java, local transaction can be done by using JDBC, hibernate, Enterprise JavaBeans (EJB), spring framework.

                    Local Transaction

Distributed Transaction

Distributed transaction means the operations are executed on more than one database.

In Java, distributed transaction can be done either using EJB technology or by using spring framework.

                     Distributed Transaction

Transaction property

1. Atomicity

Every transaction is an atomic operation. It means "follow all or nothing principle". For example, in a transfer of money operation-in a bank, if an amount is withdrawn from account 1 and it is deposited to account 2. Here either both operations are done or both are cancelled. So it is an atomic operation.

2. Consistency

After the transaction is completed successfully or fails, the data left in the database, should be reliable. Reliable data is called consistent data.

3. Isolation

If two transaction are working on the same data then one transaction does not clash (collide) with the other. It means transaction is isolated.

4. Durability

The data generated after completion of transaction will be long-lasting data, until another transaction is done on that data. We call these data durable.

Transaction management method in JDBC

The following are the three methods given by Connection interface in JDBC to perform transaction management.

i.  setAutoCommit (false)

ii. commit ()

iii. rollback ()

From a Java program if any SQL operation is executed, it will be permanently executed on database by default. If operations are permanently executed in database then we cannot cancel them. So transaction management is not possible.

If we want to perform transaction management in JDBC program, we should execute operation on database as temporarily. Later, we can either commit or cancel the operation.

If we want to execute SQL operation temporarily from a Java program onto database, then first of all, we need to disable auto commit mode on JDBC program. To disable auto commit mode we need to call setAutocommit (-) method.

If execution occurs while executing database operation, then we need to cancel the transaction by calling rollback (-) method.

If all operations are successfully executed, then we need to commit the transaction by calling commit (-) method.

Need for try catch in transaction management

In JDBC program we need to put the operation of transaction within try block, because Java program knows whether an error has occurred in transaction or not by observing the control in the catch block.

If the control entereds the catch block, it means an error has occurred. So, we will rollback the transaction.

If the control does not enter the catch block, it means no exception has occurred. So we commit the transaction.

This account_balance is created by the following columns and data types on which

we are going to perform transaction management:

            Transaction Management in JDBC

The source code for the transaction management:

 

BankingTest.java

 

import java.sql.*;

import java.util.Scanner;

public class BankingTest

{

      public static void main (String args []) throws Exception

       {

             //to take the input from keyboard

             Scanner s = new Scanner (System.in);

             System.out.println ("Enter the Source account number :" );

             int saccno = s.nextInt ();

             System.out.println ("Enter the Destination account number :");  

             int daccno = s.nextInt ();

            System.out.println ("Enter the amount to transfer :");

            int amnt = s.nextInt ();

            Class.forName ("oracle.jdbc.driver.OracleDriver :");

            Connection cn = DriverManager.getConnection

            ("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

            Statement st = cn.createStatement ();

            cn.setAutoCommit (false);

            ResultSet rs = st.executeQuery ("select avail_balance from account_balance where account_number="+saccno);

            rs.next ();

            int abal = rs.getInt (1);

            //System.out.print (abal);

            if (abal>amnt)

            {

                 int up = st.executeUpdate ("update account_balance set avail_balance =avail_balance-"+amnt+ "where account_number="+saccno);

                 int up1 = st.executeUpdate ("update account_balance set avail_balance = avail_balance-"+amnt+ "where account_number="+daccno);

                 //System.out.print (up+" "+up1);

                 if (up==1 && up1==1)

                 {

                      cn.commit ();

                      System.out.println ("*******: "+amnt+" balance is successfully Transferred:*******");

                 }

                 else

                  {

                      cn.rollback ();

                      System.out.println ("rollback");

                 }

            }

            else

              {

                    System.out.println ("You does not have sufficient balance !!! please deposit in your account.");

              }

      }

}

In the above program:

cn.setAutoCommit (false): Used to disable auto commit mode. Auto commit mode is disabled before any transaction happens, so the any data cannot be stored automatically.

cn.commit () It commits the transaction after the successful execution of the transaction.

cn.rollback ()  If some problem occurs in the transaction, it cancels the entire transaction.

Transaction is performed by running the program. Here, transaction is successful. You can check it from the database table.

This program also shows error message when the transaction has more than the available balance.

                Transaction Management in JDBC

                Transaction Management in JDBC

               Transaction Management in JDBC