JDBC Transaction example

"A transaction is a logical group of work that contains one or more SQL statements. Either all, or none of the statements need to be performed in order to preserve data integrity"

A complete task which is a combination of the multiple smaller tasks.
For a major task to be completed, smaller tasks need to be successfully completed.
If any one task fails then all the previous tasks are reverted back to the original state, means that database server guarantee to follow the ACID (Automicity, Consistency, Isolation and Durability) properties. A transaction is an atomic unit.

ACID Properties -

  • Atomicity :
    • Implies indivisibility
    • Any indivisible operation (one which will either complete in totally, or not at all) is said to be atomic
  • Consistency :
    • A transaction must transition persistent data from one consistent state to another
    • In the event of a failure occurs during processing, data must be restored to the state it was in prior to the transaction
  • Isolation :
    • Transactions should not affect each other
    • A transaction in progress, not yet committed or rolled back, must be isolated from other transactions
  • Durability :
    • Once a transaction commits successfully, the state changes committed by that transaction must be durable < persistent, despite any failures that occur afterwards

JDBC Transaction Management :

The Connection interface is used to manage transactions in Java applications. There is three methods setAutoCommit(), commit() and rollback() used to implement transaction in JDBC. By default true is set in setAutoCommit() method, when any SQL statement submit to the database server, database server commit it. We can set false in setAutoCommit() method to commit or rollback transaction either commit or rollback explicitly respectively by calling commit(0 and rollback() method.

Example - I am using Account table. The amount must be available at least Rs.- 1000.00. After withdrawing amount from account, it will check the amount in account, If it is less than 1000 the transaction must be rollback.

Establish connection with database     [ Type1 driver ]         [ Type4 driver ]

Create table in database server -

use test;
drop table if exists Account;
create table Account
(
accId varchar(10) primary key,
accName varchar(40),
accType varchar(10),
amount double
);
insert into Account values('a001','Ram kumar','Saving',5000.50);
insert into Account values('a002','Shayam kumar','Saving',5600.50);
insert into Account values('a003','MOhan kumar','Saving',4060.50);
insert into Account values('a004','Ravi Singh','Saving',9000.50);
insert into Account values('a005','Suraj Suman','Saving',85000.50);
Download

Following source code to demostrates the JDBC transaction -

import java.sql.*;
import java.io.*;
public class JDBCTransactionDemo1 
{
Connection con;
PreparedStatement ps, ps1;
BufferedReader br;

public JDBCTransactionDemo1() {
try 
{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/
test?user=root&password=root");
br = new BufferedReader(new InputStreamReader(System.in));

catch (Exception e
{
System.out.println("Error in connection" + e);
}
}
public void withdrawalAmt() 
{
try 
{
  System.out.println("Enter account no");
  String accid = br.readLine().trim();
  System.out.println("Enter Amount to withdrawal");
  double amt = Double.parseDouble(br.readLine().trim());
  con.setAutoCommit(false);
  ps = con.prepareStatement("update Account set amount=
  amount-? where accId=?");
  ps.setDouble(1, amt);
  ps.setString(2, accid);
  ps.executeUpdate();
  ps = con.prepareStatement("select amount from Account where accId=?");
  ps.setString(1, accid);
  ResultSet res = ps.executeQuery();
  res.next();
  double balAmt = res.getDouble(1);
  System.out.println("The balance amount is " + balAmt);
  if (balAmt < 1000
  {
    System.out.println("The transaction not complteted");
    con.rollback();
  
  else 
  {
    System.out.println("The transaction complteted");
    con.commit();
  }
  res.close();
  ps.close();

catch (Exception e
{
  e.printStackTrace();
}
}
public static void main(String[] args
{
new JDBCTransactionDemo1().withdrawalAmt();
}
}
Download

Previous Next

Comment by : Dinesh Bruse January 1st, 2014
Comment : This tutorial was so good... easy to understand.. Friends Please share Java/J2EE related stuff. in FB My ID Dinesh Bruse , JAVA CODE group.

Comment by : Dinesh Bruse January 1st, 2014
Comment : This tutorial was so good... easy to understand.. friends join this.. JAVA CODE in FB. My ID Dinesh Bruse.

Comment by : laxman October 12th, 2013
Comment : I'm getting the following error ---------------------------------------- java.sql.SQLException: ORA-01002: fetch out of sequence at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseErr


Write your Comment

Name :
Email Id : [ We will not publish your email id ]
Your Comment : [ Maximum 200 chars ]
Enter Security code