Database

<!-- MySQL -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.3.0</version>
</dependency>

<!-- PostgreSQL -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.3</version>
</dependency>

<!-- H2 (in-memory, great for testing) -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.2.224</version>
</dependency>

// MySQL
implementation 'com.mysql:mysql-connector-j:8.3.0'

// PostgreSQL
implementation 'org.postgresql:postgresql:42.7.3'

// H2
implementation 'com.h2database:h2:2.2.224'

// Legacy approach -- no longer needed since Java 6 / JDBC 4.0
Class.forName("com.mysql.cj.jdbc.Driver");
Class.forName("org.postgresql.Driver");

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

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

        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, user, password);
            System.out.println("Connected to database!");
            System.out.println("Database: " + connection.getCatalog());
            System.out.println("Auto-commit: " + connection.getAutoCommit());

            // ... use connection ...

        } catch (SQLException e) {
            System.err.println("Connection failed: " + e.getMessage());
            System.err.println("SQL State: " + e.getSQLState());
            System.err.println("Error Code: " + e.getErrorCode());
        } finally {
            // Must close manually -- easy to forget!
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
// Output:
// Connected to database!
// Database: mydb
// Auto-commit: true

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ProperConnection {
    // Store these in environment variables or a config file, never hardcoded
    private static final String URL = "jdbc:mysql://localhost:3306/mydb";
    private static final String USER = "root";
    private static final String PASSWORD = "secret";

    public static void main(String[] args) {
        // try-with-resources -- connection auto-closes when block ends
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            System.out.println("Connected!");
            System.out.println("Valid: " + conn.isValid(5)); // 5-second timeout
            // ... do work ...
        } catch (SQLException e) {
            // Connection automatically closed, even on exception
            System.err.println("Error: " + e.getMessage());
        }
        // No finally block needed -- connection is guaranteed closed
    }
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class ConnectionWithProperties {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";

        Properties props = new Properties();
        props.setProperty("user", "root");
        props.setProperty("password", "secret");
        props.setProperty("useSSL", "true");
        props.setProperty("serverTimezone", "UTC");
        props.setProperty("characterEncoding", "UTF-8");
        props.setProperty("connectTimeout", "5000");  // 5 seconds

        try (Connection conn = DriverManager.getConnection(url, props)) {
            System.out.println("Connected with properties!");
        } catch (SQLException e) {
            System.err.println("Connection failed: " + e.getMessage());
        }
    }
}

import java.sql.*;

public class StatementExample {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:h2:mem:testdb", "sa", "")) {

            Statement stmt = conn.createStatement();

            // DDL -- creating a table (no user input, Statement is fine)
            stmt.executeUpdate("CREATE TABLE countries (id INT PRIMARY KEY, name VARCHAR(50))");

            // Static insert (no user input)
            stmt.executeUpdate("INSERT INTO countries VALUES (1, 'United States')");
            stmt.executeUpdate("INSERT INTO countries VALUES (2, 'Japan')");

            // Static query
            ResultSet rs = stmt.executeQuery("SELECT * FROM countries");
            while (rs.next()) {
                System.out.println(rs.getInt("id") + ": " + rs.getString("name"));
            }
        }
    }
}
// Output:
// 1: United States
// 2: Japan

// DANGEROUS -- NEVER DO THIS
public User findUser(String username) throws SQLException {
    Statement stmt = conn.createStatement();

    // User input is concatenated directly into SQL
    String sql = "SELECT * FROM users WHERE username = '" + username + "'";
    ResultSet rs = stmt.executeQuery(sql);
    // ...
}

// Normal input: username = "john"
// SQL becomes: SELECT * FROM users WHERE username = 'john'   <-- works fine

// Malicious input: username = "' OR '1'='1"
// SQL becomes: SELECT * FROM users WHERE username = '' OR '1'='1'
//              This returns ALL users!

// Even worse: username = "'; DROP TABLE users; --"
// SQL becomes: SELECT * FROM users WHERE username = ''; DROP TABLE users; --'
//              This DELETES your entire users table!

// SAFE -- Always use PreparedStatement for any query involving user input
public User findUser(String username) throws SQLException {
    String sql = "SELECT * FROM users WHERE username = ?";
    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, username);  // Parameter index starts at 1
        ResultSet rs = pstmt.executeQuery();
        // ...
    }
}

// Input: username = "' OR '1'='1"
// The driver treats the ENTIRE string as a literal value, not SQL.
// It effectively becomes: WHERE username = '\' OR \'1\'=\'1'
// No injection possible!

import java.sql.*;

public class CreateTableExample {
    public static void main(String[] args) {
        String url = "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1";

        try (Connection conn = DriverManager.getConnection(url, "sa", "")) {

            String sql = """
                CREATE TABLE IF NOT EXISTS users (
                    id          INT AUTO_INCREMENT PRIMARY KEY,
                    username    VARCHAR(50)  NOT NULL UNIQUE,
                    email       VARCHAR(100) NOT NULL,
                    age         INT,
                    salary      DECIMAL(10,2),
                    active      BOOLEAN DEFAULT TRUE,
                    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
                """;

            try (Statement stmt = conn.createStatement()) {
                stmt.executeUpdate(sql);
                System.out.println("Table 'users' created successfully.");
            }

        } catch (SQLException e) {
            System.err.println("Error: " + e.getMessage());
        }
    }
}
// Output:
// Table 'users' created successfully.

public class InsertExample {

    // Insert a single user and return the generated ID
    public static long insertUser(Connection conn, String username, String email,
                                   int age, double salary) throws SQLException {
        String sql = "INSERT INTO users (username, email, age, salary) VALUES (?, ?, ?, ?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql,
                Statement.RETURN_GENERATED_KEYS)) {

            pstmt.setString(1, username);
            pstmt.setString(2, email);
            pstmt.setInt(3, age);
            pstmt.setDouble(4, salary);

            int rowsAffected = pstmt.executeUpdate();
            System.out.println(rowsAffected + " row(s) inserted.");

            // Retrieve the auto-generated key
            try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    long id = generatedKeys.getLong(1);
                    System.out.println("Generated ID: " + id);
                    return id;
                }
            }
        }
        return -1;
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1", "sa", "")) {

            // Create table first (from previous example)
            // ...

            insertUser(conn, "john_doe", "john@example.com", 28, 75000.00);
            insertUser(conn, "jane_smith", "jane@example.com", 32, 92000.00);
            insertUser(conn, "bob_wilson", "bob@example.com", 45, 68000.00);
        }
    }
}
// Output:
// 1 row(s) inserted.
// Generated ID: 1
// 1 row(s) inserted.
// Generated ID: 2
// 1 row(s) inserted.
// Generated ID: 3

public class SelectExample {

    // Select all users
    public static void selectAllUsers(Connection conn) throws SQLException {
        String sql = "SELECT id, username, email, age, salary, active, created_at FROM users";

        try (PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            System.out.println("--- All Users ---");
            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String email = rs.getString("email");
                int age = rs.getInt("age");
                double salary = rs.getDouble("salary");
                boolean active = rs.getBoolean("active");
                Timestamp createdAt = rs.getTimestamp("created_at");

                System.out.printf("ID: %d | %s | %s | Age: %d | $%.2f | Active: %b | %s%n",
                        id, username, email, age, salary, active, createdAt);
            }
        }
    }

    // Select with a WHERE clause
    public static void selectUserByUsername(Connection conn, String username) throws SQLException {
        String sql = "SELECT * FROM users WHERE username = ?";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, username);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    System.out.println("Found: " + rs.getString("username")
                            + " (" + rs.getString("email") + ")");
                } else {
                    System.out.println("User not found: " + username);
                }
            }
        }
    }

    // Select with multiple conditions
    public static void selectUsersAboveSalary(Connection conn, double minSalary)
            throws SQLException {
        String sql = "SELECT username, salary FROM users WHERE salary > ? AND active = ? ORDER BY salary DESC";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setDouble(1, minSalary);
            pstmt.setBoolean(2, true);

            try (ResultSet rs = pstmt.executeQuery()) {
                System.out.println("Users earning above $" + minSalary + ":");
                while (rs.next()) {
                    System.out.printf("  %s: $%.2f%n",
                            rs.getString("username"), rs.getDouble("salary"));
                }
            }
        }
    }
}
// Output:
// --- All Users ---
// ID: 1 | john_doe | john@example.com | Age: 28 | $75000.00 | Active: true | 2024-01-15 10:30:00.0
// ID: 2 | jane_smith | jane@example.com | Age: 32 | $92000.00 | Active: true | 2024-01-15 10:30:00.0
// ID: 3 | bob_wilson | bob@example.com | Age: 45 | $68000.00 | Active: true | 2024-01-15 10:30:00.0
//
// Found: john_doe (john@example.com)
//
// Users earning above $70000.0:
//   jane_smith: $92000.00
//   john_doe: $75000.00

public class UpdateExample {

    public static int updateUserEmail(Connection conn, int userId, String newEmail)
            throws SQLException {
        String sql = "UPDATE users SET email = ? WHERE id = ?";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, newEmail);
            pstmt.setInt(2, userId);

            int rowsAffected = pstmt.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("Updated email for user ID " + userId);
            } else {
                System.out.println("No user found with ID " + userId);
            }
            return rowsAffected;
        }
    }

    public static int giveRaise(Connection conn, double percentage, double minCurrentSalary)
            throws SQLException {
        String sql = "UPDATE users SET salary = salary * (1 + ? / 100) WHERE salary >= ?";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setDouble(1, percentage);
            pstmt.setDouble(2, minCurrentSalary);

            int rowsAffected = pstmt.executeUpdate();
            System.out.println(rowsAffected + " user(s) received a " + percentage + "% raise.");
            return rowsAffected;
        }
    }
}
// Output:
// Updated email for user ID 1
// 2 user(s) received a 10.0% raise.

public class DeleteExample {

    public static int deleteUser(Connection conn, int userId) throws SQLException {
        String sql = "DELETE FROM users WHERE id = ?";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, userId);

            int rowsAffected = pstmt.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("Deleted user with ID " + userId);
            } else {
                System.out.println("No user found with ID " + userId);
            }
            return rowsAffected;
        }
    }

    // Soft delete -- better practice than hard delete
    public static int deactivateUser(Connection conn, int userId) throws SQLException {
        String sql = "UPDATE users SET active = false WHERE id = ? AND active = true";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, userId);

            int rowsAffected = pstmt.executeUpdate();
            System.out.println(rowsAffected > 0 ? "User deactivated." : "User not found or already inactive.");
            return rowsAffected;
        }
    }
}
// Output:
// Deleted user with ID 3
// User deactivated.

public class ResultSetNavigation {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:testdb", "sa", "")) {
            // Setup
            Statement setup = conn.createStatement();
            setup.executeUpdate("CREATE TABLE products (id INT, name VARCHAR(50), price DECIMAL(10,2))");
            setup.executeUpdate("INSERT INTO products VALUES (1, 'Laptop', 999.99)");
            setup.executeUpdate("INSERT INTO products VALUES (2, 'Mouse', 29.99)");
            setup.executeUpdate("INSERT INTO products VALUES (3, 'Keyboard', 79.99)");

            // Basic forward-only iteration
            try (PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM products");
                 ResultSet rs = pstmt.executeQuery()) {

                // Access by column name (preferred -- more readable)
                while (rs.next()) {
                    String name = rs.getString("name");
                    double price = rs.getDouble("price");
                    System.out.println(name + ": $" + price);
                }
            }

            // Access by column index (1-based, faster but fragile)
            try (PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM products");
                 ResultSet rs = pstmt.executeQuery()) {

                while (rs.next()) {
                    int id = rs.getInt(1);        // column 1
                    String name = rs.getString(2); // column 2
                    double price = rs.getDouble(3); // column 3
                    System.out.println(id + ". " + name + ": $" + price);
                }
            }
        }
    }
}
// Output:
// Laptop: $999.99
// Mouse: $29.99
// Keyboard: $79.99
// 1. Laptop: $999.99
// 2. Mouse: $29.99
// 3. Keyboard: $79.99

// If age column is NULL in the database:
int age = rs.getInt("age");      // Returns 0 (not null, because int is a primitive)
boolean wasNull = rs.wasNull();  // Returns true -- the actual value was NULL

// Better approach: use wrapper types with getObject()
Integer age = rs.getObject("age", Integer.class);  // Returns null if column is NULL
if (age == null) {
    System.out.println("Age not provided");
} else {
    System.out.println("Age: " + age);
}

// For String, getString() already returns null for NULL columns
String email = rs.getString("email");  // Returns null if column is NULL

// Create a scrollable ResultSet
try (PreparedStatement pstmt = conn.prepareStatement(
        "SELECT * FROM products",
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY)) {

    ResultSet rs = pstmt.executeQuery();

    // Move to last row
    rs.last();
    System.out.println("Last product: " + rs.getString("name"));
    System.out.println("Total rows: " + rs.getRow());

    // Move to first row
    rs.first();
    System.out.println("First product: " + rs.getString("name"));

    // Move to a specific row
    rs.absolute(2);
    System.out.println("Second product: " + rs.getString("name"));

    // Move relative to current position
    rs.relative(-1);  // one row back
    System.out.println("Back to first: " + rs.getString("name"));
}
// Output:
// Last product: Keyboard
// Total rows: 3
// First product: Laptop
// Second product: Mouse
// Back to first: Laptop

try (PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM users");
     ResultSet rs = pstmt.executeQuery()) {

    ResultSetMetaData meta = rs.getMetaData();
    int columnCount = meta.getColumnCount();

    // Print column info
    System.out.println("Columns in result set:");
    for (int i = 1; i <= columnCount; i++) {
        System.out.printf("  %s (%s) - Nullable: %s%n",
                meta.getColumnName(i),
                meta.getColumnTypeName(i),
                meta.isNullable(i) == ResultSetMetaData.columnNullable ? "Yes" : "No");
    }

    // Print all data dynamically
    while (rs.next()) {
        for (int i = 1; i <= columnCount; i++) {
            System.out.print(meta.getColumnName(i) + "=" + rs.getObject(i) + " | ");
        }
        System.out.println();
    }
}
// Output:
// Columns in result set:
//   ID (INTEGER) - Nullable: No
//   USERNAME (VARCHAR) - Nullable: No
//   EMAIL (VARCHAR) - Nullable: No
//   AGE (INTEGER) - Nullable: Yes
//   SALARY (DECIMAL) - Nullable: Yes
//   ACTIVE (BOOLEAN) - Nullable: Yes
//   CREATED_AT (TIMESTAMP) - Nullable: Yes

import java.sql.*;

public class TransactionExample {

    public static void transferMoney(Connection conn, int fromAccountId,
                                      int toAccountId, double amount) throws SQLException {

        // Save auto-commit state to restore later
        boolean originalAutoCommit = conn.getAutoCommit();

        try {
            // Step 1: Disable auto-commit -- starts a transaction
            conn.setAutoCommit(false);

            // Step 2: Debit from source account
            String debitSql = "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?";
            try (PreparedStatement debit = conn.prepareStatement(debitSql)) {
                debit.setDouble(1, amount);
                debit.setInt(2, fromAccountId);
                debit.setDouble(3, amount);

                int rows = debit.executeUpdate();
                if (rows == 0) {
                    throw new SQLException("Insufficient funds or account not found: " + fromAccountId);
                }
            }

            // Step 3: Credit to destination account
            String creditSql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
            try (PreparedStatement credit = conn.prepareStatement(creditSql)) {
                credit.setDouble(1, amount);
                credit.setInt(2, toAccountId);

                int rows = credit.executeUpdate();
                if (rows == 0) {
                    throw new SQLException("Destination account not found: " + toAccountId);
                }
            }

            // Step 4: Both operations succeeded -- commit
            conn.commit();
            System.out.printf("Transferred $%.2f from account %d to account %d%n",
                    amount, fromAccountId, toAccountId);

        } catch (SQLException e) {
            // Step 5: Something went wrong -- rollback ALL changes
            conn.rollback();
            System.err.println("Transfer failed, rolled back: " + e.getMessage());
            throw e;

        } finally {
            // Restore original auto-commit state
            conn.setAutoCommit(originalAutoCommit);
        }
    }
}
// Output (success):
// Transferred $500.00 from account 1 to account 2
//
// Output (failure):
// Transfer failed, rolled back: Insufficient funds or account not found: 1

public static void processOrderWithSavepoint(Connection conn) throws SQLException {
    conn.setAutoCommit(false);
    Savepoint orderSavepoint = null;

    try {
        // Insert the order
        try (PreparedStatement pstmt = conn.prepareStatement(
                "INSERT INTO orders (customer_id, total) VALUES (?, ?)")) {
            pstmt.setInt(1, 42);
            pstmt.setDouble(2, 299.99);
            pstmt.executeUpdate();
        }

        // Mark a savepoint after the order is created
        orderSavepoint = conn.setSavepoint("afterOrder");

        // Try to insert shipping info (might fail)
        try {
            try (PreparedStatement pstmt = conn.prepareStatement(
                    "INSERT INTO shipping (order_id, address) VALUES (?, ?)")) {
                pstmt.setInt(1, 1);
                pstmt.setString(2, "123 Main St");
                pstmt.executeUpdate();
            }
        } catch (SQLException e) {
            // Shipping failed -- roll back to savepoint (order is still intact)
            conn.rollback(orderSavepoint);
            System.out.println("Shipping failed, but order preserved. Will ship later.");
        }

        conn.commit();
        System.out.println("Order processed successfully.");

    } catch (SQLException e) {
        conn.rollback();  // Roll back everything
        throw e;
    } finally {
        conn.setAutoCommit(true);
    }
}
// Output:
// Order processed successfully.

import java.sql.*;

public class BatchExample {

    public static void batchInsertUsers(Connection conn, List<String[]> users) throws SQLException {
        String sql = "INSERT INTO users (username, email, age) VALUES (?, ?, ?)";

        conn.setAutoCommit(false);  // Wrap batch in a transaction for performance

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {

            int batchSize = 500;  // Flush every 500 rows to avoid memory issues
            int count = 0;

            for (String[] user : users) {
                pstmt.setString(1, user[0]);  // username
                pstmt.setString(2, user[1]);  // email
                pstmt.setInt(3, Integer.parseInt(user[2]));  // age

                pstmt.addBatch();  // Add to batch
                count++;

                if (count % batchSize == 0) {
                    pstmt.executeBatch();  // Send batch to database
                    pstmt.clearBatch();    // Clear for next batch
                    System.out.println("Flushed " + count + " rows...");
                }
            }

            // Execute remaining records
            pstmt.executeBatch();

            conn.commit();
            System.out.println("Batch insert complete: " + count + " total rows.");

        } catch (SQLException e) {
            conn.rollback();
            System.err.println("Batch failed, rolled back: " + e.getMessage());
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:testdb", "sa", "")) {
            // Create table...

            List<String[]> users = List.of(
                new String[]{"user1", "user1@mail.com", "25"},
                new String[]{"user2", "user2@mail.com", "30"},
                new String[]{"user3", "user3@mail.com", "35"},
                new String[]{"user4", "user4@mail.com", "28"},
                new String[]{"user5", "user5@mail.com", "42"}
            );

            batchInsertUsers(conn, users);
        }
    }
}
// Output:
// Batch insert complete: 5 total rows.

try (Statement stmt = conn.createStatement()) {
    conn.setAutoCommit(false);

    stmt.addBatch("INSERT INTO audit_log (action) VALUES ('User created')");
    stmt.addBatch("UPDATE users SET active = true WHERE id = 1");
    stmt.addBatch("DELETE FROM temp_tokens WHERE expired = true");

    int[] results = stmt.executeBatch();
    // results[0] = rows affected by first statement
    // results[1] = rows affected by second statement
    // results[2] = rows affected by third statement

    for (int i = 0; i < results.length; i++) {
        System.out.println("Statement " + (i + 1) + ": " + results[i] + " row(s) affected");
    }

    conn.commit();
}
// Output:
// Statement 1: 1 row(s) affected
// Statement 2: 1 row(s) affected
// Statement 3: 3 row(s) affected

<!-- Maven -->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.1.0</version>
</dependency>

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class ConnectionPoolExample {

    private static HikariDataSource dataSource;

    // Initialize the pool once at application startup
    public static void initPool() {
        HikariConfig config = new HikariConfig();

        // Required settings
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
        config.setUsername("root");
        config.setPassword("secret");

        // Pool sizing
        config.setMaximumPoolSize(10);    // Max connections in pool
        config.setMinimumIdle(5);         // Min idle connections maintained
        config.setIdleTimeout(300000);    // 5 min -- close idle connections after this
        config.setMaxLifetime(1800000);   // 30 min -- max lifetime of a connection

        // Connection validation
        config.setConnectionTimeout(30000);  // 30 sec -- wait for connection from pool
        config.setConnectionTestQuery("SELECT 1");  // Validate connection health

        // Performance settings
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

        dataSource = new HikariDataSource(config);
        System.out.println("Connection pool initialized.");
    }

    // Get a connection from the pool
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    // Shutdown the pool at application exit
    public static void closePool() {
        if (dataSource != null && !dataSource.isClosed()) {
            dataSource.close();
            System.out.println("Connection pool closed.");
        }
    }

    public static void main(String[] args) {
        initPool();

        // Use connections from the pool
        try (Connection conn = getConnection()) {
            // Connection is borrowed from the pool
            System.out.println("Got connection from pool: " + conn.isValid(2));
            // ... do work ...
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // Connection is returned to the pool here, NOT closed

        closePool();
    }
}
// Output:
// Connection pool initialized.
// Got connection from pool: true
// Connection pool closed.

-- MySQL stored procedure: Get user by ID
DELIMITER //
CREATE PROCEDURE get_user_by_id(IN user_id INT)
BEGIN
    SELECT id, username, email, salary FROM users WHERE id = user_id;
END //
DELIMITER ;

-- MySQL stored procedure with OUT parameter
DELIMITER //
CREATE PROCEDURE count_active_users(OUT user_count INT)
BEGIN
    SELECT COUNT(*) INTO user_count FROM users WHERE active = true;
END //
DELIMITER ;

-- MySQL stored procedure with IN and OUT
DELIMITER //
CREATE PROCEDURE calculate_bonus(
    IN employee_id INT,
    IN bonus_percentage DECIMAL(5,2),
    OUT bonus_amount DECIMAL(10,2)
)
BEGIN
    SELECT salary * (bonus_percentage / 100) INTO bonus_amount
    FROM users WHERE id = employee_id;
END //
DELIMITER ;

import java.sql.*;

public class StoredProcedureExample {

    // Call procedure with IN parameter that returns a ResultSet
    public static void getUserById(Connection conn, int userId) throws SQLException {
        String sql = "{CALL get_user_by_id(?)}";

        try (CallableStatement cstmt = conn.prepareCall(sql)) {
            cstmt.setInt(1, userId);

            try (ResultSet rs = cstmt.executeQuery()) {
                if (rs.next()) {
                    System.out.printf("User: %s (%s) - $%.2f%n",
                            rs.getString("username"),
                            rs.getString("email"),
                            rs.getDouble("salary"));
                }
            }
        }
    }

    // Call procedure with OUT parameter
    public static int countActiveUsers(Connection conn) throws SQLException {
        String sql = "{CALL count_active_users(?)}";

        try (CallableStatement cstmt = conn.prepareCall(sql)) {
            cstmt.registerOutParameter(1, Types.INTEGER);  // Register OUT param
            cstmt.execute();

            int count = cstmt.getInt(1);  // Get the OUT value
            System.out.println("Active users: " + count);
            return count;
        }
    }

    // Call procedure with IN and OUT parameters
    public static double calculateBonus(Connection conn, int employeeId,
                                         double percentage) throws SQLException {
        String sql = "{CALL calculate_bonus(?, ?, ?)}";

        try (CallableStatement cstmt = conn.prepareCall(sql)) {
            cstmt.setInt(1, employeeId);                      // IN
            cstmt.setDouble(2, percentage);                    // IN
            cstmt.registerOutParameter(3, Types.DECIMAL);      // OUT
            cstmt.execute();

            double bonus = cstmt.getDouble(3);
            System.out.printf("Bonus for employee %d at %.1f%%: $%.2f%n",
                    employeeId, percentage, bonus);
            return bonus;
        }
    }
}
// Output:
// User: john_doe (john@example.com) - $75000.00
// Active users: 3
// Bonus for employee 1 at 10.0%: $7500.00

// WRONG -- SQL injection vulnerability
String sql = "SELECT * FROM users WHERE username = '" + userInput + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);

// RIGHT -- parameterized query
String sql = "SELECT * FROM users WHERE username = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, userInput);
ResultSet rs = pstmt.executeQuery();

// WRONG -- resources never closed if exception occurs
Connection conn = DriverManager.getConnection(url, user, password);
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
// if exception here, conn, pstmt, and rs are leaked
rs.close();
pstmt.close();
conn.close();

// RIGHT -- try-with-resources guarantees cleanup
try (Connection conn = DriverManager.getConnection(url, user, password);
     PreparedStatement pstmt = conn.prepareStatement(sql);
     ResultSet rs = pstmt.executeQuery()) {
    // Work with rs...
} // All three auto-closed, even on exception

// WRONG -- auto-commit means each statement is independent
pstmt1.executeUpdate();  // Debits account A -- committed immediately
pstmt2.executeUpdate();  // Credits account B -- FAILS!
// Account A was debited but account B was never credited. Money vanished.

// RIGHT -- wrap related operations in a transaction
conn.setAutoCommit(false);
try {
    pstmt1.executeUpdate();  // Debits account A -- NOT committed yet
    pstmt2.executeUpdate();  // Credits account B -- NOT committed yet
    conn.commit();           // Both committed together
} catch (SQLException e) {
    conn.rollback();         // Both rolled back together
}

// WRONG -- N+1 problem: 1 query to get orders + N queries to get users
ResultSet orders = stmt.executeQuery("SELECT * FROM orders");  // 1 query
while (orders.next()) {
    int userId = orders.getInt("user_id");
    // This runs once per order -- if 1000 orders, that's 1000 extra queries!
    PreparedStatement userStmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
    userStmt.setInt(1, userId);
    ResultSet user = userStmt.executeQuery();
    // ...
}

// RIGHT -- use a JOIN to get all data in one query
String sql = """
    SELECT o.id AS order_id, o.total, u.username, u.email
    FROM orders o
    JOIN users u ON o.user_id = u.id
    """;
ResultSet rs = stmt.executeQuery(sql);  // 1 query for everything

// WRONG -- credentials in source code (committed to git!)
Connection conn = DriverManager.getConnection(
    "jdbc:mysql://prod-server:3306/mydb", "admin", "P@ssw0rd!");

// RIGHT -- use environment variables or config files
String url = System.getenv("DB_URL");
String user = System.getenv("DB_USER");
String password = System.getenv("DB_PASSWORD");
Connection conn = DriverManager.getConnection(url, user, password);

// WRONG -- loses type safety, requires casting
Object value = rs.getObject("salary");
double salary = (Double) value;  // ClassCastException if null or wrong type

// RIGHT -- use typed getters
double salary = rs.getDouble("salary");
if (rs.wasNull()) {
    // Handle null case
}

// ALSO RIGHT -- use getObject with type parameter (Java 7+)
Double salary = rs.getObject("salary", Double.class);  // Returns null if SQL NULL

// Gold standard pattern for JDBC
try (Connection conn = dataSource.getConnection();
     PreparedStatement pstmt = conn.prepareStatement(sql)) {

    pstmt.setString(1, value);

    try (ResultSet rs = pstmt.executeQuery()) {
        while (rs.next()) {
            // Process row
        }
    }
}

public void doMultiStepWork(Connection conn) throws SQLException {
    conn.setAutoCommit(false);
    try {
        // Step 1...
        // Step 2...
        // Step 3...
        conn.commit();
    } catch (SQLException e) {
        conn.rollback();
        throw e;  // Re-throw so the caller knows it failed
    } finally {
        conn.setAutoCommit(true);
    }
}

try {
    // database operation
} catch (SQLException e) {
    // Log ALL the information -- you will need it for debugging
    logger.error("Database error - Message: {} | SQLState: {} | ErrorCode: {} | SQL: {}",
            e.getMessage(), e.getSQLState(), e.getErrorCode(), sql, e);
    throw e;  // Re-throw or wrap in a custom exception
}

// WRONG -- what does rs.getString(1) mean? Fragile if column order changes.
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM table1");
String x = rs.getString(1);

// RIGHT -- use aliases and access by name
String sql = """
    SELECT u.username AS user_name,
           o.total    AS order_total,
           o.created  AS order_date
    FROM users u
    JOIN orders o ON u.id = o.user_id
    """;
ResultSet rs = stmt.executeQuery(sql);
String name = rs.getString("user_name");
double total = rs.getDouble("order_total");

import java.time.LocalDateTime;

public class User {
    private int id;
    private String username;
    private String email;
    private int age;
    private double salary;
    private boolean active;
    private LocalDateTime createdAt;

    // No-arg constructor (needed for DAO mapping)
    public User() {}

    // Constructor for creating new users (no ID yet)
    public User(String username, String email, int age, double salary) {
        this.username = username;
        this.email = email;
        this.age = age;
        this.salary = salary;
        this.active = true;
    }

    // Getters and setters
    public int getId() { return id; }
    public void setId(int id) { this.id = id; }
    public String getUsername() { return username; }
    public void setUsername(String username) { this.username = username; }
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    public int getAge() { return age; }
    public void setAge(int age) { this.age = age; }
    public double getSalary() { return salary; }
    public void setSalary(double salary) { this.salary = salary; }
    public boolean isActive() { return active; }
    public void setActive(boolean active) { this.active = active; }
    public LocalDateTime getCreatedAt() { return createdAt; }
    public void setCreatedAt(LocalDateTime createdAt) { this.createdAt = createdAt; }

    @Override
    public String toString() {
        return String.format("User{id=%d, username='%s', email='%s', age=%d, salary=%.2f, active=%b}",
                id, username, email, age, salary, active);
    }
}

import java.util.List;
import java.util.Optional;

public interface UserDAO {
    // Create
    User save(User user);

    // Read
    Optional<User> findById(int id);
    Optional<User> findByUsername(String username);
    List<User> findAll();
    List<User> findByMinSalary(double minSalary);

    // Update
    boolean update(User user);

    // Delete
    boolean deleteById(int id);

    // Count
    long count();
}

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

public class UserDAOImpl implements UserDAO {

    private final DataSource dataSource;

    public UserDAOImpl(DataSource dataSource) {
        this.dataSource = dataSource;  // Injected -- no hardcoded connection info
    }

    @Override
    public User save(User user) {
        String sql = "INSERT INTO users (username, email, age, salary, active) VALUES (?, ?, ?, ?, ?)";

        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getEmail());
            pstmt.setInt(3, user.getAge());
            pstmt.setDouble(4, user.getSalary());
            pstmt.setBoolean(5, user.isActive());

            pstmt.executeUpdate();

            try (ResultSet keys = pstmt.getGeneratedKeys()) {
                if (keys.next()) {
                    user.setId(keys.getInt(1));
                }
            }
            return user;

        } catch (SQLException e) {
            throw new RuntimeException("Failed to save user: " + user.getUsername(), e);
        }
    }

    @Override
    public Optional<User> findById(int id) {
        String sql = "SELECT * FROM users WHERE id = ?";

        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapRow(rs));
                }
            }
            return Optional.empty();

        } catch (SQLException e) {
            throw new RuntimeException("Failed to find user by ID: " + id, e);
        }
    }

    @Override
    public Optional<User> findByUsername(String username) {
        String sql = "SELECT * FROM users WHERE username = ?";

        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, username);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapRow(rs));
                }
            }
            return Optional.empty();

        } catch (SQLException e) {
            throw new RuntimeException("Failed to find user by username: " + username, e);
        }
    }

    @Override
    public List<User> findAll() {
        String sql = "SELECT * FROM users ORDER BY id";
        List<User> users = new ArrayList<>();

        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                users.add(mapRow(rs));
            }
            return users;

        } catch (SQLException e) {
            throw new RuntimeException("Failed to find all users", e);
        }
    }

    @Override
    public List<User> findByMinSalary(double minSalary) {
        String sql = "SELECT * FROM users WHERE salary >= ? ORDER BY salary DESC";
        List<User> users = new ArrayList<>();

        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setDouble(1, minSalary);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    users.add(mapRow(rs));
                }
            }
            return users;

        } catch (SQLException e) {
            throw new RuntimeException("Failed to find users by salary", e);
        }
    }

    @Override
    public boolean update(User user) {
        String sql = "UPDATE users SET username = ?, email = ?, age = ?, salary = ?, active = ? WHERE id = ?";

        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getEmail());
            pstmt.setInt(3, user.getAge());
            pstmt.setDouble(4, user.getSalary());
            pstmt.setBoolean(5, user.isActive());
            pstmt.setInt(6, user.getId());

            return pstmt.executeUpdate() > 0;

        } catch (SQLException e) {
            throw new RuntimeException("Failed to update user: " + user.getId(), e);
        }
    }

    @Override
    public boolean deleteById(int id) {
        String sql = "DELETE FROM users WHERE id = ?";

        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);
            return pstmt.executeUpdate() > 0;

        } catch (SQLException e) {
            throw new RuntimeException("Failed to delete user: " + id, e);
        }
    }

    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM users";

        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            rs.next();
            return rs.getLong(1);

        } catch (SQLException e) {
            throw new RuntimeException("Failed to count users", e);
        }
    }

    // Private helper -- maps a ResultSet row to a User object
    private User mapRow(ResultSet rs) throws SQLException {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setUsername(rs.getString("username"));
        user.setEmail(rs.getString("email"));
        user.setAge(rs.getInt("age"));
        user.setSalary(rs.getDouble("salary"));
        user.setActive(rs.getBoolean("active"));

        Timestamp ts = rs.getTimestamp("created_at");
        if (ts != null) {
            user.setCreatedAt(ts.toLocalDateTime());
        }
        return user;
    }
}

public class UserService {

    private final UserDAO userDAO;

    public UserService(UserDAO userDAO) {
        this.userDAO = userDAO;  // Dependency injection
    }

    public User registerUser(String username, String email, int age, double salary) {
        // Business logic validation -- no SQL here
        if (username == null || username.isBlank()) {
            throw new IllegalArgumentException("Username cannot be empty");
        }
        if (userDAO.findByUsername(username).isPresent()) {
            throw new IllegalArgumentException("Username already taken: " + username);
        }

        User user = new User(username, email, age, salary);
        return userDAO.save(user);
    }

    public User getUserOrThrow(int id) {
        return userDAO.findById(id)
                .orElseThrow(() -> new RuntimeException("User not found: " + id));
    }

    public List<User> getHighEarners(double threshold) {
        return userDAO.findByMinSalary(threshold);
    }
}

// Usage:
// UserDAO dao = new UserDAOImpl(dataSource);
// UserService service = new UserService(dao);
// User user = service.registerUser("john_doe", "john@example.com", 28, 75000);

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;

public class Employee {
    private int id;
    private String firstName;
    private String lastName;
    private String email;
    private String department;
    private BigDecimal salary;
    private LocalDate hireDate;
    private boolean active;
    private LocalDateTime createdAt;

    public Employee() {}

    public Employee(String firstName, String lastName, String email,
                    String department, BigDecimal salary, LocalDate hireDate) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.email = email;
        this.department = department;
        this.salary = salary;
        this.hireDate = hireDate;
        this.active = true;
    }

    // Getters and setters
    public int getId() { return id; }
    public void setId(int id) { this.id = id; }
    public String getFirstName() { return firstName; }
    public void setFirstName(String firstName) { this.firstName = firstName; }
    public String getLastName() { return lastName; }
    public void setLastName(String lastName) { this.lastName = lastName; }
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    public String getDepartment() { return department; }
    public void setDepartment(String department) { this.department = department; }
    public BigDecimal getSalary() { return salary; }
    public void setSalary(BigDecimal salary) { this.salary = salary; }
    public LocalDate getHireDate() { return hireDate; }
    public void setHireDate(LocalDate hireDate) { this.hireDate = hireDate; }
    public boolean isActive() { return active; }
    public void setActive(boolean active) { this.active = active; }
    public LocalDateTime getCreatedAt() { return createdAt; }
    public void setCreatedAt(LocalDateTime createdAt) { this.createdAt = createdAt; }

    public String getFullName() {
        return firstName + " " + lastName;
    }

    @Override
    public String toString() {
        return String.format("Employee{id=%d, name='%s %s', dept='%s', salary=%s, active=%b}",
                id, firstName, lastName, department, salary, active);
    }
}

import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;

public interface EmployeeDAO {

    // Schema management
    void createTable();

    // CRUD
    Employee save(Employee employee);
    Optional<Employee> findById(int id);
    List<Employee> findAll();
    List<Employee> findByDepartment(String department);
    boolean update(Employee employee);
    boolean deleteById(int id);

    // Batch
    int[] saveBatch(List<Employee> employees);

    // Transaction example
    boolean transferDepartment(int employeeId, String newDepartment, BigDecimal salaryAdjustment);

    // Aggregation
    long count();
    BigDecimal getAverageSalaryByDepartment(String department);
}

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

public class EmployeeDAOImpl implements EmployeeDAO {

    private final DataSource dataSource;

    public EmployeeDAOImpl(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public void createTable() {
        String sql = """
            CREATE TABLE IF NOT EXISTS employees (
                id          INT AUTO_INCREMENT PRIMARY KEY,
                first_name  VARCHAR(50) NOT NULL,
                last_name   VARCHAR(50) NOT NULL,
                email       VARCHAR(100) NOT NULL UNIQUE,
                department  VARCHAR(50) NOT NULL,
                salary      DECIMAL(10,2) NOT NULL,
                hire_date   DATE NOT NULL,
                active      BOOLEAN DEFAULT TRUE,
                created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            """;

        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            throw new RuntimeException("Failed to create employees table", e);
        }
    }

    @Override
    public Employee save(Employee emp) {
        String sql = """
            INSERT INTO employees (first_name, last_name, email, department, salary, hire_date, active)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            """;

        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            setEmployeeParams(pstmt, emp);
            pstmt.executeUpdate();

            try (ResultSet keys = pstmt.getGeneratedKeys()) {
                if (keys.next()) {
                    emp.setId(keys.getInt(1));
                }
            }
            return emp;

        } catch (SQLException e) {
            throw new RuntimeException("Failed to save employee: " + emp.getEmail(), e);
        }
    }

    @Override
    public Optional<Employee> findById(int id) {
        String sql = "SELECT * FROM employees WHERE id = ?";

        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);
            try (ResultSet rs = pstmt.executeQuery()) {
                return rs.next() ? Optional.of(mapRow(rs)) : Optional.empty();
            }

        } catch (SQLException e) {
            throw new RuntimeException("Failed to find employee: " + id, e);
        }
    }

    @Override
    public List<Employee> findAll() {
        String sql = "SELECT * FROM employees ORDER BY last_name, first_name";
        return executeQuery(sql);
    }

    @Override
    public List<Employee> findByDepartment(String department) {
        String sql = "SELECT * FROM employees WHERE department = ? AND active = true ORDER BY last_name";
        List<Employee> employees = new ArrayList<>();

        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, department);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    employees.add(mapRow(rs));
                }
            }
            return employees;

        } catch (SQLException e) {
            throw new RuntimeException("Failed to find employees in department: " + department, e);
        }
    }

    @Override
    public boolean update(Employee emp) {
        String sql = """
            UPDATE employees
            SET first_name = ?, last_name = ?, email = ?, department = ?,
                salary = ?, hire_date = ?, active = ?
            WHERE id = ?
            """;

        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            setEmployeeParams(pstmt, emp);
            pstmt.setInt(8, emp.getId());
            return pstmt.executeUpdate() > 0;

        } catch (SQLException e) {
            throw new RuntimeException("Failed to update employee: " + emp.getId(), e);
        }
    }

    @Override
    public boolean deleteById(int id) {
        String sql = "DELETE FROM employees WHERE id = ?";

        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);
            return pstmt.executeUpdate() > 0;

        } catch (SQLException e) {
            throw new RuntimeException("Failed to delete employee: " + id, e);
        }
    }

    @Override
    public int[] saveBatch(List<Employee> employees) {
        String sql = """
            INSERT INTO employees (first_name, last_name, email, department, salary, hire_date, active)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            """;

        try (Connection conn = dataSource.getConnection()) {
            conn.setAutoCommit(false);

            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                for (Employee emp : employees) {
                    setEmployeeParams(pstmt, emp);
                    pstmt.addBatch();
                }

                int[] results = pstmt.executeBatch();
                conn.commit();
                return results;

            } catch (SQLException e) {
                conn.rollback();
                throw e;
            } finally {
                conn.setAutoCommit(true);
            }

        } catch (SQLException e) {
            throw new RuntimeException("Batch insert failed", e);
        }
    }

    @Override
    public boolean transferDepartment(int employeeId, String newDepartment,
                                       BigDecimal salaryAdjustment) {
        String updateEmpSql = "UPDATE employees SET department = ?, salary = salary + ? WHERE id = ?";
        String auditSql = """
            INSERT INTO department_transfers (employee_id, new_department, salary_change, transfer_date)
            VALUES (?, ?, ?, CURRENT_DATE)
            """;

        try (Connection conn = dataSource.getConnection()) {
            conn.setAutoCommit(false);

            try {
                // Step 1: Update employee's department and salary
                try (PreparedStatement pstmt = conn.prepareStatement(updateEmpSql)) {
                    pstmt.setString(1, newDepartment);
                    pstmt.setBigDecimal(2, salaryAdjustment);
                    pstmt.setInt(3, employeeId);

                    int rows = pstmt.executeUpdate();
                    if (rows == 0) {
                        throw new SQLException("Employee not found: " + employeeId);
                    }
                }

                // Step 2: Record the transfer in audit table
                // (Create audit table if it does not exist)
                try (Statement stmt = conn.createStatement()) {
                    stmt.executeUpdate("""
                        CREATE TABLE IF NOT EXISTS department_transfers (
                            id INT AUTO_INCREMENT PRIMARY KEY,
                            employee_id INT NOT NULL,
                            new_department VARCHAR(50) NOT NULL,
                            salary_change DECIMAL(10,2),
                            transfer_date DATE NOT NULL
                        )
                        """);
                }

                try (PreparedStatement pstmt = conn.prepareStatement(auditSql)) {
                    pstmt.setInt(1, employeeId);
                    pstmt.setString(2, newDepartment);
                    pstmt.setBigDecimal(3, salaryAdjustment);
                    pstmt.executeUpdate();
                }

                conn.commit();
                return true;

            } catch (SQLException e) {
                conn.rollback();
                throw e;
            } finally {
                conn.setAutoCommit(true);
            }

        } catch (SQLException e) {
            throw new RuntimeException("Department transfer failed for employee: " + employeeId, e);
        }
    }

    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM employees";

        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            rs.next();
            return rs.getLong(1);

        } catch (SQLException e) {
            throw new RuntimeException("Failed to count employees", e);
        }
    }

    @Override
    public BigDecimal getAverageSalaryByDepartment(String department) {
        String sql = "SELECT AVG(salary) FROM employees WHERE department = ? AND active = true";

        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, department);
            try (ResultSet rs = pstmt.executeQuery()) {
                rs.next();
                BigDecimal avg = rs.getBigDecimal(1);
                return avg != null ? avg : BigDecimal.ZERO;
            }

        } catch (SQLException e) {
            throw new RuntimeException("Failed to get average salary for: " + department, e);
        }
    }

    // ---------- Private Helpers ----------

    private void setEmployeeParams(PreparedStatement pstmt, Employee emp) throws SQLException {
        pstmt.setString(1, emp.getFirstName());
        pstmt.setString(2, emp.getLastName());
        pstmt.setString(3, emp.getEmail());
        pstmt.setString(4, emp.getDepartment());
        pstmt.setBigDecimal(5, emp.getSalary());
        pstmt.setDate(6, Date.valueOf(emp.getHireDate()));
        pstmt.setBoolean(7, emp.isActive());
    }

    private Employee mapRow(ResultSet rs) throws SQLException {
        Employee emp = new Employee();
        emp.setId(rs.getInt("id"));
        emp.setFirstName(rs.getString("first_name"));
        emp.setLastName(rs.getString("last_name"));
        emp.setEmail(rs.getString("email"));
        emp.setDepartment(rs.getString("department"));
        emp.setSalary(rs.getBigDecimal("salary"));
        emp.setHireDate(rs.getDate("hire_date").toLocalDate());
        emp.setActive(rs.getBoolean("active"));
        Timestamp ts = rs.getTimestamp("created_at");
        if (ts != null) {
            emp.setCreatedAt(ts.toLocalDateTime());
        }
        return emp;
    }

    private List<Employee> executeQuery(String sql) {
        List<Employee> employees = new ArrayList<>();
        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                employees.add(mapRow(rs));
            }
            return employees;

        } catch (SQLException e) {
            throw new RuntimeException("Query failed: " + sql, e);
        }
    }
}