Java & MySQL - Commit & Rollback

Once you are done with your changes and you want to commit the changes then call commit() method on connection object as follows −

conn.commit( );

Otherwise, to roll back updates to the database made using the Connection named conn, use the following code −

conn.rollback( );

The following example illustrates the use of a commit and rollback object −

try{
   //Assume a vapd connection object conn
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   
   String SQL = "INSERT INTO Employees  " +
                "VALUES (106, 20,  Rita ,  Tez )";
   stmt.executeUpdate(SQL);  
   //Submit a malformed SQL statement that breaks
   String SQL = "INSERTED IN Employees  " +
                "VALUES (107, 22,  Sita ,  Singh )";
   stmt.executeUpdate(SQL);
   // If there is no error.
   conn.commit();
}catch(SQLException se){
   // If there is any error.
   conn.rollback();
}

In this case, none of the above INSERT statement would success and everything would be rolled back.

Following is the example, which makes use of commit and rollback described.

This sample code has been written based on the environment and database setup done in the previous chapters.

Copy and paste the following example in TestApppcation.java, compile and run as follows −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

pubpc class TestApppcation {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Employees";
   static final String INSERT_QUERY = "INSERT INTO Employees (first, last, age) values( Rita ,  Tez , 20)";
   static final String INSERT_QUERY_2 = "INSERT INTO Employees (first, last, age) values( Sita ,  Singh , 20)";

   pubpc static void printResultSet(ResultSet rs) throws SQLException{
      // Ensure we start with first row
      rs.beforeFirst();
      while(rs.next()){
         // Display values
         System.out.print("ID: " + rs.getInt("id"));
         System.out.print(", Age: " + rs.getInt("age"));
         System.out.print(", First: " + rs.getString("first"));
         System.out.println(", Last: " + rs.getString("last"));
      }
      System.out.println();
   }

   pubpc static void main(String[] args) {

      Connection conn = null;
      Statement stmt = null;
      try{
         // Open a connection
         System.out.println("Connecting to database...");
         conn = DriverManager.getConnection(DB_URL,USER,PASS);

         // Set auto commit as false.
         conn.setAutoCommit(false);

         // Execute a query to create statment with
         // required arguments for RS example.
         System.out.println("Creating statement...");
         stmt = conn.createStatement(
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_UPDATABLE);

         // INSERT a row into Employees table
         System.out.println("Inserting one row....");
         stmt.executeUpdate(INSERT_QUERY);  

         // INSERT one more row into Employees table
         stmt.executeUpdate(INSERT_QUERY_2);

         // Commit data here.
         System.out.println("Commiting data here....");
         conn.commit();

         // Now pst all the available records.
         String sql = "SELECT id, first, last, age FROM Employees";
         ResultSet rs = stmt.executeQuery(sql);
         System.out.println("List result set for reference....");
         printResultSet(rs);

         // Clean-up environment
         rs.close();
         stmt.close();
         conn.close();
      }catch(SQLException se){
         se.printStackTrace();
         // If there is an error then rollback the changes.
         System.out.println("Rolpng back data here....");
         try{
            if(conn!=null)
               conn.rollback();
            }catch(SQLException se2){
               se2.printStackTrace();
            }
         }catch(Exception e){
            e.printStackTrace();
         }finally{
            // finally block used to close resources
         try{
            if(stmt!=null)
               stmt.close();
         }catch(SQLException se2){
            se2.printStackTrace();
         } 
            try{
               if(conn!=null)
                  conn.close();
            }catch(SQLException se){
               se.printStackTrace();
         }
      }		   
   }
}

Now let us compile the above example as follows −

C:>javac TestApppcation.java
C:>

When you run TestApppcation, it produces the following result −

C:>java TestApppcation
Connecting to database...
Creating statement...
Inserting one row....
Commiting data here....
List result set for reference....
ID: 1, Age: 23, First: Zara, Last: Ap
ID: 2, Age: 30, First: Mahnaz, Last: Fatma
ID: 3, Age: 35, First: Zaid, Last: Khan
ID: 4, Age: 33, First: Sumit, Last: Mittal
ID: 5, Age: 40, First: John, Last: Paul
ID: 6, Age: 20, First: Rita, Last: Tez
ID: 7, Age: 20, First: Sita, Last: Singh
C:>