JDBC FAQ Part15

33:What is meant by Transaction? How it is possible to maintain Transactions in JDBC applications?

• Transaction is nothing but an unit of work performed by the applications.
• Every transaction should have the following properties.
• Atomicity
• Consistency
• Isolation
• Durability

• Where atomicity is nothing but perform all the operations or not to perform all the operations in a transaction. That is every transaction must be in either success state or failure state.
• As part of the jdbc applications when we establish a connection automatically the connection should have a default nature called as “auto commit”.
• Auto commit in the sense when we send an sql query to the connection then connection will carry that to the DBE and make the DBE to execute provided sql query and store the results on the database permanently.
• The connections default auto commit nature violates the transactions atomicity property.
• To preserve transactions atomicity property we should change the connections auto commit nature to non-auto commit nature, for this we will use the following method.

Public void setAutoCommit(Boolean b)
Where b=true    connection is in auto commit
And b=false      connection not in auto commit.

• If we use connections non auto commit nature in our jdbc applications then we must use either commit or rollback operations explicitily as part of the transactions.

Public void commit()
Public void rollback()

The following example demonstrates how to maintain the transactions with atomicity property in the jdbc applications.

import java.sql.*;
public class TransactionEx
{
      public static void main(String[] args)throws Exception
      {
                  Connection con = null;
                  try
                  {
                              Class.forName(“sun.jdbc.odbd.JdbcOdbcDriver”);
Con = DriverMadoeer.getConnection(“jdbc:odbc:doe”,”system”,”jhon”);
con.setAutoCommit(“false”);
Statement st = con.createStatement();
st.executeUpdate(“insert into emp1 values(888,’fff’,8000,’hhh’)”);
st.executeUpdate(“update emp1 set esal = esal-500 where esal>= ‘abc’ “);
st.executeUpdate(“delete emp1 where esal<7000”);
con.commit();
                        }
                        catch(Exception e)
                        {
                                    con.rollback();
                                    System.out.println(e);
                        }
            }
      }

34:What is meant by SavePoint?How to use Savepoints in JDBC applications?

• Save point is a concept introduced by jdbc 3.0 which can be used to block a set of instructions execution in the transactions committing operation.
• To set a save point we will use the following method.

public SavePoint setSavePoint()

• To block a set of sql queries execution prior to the save point we will use the following method.

public void rollback(savepoint s)

• To release a savepoint we will use the following method

public void releaseSavePoint();

• SavePoint concept could not be supported be type1 driver, it could be supported by type4 driver.
• Even type 4 driver is supporting up to setSavePoint() and rollback() , not releaseSavepoint();

    
      Eg: 
      import java.sql.*;
public class SavePointEx
{
      public static void main(String[] args)throws Exception
      {
                  Connection con = null;
                  try
                  {
                              Class.forName(“oracle.jdbc.driver.OracleDriver”);
con = DriverMadoeer.getConnection(“jdbc:oracle:thin:@locajhost:1521:xe”,”system”,”jhon”);
con.setAutoCommit(“false”);
Statement st = con.createStatement();
st.executeUpdate(“insert into emp1 values(111,’fff’,8000,’hhh’)”);
savepoint sp= con.Savepoint();
st.executeUpdate(“insert into emp1 values(222,’ggg’,7000,’iii’) “);
con.rollback(sp);
st.executeUpdate(“insert into emp1 values(333,’hhh’,9000,’jjj’)”);
con.commit();
                        }
                        catch(Exception e)
                        {
                                    con.rollback();
                                    System.out.println(e);
                        }
            }
      }