Java Connection Rollback Method with Example

A rollback operation undoes all the changes done by the current transaction i.e. If you call a rollBack() method of the Connection interface, all the modifications are reverted until the last commit.Con.rollback()

You can also rollback the changes in the database up to a particular save point by passing the required Savepoint object as a parameter to this method as −

//Setting the save point
con.rollback("MysavePoint");

To roll back a transaction

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() or, commit the transaction using the commit() method as −

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

Finally, rollback to the last save point or, last commit using the rollback() or, rollback(Savepoint savepoint) methods.

con.rollback()
Or,
con. rollback(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 rollback() method of the Connection interface. Here, we inserted a new record in the Myplayers table. Completed the transaction using the commit() method, 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.Statement;
public class Connection_rollBack {
   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();
      //Committing the transaction
      con.commit();
      //Deleting the record
      stmt.execute("Delete from MyPlayers where id = 8");
      //Rolling back to the save point
      con.rollback();
      //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 last commit will be reverted if we rollback a transaction.

Since we have deleted the 8th record after setting the commit, 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