JDBC Transaction Management

« Prev Next »

A transaction represents a group of SQL commands that run together as one complete operation. It must follow the ACID properties:

  • Atomicity ensures that either all operations in a transaction finish successfully or none of them do.
  • Consistency – database remains in a valid state
  • Isolation – concurrent transactions don't interfere
  • Durability – changes persist even after system failure

Default Auto-commit Behavior

JDBC executes SQL statements with immediate commit as its default behavior. This is controlled by:

connection.setAutoCommit(true);  // Default

To manage transactions manually, disable auto-commit.

JDBC Transaction Management

 Connection con = DriverManager.getConnection(...);

// Step 1: Disable auto-commit
con.setAutoCommit(false);

try {
    // Step 2: Execute multiple SQL operations
    Statement stmt = con.createStatement();
    stmt.executeUpdate("UPDATE accounts SET balance = balance - 1000 WHERE id = 1");
    stmt.executeUpdate("UPDATE accounts SET balance = balance + 1000 WHERE id = 2");

    // Step 3: Commit transaction
    con.commit();
} catch (SQLException e) {
    // Step 4: Rollback on error
    con.rollback();
    e.printStackTrace();
} finally {
    con.close();
}

Example

import java.sql.*;

public class TransactionExample {
    public static void main(String[] args) {
        try (Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/bank", "root", "password")) {

            con.setAutoCommit(false);  // Begin transaction

            try (PreparedStatement withdraw = con.prepareStatement(
                        "UPDATE accounts SET balance = balance - ? WHERE id = ?");
                 PreparedStatement deposit = con.prepareStatement(
                        "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {

                withdraw.setDouble(1, 1000);
                withdraw.setInt(2, 1);
                withdraw.executeUpdate();

                deposit.setDouble(1, 1000);
                deposit.setInt(2, 2);
                deposit.executeUpdate();

                con.commit();  // Commit if both succeed
                System.out.println("Transaction committed.");
            } catch (SQLException e) {
                con.rollback();  // Rollback if any error occurs
                System.out.println("Transaction rolled back.");
                e.printStackTrace();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Note

  • Always disable auto-commit before starting a transaction.
  • Use commit() to persist changes.
  • The rollback() method allows you to reverse changes if errors occur.
  • Implement try-with-resources to ensure both connection and statement operations are handled safely.
« Prev Next »

Quickly Find What You Are Looking For

Onlinetpoint is optimized for basic learning, practice and more. Examples are well checked and working examples available on this website but we can't give assurity for 100% correctness of all the content. This site under copyright content belongs to Onlinetpoint. You agree to have read and accepted our terms of use, cookie and privacy policy.