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


In this tutorial we will learn how to commit and rollback(TCL command) - using Statement'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 Statement'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.SQLException;
import java.sql.Statement;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class StatementCommitRollbackExample {
public static void main(String... arg) {
     Connection con =null;
     Statement stmt = 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.
         
          stmt = con.createStatement();
          //Execute first update statement - update salary of employee with id=1
          stmt.executeUpdate("UPDATE EMPLOYEE set salary=14000 where id=1 ");
         
          //Let's say first update statement was executed successfully.
         
          //Execute second update statement - update salary of employee with id=2
          stmt.executeUpdate("UPDATE EMPLOYEE set salary=15000 where id=2 ");
          /*
              IF ANY THING 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(); //commit all the transactions
     } 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(stmt!=null) stmt.close(); //close Statement
              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 Statement's executeUpdate method, how to set autocommit of connection to false, rollback unsuccessful transaction and committing the successful transaction in java jdbc.


RELATED LINKS>

Execute database STORED PROCEDURE - IN parameter, OUT parameter and IN OUT parameter || call FUNCTION from java jdbc.

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

Must read for you :
Labels:

No comments:

Post a Comment