How to create and release a save point in JDBC?

JDBCJava 8Object Oriented ProgrammingProgramming

When you set a save point you define a logical rollback point within a transaction. If an error occurs past a save point, you can use the rollback method to undo either all the changes or only the changes made after the save point.

Savepoint interface gives you the additional transactional control. Most modern DBMS, support save points within their environments such as Oracle's PL/SQL.

Setting a save point

You can set a save point in a database using the setSavepoint(String savepointName) method of the Connection interface, this method accepts a string value representing the name of the save point and returns a Savepoint object.

Releasing a save point

You can release a save point using the releaseSavepoint(Savepoint savepointName) method of the Connection interface, this method accepts the name of the savepoint and releases/deletes the specified save point.

Example

Assume we have a table named customers in the database with 5 records as shown below:

+----+-----------+------+---------+----------------+
| ID | NAME      | AGE  | SALARY | ADDRESS         |
+----+-----------+------+---------+----------------+
| 1  | Amit      | 25   | 3000.00 | Hyderabad      |
| 2  | Kalyan    | 27   | 4000.00 | Vishakhapatnam |
| 3  | Renuka    | 30   | 5000.00 | Delhi          |
| 4  | Archana   | 24   | 1500.00 | Mumbai         |
| 5  | Koushik   | 30   | 9000.00 | Kota           |
+----+-----------+------+---------+----------------+

Following JDBC program Inserts 7 more records to it, sets a save point, removes some records and roll backs to the save point.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Savepoint;
public class SavepointExample {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/mydatabase";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Setting auto-commit false
      con.setAutoCommit(false);
      System.out.println(" ");
      //Creating the Statement
      PreparedStatement pstmt = con.prepareStatement("INSERT into customers VALUES (?, ?, ?, ?, ?) ");
      pstmt.setInt(1, 6);
      pstmt.setString(2, "Hardik");
      pstmt.setInt(3, 45);
      pstmt.setInt(4, 6400);
      pstmt.setString(5, "Bhopal");
      pstmt.executeUpdate();

      pstmt.setInt(1, 7);
      pstmt.setString(2, "Trupti");
      pstmt.setInt(3, 33);
      pstmt.setInt(4, 4360);
      pstmt.setString(5, "Ahmedabad");
      pstmt.executeUpdate();

      pstmt.setInt(1, 8);
      pstmt.setString(2, "Mithili");
      pstmt.setInt(3, 26);
      pstmt.setInt(4, 4100);
      pstmt.setString(5, "Vijayawada");
      pstmt.executeUpdate();

      pstmt.setInt(1, 9);
      pstmt.setString(2, "Maneesh");
      pstmt.setInt(3, 39);
      pstmt.setInt(4, 4000);
      pstmt.setString(5, "Hyderabad");
      pstmt.executeUpdate();

      pstmt.setInt(1, 10);
      pstmt.setString(2, "Rajaneesh");
      pstmt.setInt(3, 30);
      pstmt.setInt(4, 6400);
      pstmt.setString(5, "Delhi");
      pstmt.executeUpdate();

      pstmt.setInt(1, 11);
      pstmt.setString(2, "Komal");
      pstmt.setInt(3, 29);
      pstmt.setInt(4, 8000);
      pstmt.setString(5, "Ahmedabad");
      pstmt.executeUpdate();

      pstmt.setInt(1, 12);
      pstmt.setString(2, "Manyata");
      pstmt.setInt(3, 25);
      pstmt.setInt(4, 5000);
      pstmt.setString(5, "Vijayawada");
      pstmt.executeUpdate();

      //Setting save point
      Savepoint savePoint = con.setSavepoint("mysavepoint");
      System.out.println(" ");

      System.out.println("Contents of the customers table after inserting the records: ");
      Statement stmt = con.createStatement();

      //Retrieving data
      ResultSet rs = stmt.executeQuery("Select * from customers");
      while(rs.next()) {
         System.out.print("ID: "+rs.getInt("ID")+", ");
         System.out.print("Name: "+rs.getString("Name")+", ");
         System.out.print("Age: "+rs.getInt("Age")+", ");
         System.out.print("Salary: "+rs.getInt("Salary")+", ");
         System.out.print("Address: "+rs.getString("Address"));
         System.out.println();
      }
      //Deleting the records
      stmt.execute("Delete from customers where id > 5");

      System.out.println(" ");
      System.out.println("Contents of the customers table after deleting the records: ");

      //Retrieving data
      rs = stmt.executeQuery("Select * from customers");
      while(rs.next()) {
         System.out.print("ID: "+rs.getInt("ID")+", ");
         System.out.print("Name: "+rs.getString("Name")+", ");
         System.out.print("Age: "+rs.getInt("Age")+", ");
         System.out.print("Salary: "+rs.getInt("Salary")+", ");
         System.out.print("Address: "+rs.getString("Address"));
         System.out.println();
      }

      //Rolling back to the save point
      con.rollback(savePoint);

      System.out.println(" ");
      System.out.println("Contents of the table at the save point: ");

      //Retrieving data
      rs = stmt.executeQuery("Select * from customers");
      while(rs.next()) {
         System.out.print("ID: "+rs.getInt("ID")+", ");
         System.out.print("Name: "+rs.getString("Name")+", ");
         System.out.print("Age: "+rs.getInt("Age")+", ");
         System.out.print("Salary: "+rs.getInt("Salary")+", ");
         System.out.print("Address: "+rs.getString("Address"));
         System.out.println();
      }
   }
}

Output

Connection established......

Contents of the customers table after inserting the records:
ID: 1, Name: Amit, Age: 25, Salary: 3000, Address: Hyderabad
ID: 2, Name: Kalyan, Age: 27, Salary: 4000, Address: Vishakhapatnam
ID: 3, Name: Renuka, Age: 30, Salary: 5000, Address: Delhi
ID: 4, Name: Archana, Age: 24, Salary: 1500, Address: Mumbai
ID: 5, Name: Koushik, Age: 30, Salary: 9000, Address: Kota
ID: 6, Name: Hardik, Age: 45, Salary: 6400, Address: Bhopal
ID: 7, Name: Trupti, Age: 33, Salary: 4360, Address: Ahmedabad
ID: 8, Name: Mithili, Age: 26, Salary: 4100, Address: Vijayawada
ID: 9, Name: Maneesh, Age: 39, Salary: 4000, Address: Hyderabad
ID: 10, Name: Rajaneesh, Age: 30, Salary: 6400, Address: Delhi
ID: 11, Name: Komal, Age: 29, Salary: 8000, Address: Ahmedabad
ID: 12, Name: Manyata, Age: 25, Salary: 5000, Address: Vijayawada

Contents of the customers table after deleting the records:
ID: 1, Name: Amit, Age: 25, Salary: 3000, Address: Hyderabad
ID: 2, Name: Kalyan, Age: 27, Salary: 4000, Address: Vishakhapatnam
ID: 3, Name: Renuka, Age: 30, Salary: 5000, Address: Delhi
ID: 4, Name: Archana, Age: 24, Salary: 1500, Address: Mumbai
ID: 5, Name: Koushik, Age: 30, Salary: 9000, Address: Kota

Contents of the table at the save point:
ID: 1, Name: Amit, Age: 25, Salary: 3000, Address: Hyderabad
ID: 2, Name: Kalyan, Age: 27, Salary: 4000, Address: Vishakhapatnam
ID: 3, Name: Renuka, Age: 30, Salary: 5000, Address: Delhi
ID: 4, Name: Archana, Age: 24, Salary: 1500, Address: Mumbai
ID: 5, Name: Koushik, Age: 30, Salary: 9000, Address: Kota
ID: 6, Name: Hardik, Age: 45, Salary: 6400, Address: Bhopal
ID: 7, Name: Trupti, Age: 33, Salary: 4360, Address: Ahmedabad
ID: 8, Name: Mithili, Age: 26, Salary: 4100, Address: Vijayawada
ID: 9, Name: Maneesh, Age: 39, Salary: 4000, Address: Hyderabad
ID: 10, Name: Rajaneesh, Age: 30, Salary: 6400, Address: Delhi
ID: 11, Name: Komal, Age: 29, Salary: 8000, Address: Ahmedabad
ID: 12, Name: Manyata, Age: 25, Salary: 5000, Address: Vijayawada
raja
Published on 02-Apr-2019 12:06:43
Advertisements