Connection Pooling

// ── Without pooling: every call pays full connection-establishment cost ────
import java.sql.Connection;
import java.sql.DriverManager;

public void handleRequestNoPooling() throws Exception {
    long start = System.nanoTime();
    try (Connection conn = DriverManager.getConnection(
            "jdbc:postgresql://localhost:5432/storedb", "app_user", "secret")) {
        // TCP handshake + auth handshake + session setup happen HERE, every time
        // ... use conn ...
    }
    long elapsed = System.nanoTime() - start;
    // elapsed includes the full connection setup cost on top of actual query time
}

// ── With pooling: connection setup cost is paid once per physical connection,
// not once per borrow ─────────────────────────────────────────────────────
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;

public class PooledService {
    private final DataSource pool;

    public PooledService() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/storedb");
        config.setUsername("app_user");
        config.setPassword("secret");
        this.pool = new HikariDataSource(config);  // opens its initial connections once, here
    }

    public void handleRequest() throws Exception {
        try (Connection conn = pool.getConnection()) {
            // Borrowing here is cheap — just handing over an already-open connection
            // ... use conn ...
        } // returned to the pool, not torn down
    }
}

// ── Application-server style: DataSource obtained via JNDI, configured externally ──
import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.sql.Connection;

public class JndiDataSourceExample {
    public void handleRequest() throws Exception {
        DataSource ds = (DataSource) new InitialContext().lookup("java:comp/env/jdbc/StoreDB");
        try (Connection conn = ds.getConnection()) {
            // host, port, credentials, and pool settings all live in server config,
            // not in this application code
        }
    }
}

// ── Self-contained application style: DataSource constructed directly ──────────
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;

public class SelfContainedDataSourceExample {
    public static DataSource createDataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/storedb");
        config.setUsername("app_user");
        config.setPassword("secret");
        return new HikariDataSource(config);
    }
}

// ── Spring Boot style: DataSource auto-configured from application.properties ──
// spring.datasource.url=jdbc:postgresql://localhost:5432/storedb
// spring.datasource.username=app_user
// spring.datasource.password=secret
// spring.datasource.hikari.maximum-pool-size=10
//
// Application code simply autowires the DataSource (or a higher-level
// abstraction like JdbcTemplate built on top of it) without constructing
// it manually at all.

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class PoolSizingAndLeakDetection {
    public static HikariDataSource createConfiguredPool() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/storedb");
        config.setUsername("app_user");
        config.setPassword("secret");

        // ── Sizing ───────────────────────────────────────────────────────
        config.setMaximumPoolSize(10);     // start modest; raise only if measured contention warrants it
        config.setMinimumIdle(2);          // keep a small number of warm connections ready

        // ── Validation / health checking ────────────────────────────────
        config.setConnectionTestQuery(null);  // HikariCP prefers isValid() over a test query when supported
        config.setValidationTimeout(3000);    // ms allowed for isValid() to confirm health
        config.setMaxLifetime(1800000);       // proactively retire connections after 30 min,
                                               // ahead of any server-side idle/lifetime limits

        // ── Leak detection ──────────────────────────────────────────────
        config.setLeakDetectionThreshold(60000);  // warn (with stack trace) if a connection
                                                   // is held longer than 60 seconds without being returned

        return new HikariDataSource(config);
    }

    // ── The leak this setting is designed to catch ──────────────────────
    public static void leakyMethod(HikariDataSource pool) throws Exception {
        java.sql.Connection conn = pool.getConnection();
        // ... work happens here ...
        // BUG: conn.close() is never called — no try-with-resources, no finally.
        // This connection is now permanently lost to the pool until the JVM
        // restarts or some other mechanism reclaims it.
    }

    // ── The fix ───────────────────────────────────────────────────────
    public static void correctMethod(HikariDataSource pool) throws Exception {
        try (java.sql.Connection conn = pool.getConnection()) {
            // ... work happens here ...
        }  // guaranteed to return the connection to the pool, even on exception
    }
}