transaction

import java.sql.*;

public class TransactionExample {

    public static void main(String[] args) {
        // Database URL and credentials
        String dbUrl = "jdbc:mysql://localhost:3306/nehal";
        String user = "root";
        String password = "nehal@123";

        Connection conn = null;

        try {
            // Establish connection
            conn = DriverManager.getConnection(dbUrl, user, password);

            // Disable auto-commit mode
            conn.setAutoCommit(false);

            // SQL queries
            String deductMoney = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
            String addMoney = "UPDATE accounts SET balance = balance + ? WHERE id = ?";

            try (
                PreparedStatement stmt1 = conn.prepareStatement(deductMoney);
                PreparedStatement stmt2 = conn.prepareStatement(addMoney)
            ) {
                // Deduct money from account 1
                stmt1.setDouble(1, 500.0); // Deduct $500
                stmt1.setInt(2, 1);        // From account ID 1
                stmt1.executeUpdate();

                // Add money to account 2
                stmt2.setDouble(1, 500.0); // Add $500
                stmt2.setInt(2, 2);        // To account ID 2
                stmt2.executeUpdate();

                // Commit the transaction
                conn.commit();
                System.out.println("Transaction successful!");
            } catch (SQLException e) {
                // Rollback the transaction in case of an error
                if (conn != null) {
                    conn.rollback();
                    System.out.println("Transaction rolled back due to an error.");
                }
                e.printStackTrace();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    // Re-enable auto-commit mode
                    conn.setAutoCommit(true);
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}