Transactions in JDBC

Introduction to Transactions

  • Initially when a database connection is created, it is in auto commit mode. The SQL statement is committed automatically when it is completed, not when it is executed.
  • A transaction is set of actions to be carried out as a single, atomic action.
  • The transaction is described with the ACID properties. ACID stands for Atomicity, Consistency, Isolation and Durability.
  • Transaction provides the control when any changes are applied to the database.
The Connection interface provides the following methods to manage transaction:

1. voidsetAutoCommit(boolean status)
It provides the manual transaction support. If the status is true, it is committed automatically. When the parameter of setAutoCommit ( ) methods is false, it will turn off the auto-commit mode.

For example:
conn.setAutoCommit(false);

2. void commit( )
This method commit all the changes which is performed in database.

For example:
con.commit( );

3. void rollback( )
If any action fails during transaction, then we use rollback( ) method. It cancels the transaction.

For example:
conn.rollback( );

Example : Different methods in Transaction using Statement

InsertDemo.java

import java.sql.*;
classInsertDemo
{
    public static void main(String args[])throws Exception
    {
        Connection conn = null;
        Statement stmt = null;
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "scott", "tiger");
            conn.setAutoCommit(false);
            stmt = conn.createStatement( );
            stmt.executeUpdate("insert into Student values (101, 'Surendra', 'MCA')");
            stmt.executeUpdate("insert into Student values (102, 'Pravin', 'MCA')");
            stmt.executeUpdate("insert into Student values(105, 'Vinod', 'BE')");
            conn.commit();
        }
        catch(SQLException e)
        {
            Conn.rollback();
        }
        finally
        {
            try
            {
                if(conn != null)
                {
                     conn.close();
                }
            }
            catch(SQLException e)
            {
                e.printStackTrace();
            }
                try
                {
                     if(stmt != null)
                     {
                          stmt.close();
                     }
                }
                catch(SQLException e)
                {
                     e.printStackTrace();
                }
        }
    }
}