How would you build a database connection pool from scratch, and how does HikariCP improve on it?

package com.bank.pool;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.BlockingQueue;
import java.util.concurrent.TimeUnit;

public class SimpleConnectionPool {

    private final BlockingQueue<Connection> pool;
    private final int size;

    public SimpleConnectionPool(String url, String user, String password, int size)
            throws SQLException {
        this.size = size;
        this.pool = new ArrayBlockingQueue<>(size);
        for (int i = 0; i < size; i++) {
            pool.offer(DriverManager.getConnection(url, user, password));
        }
    }

    public Connection borrow(long timeoutMs) throws InterruptedException {
        Connection conn = pool.poll(timeoutMs, TimeUnit.MILLISECONDS);
        if (conn == null) {
            throw new RuntimeException(
                    "Connection not available, timed out after " + timeoutMs + " ms");
        }
        return conn;
    }

    public void release(Connection conn) {
        if (conn != null) {
            pool.offer(conn);  // Returns connection to the queue
        }
    }

    public int getAvailableCount() {
        return pool.size();
    }
}

package com.bank.service;

import com.bank.pool.SimpleConnectionPool;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BalanceService {

    private final SimpleConnectionPool pool;

    public BalanceService(SimpleConnectionPool pool) {
        this.pool = pool;
    }

    public BigDecimal getBalance(String accountId) throws Exception {
        Connection conn = pool.borrow(3000);  // Wait max 3 seconds
        try {
            String sql = "SELECT balance FROM accounts WHERE account_id = ?";
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                ps.setString(1, accountId);
                try (ResultSet rs = ps.executeQuery()) {
                    return rs.next() ? rs.getBigDecimal("balance") : BigDecimal.ZERO;
                }
            }
        } finally {
            pool.release(conn);  // Always return, even on exception
        }
    }
}

javac -d out com/bank/pool/SimpleConnectionPool.java com/bank/service/BalanceService.java
java -cp out com.bank.service.BalanceService

package com.bank.pool;

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

public class NaivePoolWeaknesses {

    // Weakness 1: Lock contention
    // LinkedBlockingQueue uses internal locks
    // Under 100 concurrent threads, many contend on the same lock
    // Throughput drops as thread count increases

    // Weakness 2: No validation
    // If the database dropped a connection (timeout, restart),
    // the pool hands out a dead connection
    public void demonstrateDeadConnection(Connection conn) throws SQLException {
        // This query fails because the connection was dropped
        conn.prepareStatement("SELECT 1").executeQuery();
        // PSQLException: This connection has been closed
    }

    // Weakness 3: No lifetime recycling
    // A connection pinned for days accumulates stale server-side state
    // After a database failover, old connections point to the wrong server

    // Weakness 4: No leak detection
    // If a developer forgets pool.release(conn), the connection is lost
    // The pool silently shrinks until it is exhausted
    // No warning, no stack trace to find the offending code
}

package com.bank.config;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;

public class HikariPoolConfig {

    public DataSource createPool() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/nbi_ledger");
        config.setUsername("nbi_app");
        config.setPassword("secret");
        config.setMaximumPoolSize(10);

        // Connection validation: lightweight isValid() call
        // Dead connections are discarded and replaced before reaching code
        config.setConnectionTestQuery("SELECT 1"); // Fallback if isValid not supported

        // Lifetime recycling: retire after 30 minutes
        config.setMaxLifetime(1800000);

        // Leak detection: log stack trace if held > 5 seconds
        config.setLeakDetectionThreshold(5000);

        return new HikariDataSource(config);
    }
}

package com.bank.pool;

// HikariCP validation flow (conceptual)
public class ValidationFlow {

    // Step 1: Thread calls dataSource.getConnection()
    // Step 2: ConcurrentBag picks a connection (thread-local first)
    // Step 3: HikariCP calls connection.isValid(timeout)
    //         or runs the connectionTestQuery
    // Step 4a: If valid → hand it to the caller
    // Step 4b: If invalid → discard, create new, validate, hand out

    // With the naive BlockingQueue pool:
    // Step 1: Thread calls pool.borrow()
    // Step 2: Queue returns a connection
    // Step 3: NO VALIDATION
    // Step 4: If the connection is dead, the query fails
    //         The developer sees: PSQLException: This connection has been closed
}

package com.bank.service;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

public class LeakyLoanService {

    private final DataSource dataSource;

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

    // BUG: connection is never closed on the exception path
    public BigDecimal getLoanBalance(String loanId) throws SQLException {
        Connection conn = dataSource.getConnection();
        PreparedStatement ps = conn.prepareStatement(
                "SELECT outstanding FROM loans WHERE loan_id = ?");
        ps.setString(1, loanId);
        ResultSet rs = ps.executeQuery();
        // If an exception occurs here, conn is never returned!
        BigDecimal balance = rs.next() ? rs.getBigDecimal(1) : BigDecimal.ZERO;
        conn.close();
        return balance;
    }
    // HikariCP logs after 5 seconds:
    // WARN  Apparent connection leak detected (held for 5001ms)
    // java.lang.Exception: Apparent connection leak
    //   at com.bank.service.LeakyLoanService.getLoanBalance(LeakyLoanService.java:15)
}

Naive pool vs HikariCP comparison:

                     Naive BlockingQueue    HikariCP
Borrow mechanism     Lock-based take()      Lock-free ConcurrentBag
Release mechanism    Lock-based offer()     Thread-local return
Validation           None                   isValid() + test query
Lifetime recycling   None                   maxLifetime eviction
Leak detection       None                   Stack trace logging
Dead connection      Handed out, fails      Discarded, replaced
Thread affinity      None                   Thread-local preference