Database Connectivity: JDBC Architecture & Transactions

import java.sql.*;

public class EnterpriseBankingDAO {
    // Using embedded In-Memory H2 / SQLite / MySQL compatible JDBC URL
    private static final String URL = "jdbc:h2:mem:bankdb;DB_CLOSE_DELAY=-1";
    private static final String USER = "sa";
    private static final String PASS = "";

    public static void main(String[] args) {
        System.out.println("==================================================");
        System.out.println("   DEVASCENT ENTERPRISE JDBC & TRANSACTION ENGINE ");
        System.out.println("==================================================");

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
            System.out.println("✓ Connected to Database: " + conn.getMetaData().getDatabaseProductName());

            // 1. CREATE Table (DDL)
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("CREATE TABLE accounts (" +
                        "id INT PRIMARY KEY, " +
                        "holder_name VARCHAR(50), " +
                        "balance DOUBLE)");
                System.out.println("✓ Created table 'accounts'");
            }

            // 2. INSERT Accounts (CRUD: Create) via PreparedStatement
            String insertSql = "INSERT INTO accounts (id, holder_name, balance) VALUES (?, ?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSql)) {
                pstmt.setInt(1, 101);
                pstmt.setString(2, "Alice Vance");
                pstmt.setDouble(3, 5000.00);
                pstmt.executeUpdate();

                pstmt.setInt(1, 102);
                pstmt.setString(2, "Bob Miller");
                pstmt.setDouble(3, 1200.00);
                pstmt.executeUpdate();
                System.out.println("✓ Inserted 2 initial account records");
            }

            // 3. READ & Print Accounts (CRUD: Read) via ResultSet
            displayAccounts(conn, "Initial State");

            // 4. ATOMIC TRANSACTION: Transfer $1,000 from Alice (101) to Bob (102)
            System.out.println("\n--- Executing Atomic Transaction: Transfer $1000 ---");
            transferFunds(conn, 101, 102, 1000.00);

            displayAccounts(conn, "Post-Transfer State");

            // 5. DELETE Account (CRUD: Delete)
            String deleteSql = "DELETE FROM accounts WHERE id = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(deleteSql)) {
                pstmt.setInt(1, 102);
                int rows = pstmt.executeUpdate();
                System.out.println("\n✓ Deleted Account #102 (Rows affected: " + rows + ")");
            }

            displayAccounts(conn, "Final State");
            System.out.println("==================================================");

        } catch (SQLException e) {
            System.err.println("Database Failure: " + e.getMessage());
        }
    }

    // Transaction Controller
    private static void transferFunds(Connection conn, int fromId, int toId, double amount) throws SQLException {
        String debitSql = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
        String creditSql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";

        boolean originalAutoCommit = conn.getAutoCommit();
        try {
            conn.setAutoCommit(false); // START TRANSACTION

            try (PreparedStatement debit = conn.prepareStatement(debitSql);
                 PreparedStatement credit = conn.prepareStatement(creditSql)) {

                // Step A: Debit Sender
                debit.setDouble(1, amount);
                debit.setInt(2, fromId);
                debit.executeUpdate();

                // Step B: Credit Receiver
                credit.setDouble(1, amount);
                credit.setInt(2, toId);
                credit.executeUpdate();

                conn.commit(); // ALL STEPS SUCCEED: PERSIST
                System.out.println("  ✓ Transaction committed atomically!");
            }
        } catch (SQLException ex) {
            conn.rollback(); // ERROR DETECTED: REVERT ALL CHANGES
            System.err.println("  ✗ Transaction rolled back: " + ex.getMessage());
            throw ex;
        } finally {
            conn.setAutoCommit(originalAutoCommit); // Restore state
        }
    }

    // Helper: Query and Display Table
    private static void displayAccounts(Connection conn, String title) throws SQLException {
        String query = "SELECT id, holder_name, balance FROM accounts ORDER BY id ASC";
        System.out.println("\n--- Accounts Snapshot [" + title + "] ---");
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {
            while (rs.next()) {
                System.out.printf("  Account #%d | Holder: %-12s | Balance: $%.2f%n",
                        rs.getInt("id"),
                        rs.getString("holder_name"),
                        rs.getDouble("balance"));
            }
        }
    }
}