• Skip to main content
  • Skip to primary sidebar
  • Skip to secondary sidebar
  • Skip to footer

Computer Notes

Library
    • Computer Fundamental
    • Computer Memory
    • DBMS Tutorial
    • Operating System
    • Computer Networking
    • C Programming
    • C++ Programming
    • Java Programming
    • C# Programming
    • SQL Tutorial
    • Management Tutorial
    • Computer Graphics
    • Compiler Design
    • Style Sheet
    • JavaScript Tutorial
    • Html Tutorial
    • Wordpress Tutorial
    • Python Tutorial
    • PHP Tutorial
    • JSP Tutorial
    • AngularJS Tutorial
    • Data Structures
    • E Commerce Tutorial
    • Visual Basic
    • Structs2 Tutorial
    • Digital Electronics
    • Internet Terms
    • Servlet Tutorial
    • Software Engineering
    • Interviews Questions
    • Basic Terms
    • Troubleshooting
Menu

Header Right

Home » Java » Jdbc » Transaction Management in JDBC
Next →
← Prev

Transaction Management in JDBC

By Dinesh Thakur

A transaction is a group of operations used to perform a particular task. In a transaction if one operation fails then all operations of the transaction gets cancelled. Finally, the transaction status fails.

If all operations in a transaction are successfully executed, then only the transaction status is successful. A transaction follows the principle of –All or Nothing. For example, booking a movie ticket online is a transaction.

Transactions are of two types:

1. Local transaction

2. Distributed transaction (Global Transaction)

                    Type of Transactions

We’ll be covering the following topics in this tutorial:

  • Local Transaction
  • Distributed Transaction
  • Transaction property
  • Transaction management method in JDBC

Local Transaction

Local transaction means all operations are executed on a single database. In Java, local transaction can be done by using JDBC, hibernate, Enterprise JavaBeans (EJB), spring framework.

                    Local Transaction

Distributed Transaction

Distributed transaction means the operations are executed on more than one database.

In Java, distributed transaction can be done either using EJB technology or by using spring framework.

                     Distributed Transaction

Transaction property

1. Atomicity

Every transaction is an atomic operation. It means “follow all or nothing principle”. For example, in a transfer of money operation-in a bank, if an amount is withdrawn from account 1 and it is deposited to account 2. Here either both operations are done or both are cancelled. So it is an atomic operation.

2. Consistency

After the transaction is completed successfully or fails, the data left in the database, should be reliable. Reliable data is called consistent data.

3. Isolation

If two transaction are working on the same data then one transaction does not clash (collide) with the other. It means transaction is isolated.

4. Durability

The data generated after completion of transaction will be long-lasting data, until another transaction is done on that data. We call these data durable.

Transaction management method in JDBC

The following are the three methods given by Connection interface in JDBC to perform transaction management.

i.  setAutoCommit (false)

ii. commit ()

iii. rollback ()

From a Java program if any SQL operation is executed, it will be permanently executed on database by default. If operations are permanently executed in database then we cannot cancel them. So transaction management is not possible.

If we want to perform transaction management in JDBC program, we should execute operation on database as temporarily. Later, we can either commit or cancel the operation.

If we want to execute SQL operation temporarily from a Java program onto database, then first of all, we need to disable auto commit mode on JDBC program. To disable auto commit mode we need to call setAutocommit (-) method.

If execution occurs while executing database operation, then we need to cancel the transaction by calling rollback (-) method.

If all operations are successfully executed, then we need to commit the transaction by calling commit (-) method.

Need for try catch in transaction management

In JDBC program we need to put the operation of transaction within try block, because Java program knows whether an error has occurred in transaction or not by observing the control in the catch block.

If the control entereds the catch block, it means an error has occurred. So, we will rollback the transaction.

If the control does not enter the catch block, it means no exception has occurred. So we commit the transaction.

This account_balance is created by the following columns and data types on which

we are going to perform transaction management:

            

The source code for the transaction management:

 

BankingTest.java

 

import java.sql.*;

import java.util.Scanner;

public class BankingTest

{

      public static void main (String args []) throws Exception

       {

             //to take the input from keyboard

             Scanner s = new Scanner (System.in);

             System.out.println (“Enter the Source account number :” );

             int saccno = s.nextInt ();

             System.out.println (“Enter the Destination account number :”);  

             int daccno = s.nextInt ();

            System.out.println (“Enter the amount to transfer :”);

            int amnt = s.nextInt ();

            Class.forName (“oracle.jdbc.driver.OracleDriver :”);

            Connection cn = DriverManager.getConnection

            (“jdbc:oracle:thin:@localhost:1521:orcl”, “scott”, “tiger”);

            Statement st = cn.createStatement ();

            cn.setAutoCommit (false);

            ResultSet rs = st.executeQuery (“select avail_balance from account_balance where account_number=”+saccno);

            rs.next ();

            int abal = rs.getInt (1);

            //System.out.print (abal);

            if (abal>amnt)

            {

                 int up = st.executeUpdate (“update account_balance set avail_balance =avail_balance-“+amnt+ “where account_number=”+saccno);

                 int up1 = st.executeUpdate (“update account_balance set avail_balance = avail_balance-“+amnt+ “where account_number=”+daccno);

                 //System.out.print (up+” “+up1);

                 if (up==1 && up1==1)

                 {

                      cn.commit ();

                      System.out.println (“*******: “+amnt+” balance is successfully Transferred:*******”);

                 }

                 else

                  {

                      cn.rollback ();

                      System.out.println (“rollback”);

                 }

            }

            else

              {

                    System.out.println (“You does not have sufficient balance !!! please deposit in your account.”);

              }

      }

}

In the above program:

cn.setAutoCommit (false): Used to disable auto commit mode. Auto commit mode is disabled before any transaction happens, so the any data cannot be stored automatically.

cn.commit () It commits the transaction after the successful execution of the transaction.

cn.rollback ()  If some problem occurs in the transaction, it cancels the entire transaction.

Transaction is performed by running the program. Here, transaction is successful. You can check it from the database table.

This program also shows error message when the transaction has more than the available balance.

                

                

               

You’ll also like:

  1. What is Transaction in DBMS? Explain Process,States and Properties of Transaction.
  2. What is JDBC API? Important Goals of JDBC-API.
  3. JDBC Join
  4. SAVE POINT in JDBC
  5. JDBC – ResultSetMetaData Interface
Next →
← Prev
Like/Subscribe us for latest updates     

About Dinesh Thakur
Dinesh ThakurDinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular Computer Notes blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps.

Dinesh Thakur is a Freelance Writer who helps different clients from all over the globe. Dinesh has written over 500+ blogs, 30+ eBooks, and 10000+ Posts for all types of clients.


For any type of query or something that you think is missing, please feel free to Contact us.


Primary Sidebar

SQL Tutorials

SQL Tutorials

  • SQL - Home
  • SQL - Select
  • SQL - Create
  • SQL - View
  • SQL - Sub Queries
  • SQL - Update
  • SQL - Delete
  • SQL - Order By
  • SQL - Select Distinct
  • SQL - Group By
  • SQL - Where Clause
  • SQL - Select Into
  • SQL - Insert Into
  • SQL - Sequence
  • SQL - Constraints
  • SQL - Alter
  • SQL - Date
  • SQL - Foreign Key
  • SQL - Like Operator
  • SQL - CHECK Constraint
  • SQL - Exists Operator
  • SQL - Drop Table
  • SQL - Alias Syntax
  • SQL - Primary Key
  • SQL - Not Null
  • SQL - Union Operator
  • SQL - Unique Constraint
  • SQL - Between Operator
  • SQL - Having Clause
  • SQL - Isnull() Function
  • SQL - IN Operator
  • SQL - Default Constraint
  • SQL - Minus Operator
  • SQL - Intersect Operator
  • SQL - Triggers
  • SQL - Cursors

Advanced SQL

  • SQL - Joins
  • SQL - Index
  • SQL - Self Join
  • SQL - Outer Join
  • SQL - Join Types
  • SQL - Cross Join
  • SQL - Left Outer Join
  • SQL - Right Join
  • SQL - Drop Index
  • SQL - Inner Join
  • SQL - Datediff() Function
  • SQL - NVL Function
  • SQL - Decode Function
  • SQL - Datepart() Function
  • SQL - Count Function
  • SQL - Getdate() Function
  • SQL - Cast() Function
  • SQL - Round() Function

Other Links

  • SQL - PDF Version

Footer

Basic Course

  • Computer Fundamental
  • Computer Networking
  • Operating System
  • Database System
  • Computer Graphics
  • Management System
  • Software Engineering
  • Digital Electronics
  • Electronic Commerce
  • Compiler Design
  • Troubleshooting

Programming

  • Java Programming
  • Structured Query (SQL)
  • C Programming
  • C++ Programming
  • Visual Basic
  • Data Structures
  • Struts 2
  • Java Servlet
  • C# Programming
  • Basic Terms
  • Interviews

World Wide Web

  • Internet
  • Java Script
  • HTML Language
  • Cascading Style Sheet
  • Java Server Pages
  • Wordpress
  • PHP
  • Python Tutorial
  • AngularJS
  • Troubleshooting

 About Us |  Contact Us |  FAQ

Dinesh Thakur is a Technology Columinist and founder of Computer Notes.

Copyright © 2023. All Rights Reserved.