JDBC - Commit and RollBack Examples

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

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

Using Commit Statement to Persist Inserted Rows in Table Example

In this example, we've six static strings containing a dababase connection url, username, password, SELECT Query and two INSERT queries. Insert Queries are used to insert two records in the Employees table and Select query will be used to get all the employees. Now using DriverManager.getConnection() method, we've prepared a database connection. Using setAutoCommit(false), we've set the auto commit as false which is by default true. Once connection is prepared, we've created a Statement object using connection.createStatement() method. While creating Statement Object, we've used ResultSet types as TYPE_SCROLL_INSENSITIVE and CONCUR_READ_ONLY. Then using statement.executeUpdate(), the INSERT Queries are executed. Now using commit() method, the changes are persisted to the database.

We've defined a method printResultSet() which takes the resultset as argument, iterates it and print all the records of the resultset. Once all changes are commited, printResultSet() method is called to print all the records to verify the inserted records being available or not.

Copy and paste the following example in JDBCCommitTransactionExample.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;

public class JDBCCommitTransactionExample {
   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)";

   public 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();
   }

   public 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
         System.out.println("Inserting another row....");
         stmt.executeUpdate(INSERT_QUERY_2);

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

         // Now list all the available records.
         ResultSet rs = stmt.executeQuery(QUERY);
         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("Rolling 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();
         }
      }		   
   }
}

Output

Now let us compile the above example as follows −

C:\>javac JDBCCommitTransactionExample.java
C:\>

When you run JDBCCommitTransactionExample, it produces the following result −

C:\>java JDBCCommitTransactionExample
Connecting to database...
Creating statement...
Inserting one row....
Inserting another row....
Commiting data here....
List result set for reference....
ID: 1, Age: 23, First: Zara, Last: Ali
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:\>

Using Rollback Statement to Revert Inserted Rows in Table Example

In this example, we've six static strings containing a dababase connection url, username, password, SELECT Query and two INSERT queries. Insert Queries are used to insert two records in the Employees table and Select query will be used to get all the employees. One of the insert query is invalid as Employee1 table does not exist. Now using DriverManager.getConnection() method, we've prepared a database connection. Using setAutoCommit(false), we've set the auto commit as false which is by default true. Once connection is prepared, we've created a Statement object using connection.createStatement() method. While creating Statement Object, we've used ResultSet types as TYPE_SCROLL_INSENSITIVE and CONCUR_READ_ONLY. Then using statement.executeUpdate(), the INSERT Queries are executed.

As second INSERT query is invalid, SQLException will be raised. We've handled the exception and using rollback() method, the changes are reverted.

Copy and paste the following example in JDBCRollbackTransactionExample.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;

public class JDBCRollbackTransactionExample {
   static final String MYSQL_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER_NAME = "guest";
   static final String PASSWORD = "guest123";
   static final String INSERT_QUERY1 = "INSERT INTO Employees (first, last, age) values('Jeevan', 'Rao', 35)";
   static final String INSERT_QUERY2 = "INSERT INTO Employees11 (first, last, age) values('Aditya', 'Chaube', 40)";
   static final String SELECT_QUERY = "SELECT id, age, first, last FROM Employees";

   public 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();
   }

   public static void main(String[] args) {
      Connection conn = null;
      Statement stmt = null;
      ResultSet rs = null;
      try{
         // Open database connection
         conn = DriverManager.getConnection(MYSQL_URL,USER_NAME,PASSWORD);
         System.out.println(" Connection established with TUTORIALSPOINT database.");

         conn.setAutoCommit(false);
         stmt = conn.createStatement(
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_UPDATABLE);

         System.out.println("Before inserting a row into Employees table");
         stmt.executeUpdate(INSERT_QUERY1);  
         System.out.println("Inserted a row into Employees table");
         stmt.executeUpdate(INSERT_QUERY2);

         System.out.println("Before committing 2 inserts.");
         conn.commit();
         System.out.println(" After committing 2 inserts.");

         rs = stmt.executeQuery(SELECT_QUERY);
         System.out.println("List ALL records of Employees table..");

         printResultSet(rs);

         // Clean-up resources
         rs.close();
         stmt.close();
         conn.close();
      }catch(SQLException se){
         se.printStackTrace();
         System.out.println("If error has occured, discard changes");
         try{
            if(conn!=null)
               conn.rollback();
               //Checking if records are in 'before-insert' state. 
               rs = stmt.executeQuery(SELECT_QUERY);
               System.out.println("List ALL records of Employees table..");
               printResultSet(rs);
               // Clean-up resources
               rs.close();
               stmt.close();
               conn.close();
         }catch(SQLException se){      
            se.printStackTrace();
            System.out.println("If error has occured, discard changes");
            try{
               if(conn!=null)
                  conn.rollback();

               // Checking if records are in 'before-insert' state. 
               rs = stmt.executeQuery(SELECT_QUERY);
               System.out.println("List ALL records of Employees table..");

               printResultSet(rs);
            }
            catch(SQLException se2){
               se2.printStackTrace();
            }catch(Exception e){
               e.printStackTrace();
            }finally{
               try{
                  if(rs != null){
                     rs.close();
                  }
               }catch(SQLException se1){
                  se1.printStackTrace();
               }
               try{
                  if(stmt!=null)
                     stmt.close();
               }catch(SQLException se2){
                  se2.printStackTrace();
               } 
               try{
                  if(conn!=null)
                     conn.close();
               }catch(SQLException se){
                  se.printStackTrace();
               }
            }		   
         }
      }
   }
}

Output

Now let us compile the above example as follows −

C:\>javac JDBCRollbackTransactionExample.java
C:\>

When you run JDBCRollbackTransactionExample, it produces the following result −

C:\>java JDBCRollbackTransactionExample
Connection established with TUTORIALSPOINT database.
Before inserting a row into Employees table
Inserted a row into Employees table
java.sql.SQLSyntaxErrorException: Table 'tutorialspoint.employees11' doesn't exist
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:112)
...	
If error has occured, discard changes
List ALL records of Employees table..
ID: 1, Age: 18, First: Zara, Last: Ali
ID: 2, Age: 25, First: Mahnaz, Last: Fatma
ID: 3, Age: 30, First: Zaid, Last: Khan
ID: 4, Age: 28, First: Sumit, Last: Mittal
ID: 7, Age: 20, First: Rita, Last: Tez
ID: 8, Age: 20, First: Sita, Last: Singh

C:\>

jdbc-transactions.htm

Print Page