SAVE POINT in JDBC

The save point is a logical position in a transaction up to which we can rollback the transaction. When the save point is placed in the middle of the transaction, the logics placed before the save point will be committed and the logics placed after the save point will be rolled back. There are two types of save points:

1. Named save point

2. Unnamed save point

The save point object is the object of a class given by JDBC driver implementing java.sql.Savepoint interface.

When the save point is placed in the transaction we cannot reference it from connection.commit (), but it can be referenced from connection.rollback (). That means we cannot commit up to save point position, but we can roll back up to save point position.

Source codes for save point testing program.

SavepointDemo.java

import java.sql.*;

public class SavepointDemo 

{

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

          {

                  Class.forName (“oracle.jdbc.driver.oracleDriver”);

                  Connection con = DriverManager.getConnection

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

                  Statement st = con.createStatement ();

                  con.setAutoCommit (false);

                  st.executeUpdate (“insert into college values (‘olamipa’, ‘mumbai’, 1009)”);   

                  Savepoint svpt = con.setSavepoint (“mysp”);

                  st.executeUpdate (“update college set cname= ‘xavier’ where code=1001”);

                 st.executeUpdate (“insert into college values (‘KIIT’, ‘Bhubaneswar’, 1008)”);

                 con.rollback (svpt);

                 con.commit ();

           }

}

Savepoint svpt=con.setSavepoint (“mysp”): After execution of some database operation the save point is created at this position, and any name can be passed as string.

con.rollback (svpt): When we call rollback on save point instance, the transaction before the save point is saved but the transaction after the save point is not performed.

Running the Program

We can confirm from the database table.