82 Advanced Java Tutorial | JDBC | Savepoint | Java Database Connectivity | adv java

Advanced Java Tutorial JDBC: adv java : Savepoint How to perform savepoint operation in JDBC? SavePoint: (Feature of JDBC 3.0) Understanding the need of Savepoint: ---------------------------------- Senario 1: --------- Begin Transaction operation 1 operation 2 ....... operation n commit; Transaction All operations will be committed in Database software. Senario 2: --------- Begin Transaction operation 1 operation 2 ....... operation n Rollback Transaction All operations will be rolled back in Database software. Senario 3: --------- Begin Transaction operation 1 operation 2 savepoint (p) Operation 3............ operation n Rollback from (p) Operation 1, Operation 2 will be commited and operation 3 will be roll back in Database software. Savepoint is logical position in a transactions upto which we can roll back the transaction. When save point is placed in the middle of the transactions the logic placed before savepoint will be commited/saved & logics placed after save point will rolled back. There are two types of savepoint: 1) named savepoint 2) Un-named savepoint (The underlaying database software generates id for this savepoint dynamically) Savepoint object means its the object of a java class given by JDBC driver that implements java.sql.SavePoint interface. When savepoint placed in the transaction we can not refrernce it from con.commit(-) method but it can be referenced from con.rollback(-) method. That means we can commit upto savepoint position, but we can roll back upto savepoint position. Ex: Java JDBC working with savepoint to perform both commit & rollback operations in a single transactions. =================================================================== import java.sql.*; public class SavePointTest { public static void main(String[] args) throws Exception { //create the connection object Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "boss"); Statement st=con.createStatement(); //begin Transaction con.setAutoCommit(false); //operation 1 st.executeUpdate("insert into student values(9976,'yogesh','abad')"); //op1 st.executeUpdate("insert into student values(9978,'bhosle','Omerga')");//op2 //create named savepoint Savepoint sp=con.setSavepoint("mysp"); ////op3 st.executeUpdate("update student set sname='patil' where sno=217"); ////op4 st.executeUpdate("delete from student where sno=456"); //rollback upto savepoint mysp con.rollback(sp); //commit con.commit();//op1,op2 will be commited & op3 , op 4 will be rolled back } }