JDBC Transactions Create Savepoint, rollback in transaction in java


In this tutorial we will learn how to Create Savepoint in transaction, how to rollback transaction to specific savepoint in transaction in java JDBC. And we will learn about deleting or removing savepoint from current transaction in java jdbc.



Savepoint methods in java jdbc>

setSavepoint method creates a savepoint with the no name in the current transaction in java.

setSavepoint(savepointName) method creates a savepoint with the specified savepointName in the current transaction in java JDBC.


Rollback methods in java jdbc>

rollback() method undoes all the transactions performed in the current transaction in java jdbc.

rollback(savepointName) method undoes all the transactions performed after specified savepointName in the current transaction in java.


Delete/remove Savepoint methods in java jdbc>

releaseSavepoint(savepointName) method deletes specified savepointName and all the subsequent savepoints from the current transaction in java.



JDBC Transactions : Example/ Programs to create Savepoint, rollback and commit in JDBC java

--Before executing java program execute these database scripts  >
create table EMPLOYEE(ID number(4), NAME varchar2(22));
insert into EMPLOYEE values(7, 'ankit');
insert into EMPLOYEE values(8, 'rohit');
commit;


Before execution of below program EMPLOYEE table will look like this >

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class SavepointRollbackExample {
   public static void main(String... arg) {
          Connection connection = null;
          PreparedStatement prepStmt = null;
          try {
                 // registering Oracle driver class
                 Class.forName("oracle.jdbc.driver.OracleDriver");
                 // getting connection
                 connection = DriverManager.getConnection(
                              "jdbc:oracle:thin:@localhost:1521:orcl",
                              "ankit", "Oracle123");
                 System.out.println("Connection established successfully!");
                 connection.setAutoCommit(false);
                
                
                 //SAVEPOINT 1
                 Savepoint savepoint1 = connection.setSavepoint();
                 System.out.println("\nSavepoint1 created");
                
                 prepStmt = connection.prepareStatement("DELETE from EMPLOYEE where ID=? ");
                 prepStmt.setInt(1, 7); //substitute first occurrence of ? with 7
                
                 prepStmt.executeUpdate(); // execute delete query
                 System.out.println("deleted");
                
                 connection.rollback(savepoint1); //Rollback to savepoint1
                 System.out.println("Rolled back to savepoint1");
                
                
                 //SAVEPOINT 2
                 Savepoint savepoint2 = connection.setSavepoint();
                 System.out.println("\nSavepoint2 created");
                
                 prepStmt.setInt(1, 8); //substitute first occurrence of ? with 8
                
                 prepStmt.executeUpdate(); // execute delete query
                 System.out.println("deleted");
                
                 connection.rollback(savepoint2); //Rollback to savepoint2
                 System.out.println("Rolled back to savepoint2");
                
                 connection.commit(); //commit the transaction
                
          } catch (ClassNotFoundException e) {
                 e.printStackTrace();
          } catch (SQLException e) {
                 e.printStackTrace();
          }
          finally{
                 try {
                       if(prepStmt!=null) prepStmt.close(); //close PreparedStatement
                       if(connection!=null) connection.close(); // close connection
                 } catch (SQLException e) {
                       e.printStackTrace();
                 }
          }
   }
}
/*OUTPUT
Connection established successfully!
Savepoint1 created
deleted
Rolled back to savepoint1
Savepoint2 created
deleted
Rolled back to savepoint2
*/


After execution of above program EMPLOYEE table will look like this >
Because both transactions were rolled back after execution of delete query.





Must read : java.sql.PreparedStatement - using executeUpdate  and executeQuery methods - CREATE, SELECT, INSERT and UPDATE


Must Know :

By default connection is auto-commit, so all delete queries will be committed automatically.
(Also must know : delete is a DML(Data Manipulation Language) command, queries are not committed automatically in database)

We may stop connection from doing auto-commits by setting auto-commit to false using > con.setAutoCommit(false);
Well in that we may commit transactions any time using con.commit();

Also, We may set connection back to default behavior of doing auto-commit by using > con.setAutoCommit(true) or simply using con.setAutoCommit();

But what will be benefit of using con.setAutoCommit(false)?
If any transaction goes wrong than we may halt execution further related transactions before calling con.commit() and call con.rollback() in catch block.


So, in this tutorial we will learned how to Create Savepoint in transaction, how to rollback transaction to specific savepoint in transaction in java JDBC and learned about deleting or removing savepoint from current transaction in java jdbc.

RELATED LINKS>

java.sql.PreparedStatement - using executeUpdate  and executeQuery methods - CREATE, SELECT, INSERT, UPDATE and DELETE



Execute database STORED PROCEDURE - IN parameter, OUT parameter and IN OUT parameter || call FUNCTION from java

Difference between CLOB and CLOB data type in Oracle


JDBC- Insert/Store/save FILE in database


JDBC- Batch PreparedStatement example- Execute INSERT query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java

No comments:

Post a Comment