JDBC & the DAO Pattern

Connection Management & Pooling

18 min Lesson 3 of 13

Connection Management & Pooling

In a web application, database connections are a shared, finite resource. Every HTTP request that needs to hit the database must borrow a connection, do its work, and return the connection promptly. Get this wrong — either by leaking connections or by opening too many — and your application will grind to a halt under moderate load. This lesson shows you the professional patterns that prevent both failure modes.

Why Opening a Fresh Connection Every Time Is a Problem

Establishing a TCP connection to a database server involves a DNS lookup, a TCP three-way handshake, driver authentication, and session setup on the server side. On a LAN this commonly takes 20–100 ms; over a cloud network it can easily exceed 200 ms. For a web endpoint that must respond in under 500 ms, spending a third of its budget on connection setup is unacceptable.

Beyond latency, database servers enforce a hard limit on simultaneous sessions (MySQL defaults to 151; PostgreSQL to 100). A DriverManager-based application that opens a connection per request will saturate that limit under modest concurrency and throw SQLNonTransientConnectionException at everyone else.

The root problem: Connection creation is expensive and connections are scarce. A connection pool amortises the creation cost by reusing already-open connections and bounds the total count so you never exceed the server limit.

The DataSource Interface

javax.sql.DataSource is the standard JDBC abstraction for a connection factory. Its contract is simple:

public interface DataSource extends CommonDataSource {
    Connection getConnection() throws SQLException;
    Connection getConnection(String username, String password) throws SQLException;
}

All pooling libraries (HikariCP, Apache DBCP2, c3p0, Tomcat JDBC Pool) implement this interface. Your application code only ever calls dataSource.getConnection() — it never knows or cares whether the returned Connection came from a pool, a fresh socket, or a test stub. That indirection is what makes the design testable and portable.

HikariCP — the Industry Standard Pool

HikariCP has been the default pool in Spring Boot since version 2.0 and is widely regarded as the fastest, most reliable JDBC pool available. Add it to your Maven project:

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

Create a singleton DataSource during application startup — in a Servlet-based application, a ServletContextListener is the right hook:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import jakarta.servlet.ServletContextEvent;
import jakarta.servlet.ServletContextListener;
import jakarta.servlet.annotation.WebListener;
import javax.sql.DataSource;

@WebListener
public class AppBootstrap implements ServletContextListener {

    @Override
    public void contextInitialized(ServletContextEvent sce) {
        HikariConfig cfg = new HikariConfig();
        cfg.setJdbcUrl(System.getenv("DB_URL"));          // e.g. jdbc:postgresql://db:5432/shop
        cfg.setUsername(System.getenv("DB_USER"));
        cfg.setPassword(System.getenv("DB_PASS"));

        // Pool sizing — see discussion below
        cfg.setMaximumPoolSize(10);
        cfg.setMinimumIdle(2);

        // Timeout tuning
        cfg.setConnectionTimeout(30_000);    // max ms to wait for a connection from the pool
        cfg.setIdleTimeout(600_000);         // idle connection eviction after 10 min
        cfg.setMaxLifetime(1_800_000);       // hard 30-min cap (must be < DB wait_timeout)

        // Validation
        cfg.setConnectionTestQuery("SELECT 1");   // omit for drivers that support isValid()

        HikariDataSource ds = new HikariDataSource(cfg);
        sce.getServletContext().setAttribute("dataSource", ds);
    }

    @Override
    public void contextDestroyed(ServletContextEvent sce) {
        HikariDataSource ds = (HikariDataSource)
            sce.getServletContext().getAttribute("dataSource");
        if (ds != null) ds.close();    // drains pool gracefully on undeploy
    }
}

Any servlet or DAO can then retrieve the shared DataSource from the ServletContext:

DataSource ds = (DataSource) getServletContext().getAttribute("dataSource");
try (Connection conn = ds.getConnection()) {
    // execute your SQL
}
// conn.close() returns the connection to the pool — the TCP socket stays open

Always use try-with-resources for connections, statements, and result sets. When you call conn.close() on a pooled connection, HikariCP does not close the underlying socket — it resets session state and returns the logical handle to the pool. Forgetting to call it is just as harmful as a real leak: the pool slot is reserved forever and the application eventually deadlocks waiting for a connection that never comes back.

Sizing the Pool Correctly

A common mistake is to set maximumPoolSize very high, reasoning that "more connections = more throughput". The opposite is usually true once you exceed the database server's optimal concurrency. The HikariCP team and PostgreSQL documentation both recommend starting with:

// Empirical formula (PostgreSQL wiki / HikariCP docs):
// pool_size = (number of CPU cores on the DB server) * 2 + number of effective spindle disks
// A 4-core DB server with SSDs:  4 * 2 + 1 = 9  →  round to 10
cfg.setMaximumPoolSize(10);
cfg.setMinimumIdle(2);     // keep 2 warm when the app is quiet; avoid cold-start penalty

In a microservice world where multiple application instances share one database, the total connection count across all instances must remain within the server's max_connections. Ten instances each with a pool of 10 = 100 connections — right at the PostgreSQL default ceiling. Factor this in before bumping pool sizes.

JNDI — Container-Managed DataSources

In an application-server environment (WildFly, GlassFish, Payara, Tomcat in enterprise mode) you often don't create the pool in code at all. Instead the server administrator configures a pool in the server's management console and registers it under a JNDI (Java Naming and Directory Interface) name. Your application looks it up:

import javax.naming.InitialContext;
import javax.sql.DataSource;

// Inside a servlet or EJB:
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/ShopDB");
try (Connection conn = ds.getConnection()) {
    // use normally
}

The JNDI resource reference must also be declared in web.xml (or @Resource injection in a Jakarta EE component):

<!-- web.xml -->
<resource-ref>
    <res-ref-name>jdbc/ShopDB</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

With CDI or EJB you can inject the DataSource directly, which is even cleaner:

import jakarta.annotation.Resource;
import jakarta.enterprise.context.RequestScoped;
import javax.sql.DataSource;

@RequestScoped
public class OrderRepository {

    @Resource(lookup = "java:comp/env/jdbc/ShopDB")
    private DataSource dataSource;

    public void save(Order order) throws SQLException {
        try (Connection conn = dataSource.getConnection()) {
            // ...
        }
    }
}

JNDI vs embedded pool: JNDI lookup is the enterprise-standard approach; it lets ops teams change credentials and pool parameters without touching your WAR. HikariCP configured in application code is the pragmatic choice for standalone JARs (Spring Boot fat JARs, microservices). For new projects without a mandate, HikariCP in code is simpler to set up and test.

What Happens Inside the Pool

Understanding the lifecycle helps you debug problems:

Borrow: Your code calls ds.getConnection(). HikariCP picks an idle connection from the pool. If none is available and the pool is below maximumPoolSize, it opens a new one. If the pool is full, it waits up to connectionTimeout milliseconds then throws SQLTransientConnectionException.
Use: You execute SQL on the borrowed connection. If an exception causes you to skip close(), the pool's housekeeping thread will eventually detect the leak (if leakDetectionThreshold is configured) and log a warning.
Return: conn.close() resets autoCommit, clears warnings, rolls back any uncommitted transaction, and marks the connection available.
Validation: Before handing a connection to the next caller, HikariCP runs a fast validation (isValid() or your connectionTestQuery) to detect connections the database server closed due to wait_timeout or a network hiccup.
Eviction: Connections idle longer than idleTimeout, or alive longer than maxLifetime, are quietly closed and replaced — keeping the pool fresh.

Never commit and then forget to close. If you set autoCommit=false and return a connection to the pool without calling commit() or rollback(), HikariCP will roll the transaction back on return — but the next borrower may see inconsistent state in row-level locks held until that rollback, causing mysterious timeouts. Use try-with-resources or a finally block that always calls rollback() on error and commit() on success.

Enabling HikariCP Metrics (Optional but Useful)

HikariCP exposes pool statistics via JMX and Micrometer out of the box. In a Spring Boot application you get pool metrics in Actuator (/actuator/metrics/hikaricp.connections) for free. In a standalone Servlet app you can poll programmatically:

HikariDataSource hds = (HikariDataSource) ds;
HikariPoolMXBean pool = hds.getHikariPoolMXBean();

int active  = pool.getActiveConnections();    // currently borrowed
int idle    = pool.getIdleConnections();      // waiting in pool
int waiting = pool.getThreadsAwaitingConnection(); // threads blocked on borrow
int total   = pool.getTotalConnections();     // active + idle

Watch threadsAwaitingConnection in production. Sustained values above zero mean your pool is too small or your queries are running too long — both are actionable signals.

Summary

Connection pooling via DataSource is not an optimisation you add later — it is a correctness requirement for any web application. Use HikariCP (or a JNDI-managed pool in a full application server), size the pool empirically, always return connections promptly with try-with-resources, and monitor pool metrics so you catch saturation before it becomes an outage. In the next lesson you will put a pooled DataSource to work executing real CRUD statements with PreparedStatement.