JDBC Transaction Example: Bank Fund Transfer

In this example, we will simulate a bank fund transfer between two accounts using JDBC transactions. The transfer involves debiting the amount from one account and crediting it to another. Both operations must succeed together; otherwise, the transaction is rolled back to maintain data integrity.

Understanding Commit and Rollback in Transactions

When working with databases, a transaction is a group of operations performed as a single unit. Two important concepts in transactions are commit and rollback:

डेटाबेस के साथ काम करते समय, एक ट्रांजैक्शन कई ऑपरेशन्स का एक समूह होता है जो एक साथ निष्पादित होते हैं। ट्रांजैक्शन में दो महत्वपूर्ण concepts हैं: commit और rollback।

Commit: Saves all the changes made in the transaction permanently to the database. Once committed, the changes cannot be undone by rollback.
Rollback: Undoes all changes made in the current transaction, reverting the database to its previous consistent state.

Example: Imagine transferring money from Account A to Account B:

First, money is deducted from Account A.
Then, money is added to Account B.

If both steps succeed, we commit the transaction — the transfer is saved permanently. But if, say, the money cannot be added to Account B due to an error, we rollback — undo the deduction from Account A to avoid losing money.

अगर दोनों स्टेप्स सफल होते हैं, तो हम ट्रांजैक्शन को commit करते हैं — ट्रांसफर स्थायी रूप से सेव हो जाता है। लेकिन अगर किसी कारण से Account B में पैसा नहीं डाला जा सकता, तो हम rollback करते हैं — Account A से किए गए पैसे की कटौती को वापस कर देते हैं ताकि पैसे खोए नं जाएं।

This ensures the database stays consistent and reliable.

Sample Database Table: `accounts`

Table structure for bank accounts:

CREATE TABLE accounts (
  account_number INT PRIMARY KEY,
  account_holder_name VARCHAR(100),
  balance DECIMAL(10, 2)
);

INSERT INTO accounts VALUES
(101, 'Aryan Singh', 5000.00),
(102, 'Riya Sharma', 3000.00);

Java Code: Bank Fund Transfer with Transaction

import java.sql.*;

public class BankFundTransfer {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/studentdb";
    String user = "root";
    String password = "your_mysql_password";

    int fromAccount = 101;
    int toAccount = 102;
    double amount = 1000.00;

    try (Connection con = DriverManager.getConnection(url, user, password)) {
      Class.forName("com.mysql.cj.jdbc.Driver");

      // Disable auto-commit for transaction
      con.setAutoCommit(false);

      try (PreparedStatement withdrawStmt = con.prepareStatement(
               "UPDATE accounts SET balance = balance - ? WHERE account_number = ? AND balance >= ?");
           PreparedStatement depositStmt = con.prepareStatement(
               "UPDATE accounts SET balance = balance + ? WHERE account_number = ?")) {

        // Withdraw amount from source account
        withdrawStmt.setDouble(1, amount);
        withdrawStmt.setInt(2, fromAccount);
        withdrawStmt.setDouble(3, amount);
        int rowsWithdrawn = withdrawStmt.executeUpdate();

        if (rowsWithdrawn == 0) {
          throw new SQLException("Insufficient balance or invalid account for withdrawal.");
        }

        // Deposit amount to destination account
        depositStmt.setDouble(1, amount);
        depositStmt.setInt(2, toAccount);
        int rowsDeposited = depositStmt.executeUpdate();

        if (rowsDeposited == 0) {
          throw new SQLException("Invalid destination account.");
        }

        // Commit the transaction if both succeed
        con.commit();
        System.out.println("Fund transfer successful.");

      } catch (SQLException e) {
        System.out.println("Transaction failed, rolling back.");
        con.rollback(); // rollback all changes on error
        e.printStackTrace();
      } finally {
        con.setAutoCommit(true); // restore default auto-commit mode
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Explanation

con.setAutoCommit(false); disables auto-commit so that multiple statements run in one transaction.
The withdrawStmt decreases the balance of the source account if sufficient funds are available.
If withdrawal fails (no rows updated), an exception is thrown to prevent partial update.
The depositStmt increases the balance of the destination account.
If deposit fails, an exception is thrown, and the entire transaction is rolled back.
con.commit(); commits both operations together, ensuring atomicity.
If any error occurs, con.rollback(); undoes all changes made in the transaction.

Expected Output

Fund transfer successful.

This message indicates the fund transfer completed successfully as one atomic transaction.

Account Balances Before and After Transfer

You can check account balances before and after the transaction by running:

SELECT account_number, account_holder_name, balance FROM accounts;

Sample output before transfer:

+----------------+--------------------+----------+
| account_number | account_holder_name | balance  |
+----------------+--------------------+----------+
| 101            | Aryan Singh        | 5000.00  |
| 102            | Riya Sharma        | 3000.00  |
+----------------+--------------------+----------+

Sample output after transfer of 1000 from 101 to 102:

+----------------+--------------------+----------+
| account_number | account_holder_name | balance  |
+----------------+--------------------+----------+
| 101            | Aryan Singh        | 4000.00  |
| 102            | Riya Sharma        | 4000.00  |
+----------------+--------------------+----------+

Previous: Call Stored Proceure in java → ← Next: jdbc batch processing java