How to Perform rollback operation in JDBC

java.sql.Connection interface provides rollback method to perform rollback operation.

rollback method available in two variants.

void rollback() throws SQLException

Undoes all changes made in the current transaction and releases any database locks currently held by this Connection object.

void rollback(Savepoint savepoint) throws SQLException

Undoes all changes made after the given Savepoint object was set.

To perform rollback operations follow below steps.

Step 1: First disable autocommit mode, otherwise every sql statement get committed after execution.

Step 2: call rollback() where ever you want to perform roll back operation.

/* Step 1: Import sql package */
import java.sql.*;

public class SampleApp {
    
    /* Update username, password and driver details here */
    static Connection getConnection() throws ClassNotFoundException, SQLException{
         /* Step 2: Load Driver */
        System.out.println("Loading/Registering driver");  
       
        Class.forName("com.mysql.jdbc.Driver");
         
        /* Step 3: Open connection to database */
        System.out.println("Connecting to database");
        String url = "jdbc:mysql://localhost/world";
        String userName = "root";
        String pasword = "tiger";
        return DriverManager.getConnection(url, userName, pasword);
    }
    
    public static void main(String args[]) throws SQLException, ClassNotFoundException{
        
        Connection conn = getConnection();
        conn.setAutoCommit(false);
          /* Create table employee */
        String query = "CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))";
        Statement stmt = conn.createStatement();
        stmt.execute(query);     
        
        /* Insert data to employee table */
        query = "INSERT INTO employee values(1, \"Krishna\")";
        stmt.execute(query);
        conn.commit();
        
        query = "INSERT INTO employee values(2, \"Arjun\")";
        stmt.execute(query);
        
        System.out.println("Calling roll back operation");
        
        conn.rollback();
        
        query = "SELECT * FROM employee";
        ResultSet rs = stmt.executeQuery(query);
        
        while(rs.next()){
            int id  = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println(id +" " + name);
        }
        System.out.println("Dropping table employee");
        query = "DROP TABLE employee";
        stmt.execute(query);
        
 rs.close();
        stmt.close();
        conn.close();
    }
}

Output

Loading/Registering driver
Connecting to database
Calling roll back operation
1 Krishna
Dropping table employee

As you observe the above program, auto commit mode is disabled .After inserting first record (1, Krishna), application called commit operation. But commit operation is not called after inserting second record, So when the application calls roll back operation, the data restored to previously committed state, and restores record 1 only.

 

Prevoius                                                 Next                                                 Home