Tutorialspoint

1 Answer
Vikyath Ram

The commit() method of the Connection interface saves all the modifications made since the last commit.

con.save()

If any issue occurs after the commit you can revert all the changes done till this commit by invoking the rollback() method.

Con.rollback()

To commit 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);

Commit the transaction using the commit() method as −

con.commit();

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 commit() 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 last commit 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 commit, 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
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.