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 a website that is meant to offer basic knowledge, practice and learning materials. Though all the examples have been tested and verified, we cannot ensure the correctness or completeness of all the information on our website. All contents published on this website are subject to copyright and are owned by OnlineTpoint. By using this website, you agree that you have read and understood our Terms of Use, Cookie Policy and Privacy Policy.