JDBC Transactions - commit and rollback(TCL command) - using PreparedStatement's executeUpdate method, setting connection.setAutoCommit(false) in java


In this tutorial we will learn how to commit and rollback(TCL command) - using PreparedStatement's executeUpdate method, how to set autocommit of connection to false, rollback unsuccessful transaction and committing the successful transaction in java jdbc.


Must know : Commit and rollback are Transaction Control (TCL) statements.


--Before executing java program execute these database scripts  >
create table EMPLOYEE(id number(4), name varchar2(22), salary number(5));
insert into EMPLOYEE values(7, 'ankit', 1000);
insert into EMPLOYEE values(8, 'rohit', 1000);
commit;


--If table already exists then execute the DROP command >
drop table EMPLOYEE;


Example/ Programs JDBC Transactions - commit or rollback(TCL command) - using PreparedStatement's executeUpdate method, setting connection.setAutoCommit(false) in java
Let's we have to update salary of two employees, and salary of both employees must be updated simultaneously in database.

Let's say salary of first employee is updated successfully. But, if anything goes wrong in updating salary of second employee than any updation done to first employee's salary will be rolled back.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class PreparedStatementCommitRollbackExample {
public static void main(String... arg) {
     Connection con =null;
     PreparedStatement prepStmt = null;
     try {
          // registering Oracle driver class
          Class.forName("oracle.jdbc.driver.OracleDriver");
          // getting connection
          con = DriverManager.getConnection(
                   "jdbc:oracle:thin:@localhost:1521:orcl",
                   "ankit", "Oracle123");
          System.out.println("Connection established successfully!");
         
          con.setAutoCommit(false); //Now, transactions won't be committed automatically.
         
          prepStmt = con.prepareStatement("UPDATE EMPLOYEE set salary=? where id=? ");
          prepStmt.setInt(1, 14000); //substitute first occurrence of ? with 14000
          prepStmt.setInt(2, 7); //substitute second occurrence of ? with 7
          //Execute first update statement - update salary of employee with id=1
          prepStmt.setInt(1, 14000); //substitute first occurrence of ? with 14000
          prepStmt.setInt(2, 7); //substitute second occurrence of ? with 7
          prepStmt.executeUpdate();
         
          //Let's say first update statement was executed successfully.
         
          //Execute second update statement - update salary of employee with id=2
          prepStmt.setInt(1, 15000); //substitute first occurrence of ? with 15000
          prepStmt.setInt(2, 8); //substitute second occurrence of ? with 8
          prepStmt.executeUpdate();
         
          /*
              IF ANYTHING GOES WRONG IN EXECUTING SECOND UPDATE, control will go to
              catch block, rollback() method will be called and any changes
                   done by first update statement will be rolled back.
          */        
          System.out.println("Salary of both employees was updated successfully");
         
          con.commit();
     } catch (Exception e) {
          e.printStackTrace();
          try {
              con.rollback(); //In case of Exception, rollback all the transactions
              System.out.println("Salary of employees wasn't updated, rollback done");
          } catch (SQLException e1) {
              e1.printStackTrace();
          }
     }
     finally{
          try {
              if(prepStmt!=null) prepStmt.close(); //close PreparedStatement
              if(con!=null) con.close(); // close connection
          } catch (SQLException e) {
              e.printStackTrace();
          }
     }
}
}
/*OUTPUT
Connection established successfully!
Salary of both employees was updated successfully
*/


So in this tutorial we learned how to commit and rollback(TCL command) - using PreparedStatement's executeUpdate method, how to set autocommit of connection to false, rollback unsuccessful transaction and committing the successful transaction in java jdbc.

RELATED LINKS>

java.sql.PreparedStatement tutorial - using executeUpdate  and executeQuery methods - CREATE, SELECT, INSERT, UPDATE and DELETE in java jdbc.

Oracle 11g and SQL Developer installation and setup on 32/64bit windows - explained step by step with screenshots


No comments:

Post a Comment