Tutorialspoint

1 Answer
Vikyath Ram

A save point is a logical rollback point within a transaction. When you set a save point, whenever an error occurs past a save point, you can undo the events you have done up to the save point using the rollback() method.

You can set a save point in a database using the setSavepoint(String savepointName) method of the Connection interface.

//Setting the save point
Savepoint savePoint = con.setSavepoint("MysavePoint");

This method accepts a string value representing the name of the save point and, returns a save point object.

To set a save point −

Register the driver using the registerDriver() method of the DriverManager class as −

//Registering the Driver
DriverManager.registerDriver(new com.mysql.jdbc.Driver());

Get the connection using the getConnection() method of the DriverManager class as −

//Getting the connection
String url = "jdbc:mysql://localhost/mydatabase";
Connection con = DriverManager.getConnection(url, "root", "password");

Turn off the auto-commit using the setAutoCommit() method as −

//Setting the auto commit false
con.setAutoCommit(false);

Set the save point using the setSavepoint() method of the Connection interface as −

Savepoint savePoint = con.setSavepoint();

You can also pass the name of the save point as a String variable to this method as −

Savepoint savePoint = con.setSavepoint("MysavePoint");

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below −

CREATE TABLE MyPlayers(
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements −

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following JDBC program demonstrates the setSavepoint() method of the Connection interface. Here, we inserted a new record in the Myplayers table. Set the Savepoint, deleted the previously inserted record and rolled back to the created save point and displayed the contents of the table

Example

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
public class Connection_setSavepoint {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String url = "jdbc:mysql://localhost/mydatabase";
      Connection con = DriverManager.getConnection(url, "root", "password");
      System.out.println("Connection established......");
      //Setting the auto commit false
      con.setAutoCommit(false);
      //Creating a Statement object
      Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select * from MyPlayers");
      System.out.println("Contents of the table initially");
      while(rs.next()) {
         System.out.print("ID: "+rs.getString("ID")+", ");
         System.out.print("First_Name: "+rs.getString("First_Name")+", ");
         System.out.print("Last_Name: "+rs.getString("Last_Name")+", ");
         System.out.print("Date_Of_Birth: "+rs.getString("Date_Of_Birth")+", ");
         System.out.print("Place_Of_Birth: "+rs.getString("Place_Of_Birth")+", ");
         System.out.print("Country: "+rs.getString("Country"));
         System.out.println("");
      }
      PreparedStatement pstmt = con.prepareStatement("INSERT INTO MyPlayers VALUES (?, ?, ?, ?, ?, ?)");
      pstmt.setInt(1, 8);
      pstmt.setString(2, "Ishant");
      pstmt.setString(3, "Sharma");
      pstmt.setDate(4, new Date(904694400000L));
      pstmt.setString(5, "Delhi");
      pstmt.setString(6, "India");
      pstmt.executeUpdate();
      //Setting the save point
      Savepoint savePoint = con.setSavepoint("MysavePoint");
      //Deleting the record
      stmt.execute("Delete from MyPlayers where id = 8");
      //Rolling back to the save point
      con.rollback(savePoint);
      //Contents of the table after the roll-back
      System.out.println("Contents of the table");
      rs = stmt.executeQuery("select * from MyPlayers");
      while(rs.next()) {
         System.out.print("ID: "+rs.getString("ID")+", ");
         System.out.print("First_Name: "+rs.getString("First_Name")+", ");
         System.out.print("Last_Name: "+rs.getString("Last_Name")+", ");
         System.out.print("Date_Of_Birth: "+rs.getString("Date_Of_Birth")+", ");
         System.out.print("Place_Of_Birth: "+rs.getString("Place_Of_Birth")+", ");
         System.out.print("Country: "+rs.getString("Country"));
         System.out.println("");
      }
   }
}

Now, you can observe the new record created before the save point in the result though we have deleted it.

All the changes done past the save point will be reverted if we rollback to a particular save point.

Since we have deleted the 8th record after setting the save point, this delete is reverted at the time of roll back.

Output

Connection established......
Contents of the table initially
ID: 1, First_Name: Shikhar, Last_Name: Dhawan, Date_Of_Birth: 1981-12-05, Place_Of_Birth: Delhi, Country: India
ID: 2, First_Name: Jonathan, Last_Name: Trott, Date_Of_Birth: 1981-04-22, Place_Of_Birth: CapeTown, Country: SouthAfrica
ID: 3, First_Name: Kumara, Last_Name: Sangakkara, Date_Of_Birth: 1977-10-27, Place_Of_Birth: Matale, Country: Srilanka
ID: 4, First_Name: Virat, Last_Name: Kohli, Date_Of_Birth: 1988-11-05, Place_Of_Birth: Mumbai, Country: India
ID: 5, First_Name: Rohit, Last_Name: Sharma, Date_Of_Birth: 1987-04-30, Place_Of_Birth: Nagpur, Country: India
ID: 6, First_Name: Ravindra, Last_Name: Jadeja, Date_Of_Birth: 1988-12-06, Place_Of_Birth: Nagpur, Country: India
ID: 7, First_Name: James, Last_Name: Anderson, Date_Of_Birth: 1982-06-30, Place_Of_Birth: Burnley , Country: England
Contents of the table after rollback
ID: 1, First_Name: Shikhar, Last_Name: Dhawan, Date_Of_Birth: 1981-12-05, Place_Of_Birth: Delhi, Country: India
ID: 2, First_Name: Jonathan, Last_Name: Trott, Date_Of_Birth: 1981-04-22, Place_Of_Birth: CapeTown, Country: SouthAfrica
ID: 3, First_Name: Kumara, Last_Name: Sangakkara, Date_Of_Birth: 1977-10-27, Place_Of_Birth: Matale, Country: Srilanka
ID: 4, First_Name: Virat, Last_Name: Kohli, Date_Of_Birth: 1988-11-05, Place_Of_Birth: Mumbai, Country: India
ID: 5, First_Name: Rohit, Last_Name: Sharma, Date_Of_Birth: 1987-04-30, Place_Of_Birth: Nagpur, Country: India
ID: 6, First_Name: Ravindra, Last_Name: Jadeja, Date_Of_Birth: 1988-12-06, Place_Of_Birth: Nagpur, Country: India
ID: 7, First_Name: James, Last_Name: Anderson, Date_Of_Birth: 1982-06-30, Place_Of_Birth: Burnley , Country: England
ID: 8, First_Name: Ishant, Last_Name: Sharma, Date_Of_Birth: 1998-09-02, Place_Of_Birth: Delhi, Country: India

Advertisements

We use cookies to provide and improve our services. By using our site, you consent to our Cookies Policy.