How to set savepoint

java.sql.Connection interface provides 'setSavepoint' method to set the savepoint.

'setSavepoint' method available in two variants.

Savepoint setSavepoint() throws SQLException

Creates an unnamed savepoint in the current transaction and returns the new Savepoint object that represents it.

Savepoint setSavepoint(String name) throws SQLException

Creates a savepoint with the given name in the current transaction and returns the new Savepoint object that represents it.

/* 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);
        
        Savepoint s1 = conn.setSavepoint();
        
        query = "INSERT INTO employee values(2, \"Arjun\")";
        stmt.execute(query);
        
        System.out.println("Calling roll back operation");
        
        conn.rollback(s1);
        
        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

Savepoint s1 = conn.setSavepoint();

Above statement creates a savepoint.

conn.rollback(s1);

Undoes all changes made after the Savepoint 's1' object was set.

Prevoius                                                 Next                                                 Home