JDBC & Databases

Connection Pooling & Resources

15 min Lesson 9 of 13

Connection Pooling & Resources

Every Connection object you create with DriverManager.getConnection() opens a physical TCP socket to the database server, authenticates, and allocates memory on both ends. That round-trip costs tens to hundreds of milliseconds. In a web application that receives hundreds of concurrent requests, creating a fresh connection for every query is the single fastest way to bring a database server to its knees.

Connection pooling solves this by keeping a cache of already-open connections that are handed out to callers and returned when the caller is done — without ever closing the underlying socket. The result is dramatically lower latency and far fewer resources consumed on the database server.

Why raw DriverManager does not scale

Consider the lifecycle of a connection opened with DriverManager:

Your code calls DriverManager.getConnection(url, user, pw).
The JDBC driver opens a TCP socket to the database.
The database authenticates the user (~10–100 ms).
Your code runs the query.
Your code calls connection.close(), tearing down the socket.

Steps 1–3 and 5 are pure overhead that dwarfs the actual query time for short queries. Under load, the database also runs out of available connection slots (PostgreSQL defaults to 100; many shared hosts allow far fewer).

Rule of thumb: in any application that handles more than a handful of concurrent users, always use a connection pool. Even a single-threaded background job benefits because pool creation overhead is paid once at startup, not once per query.

HikariCP — the de-facto standard pool

HikariCP is the fastest, most widely used JDBC connection pool for the JVM. Spring Boot uses it by default. Adding it to a Maven project is one dependency:

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

Creating and configuring a pool at application startup:

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

public class DataSourceFactory {

    public static DataSource createPool() {
        HikariConfig config = new HikariConfig();

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

        // Pool sizing
        config.setMaximumPoolSize(10);       // max open connections
        config.setMinimumIdle(2);            // connections kept idle/warm

        // Timeout tuning (milliseconds)
        config.setConnectionTimeout(3_000);  // how long to wait for a connection from the pool
        config.setIdleTimeout(600_000);      // how long an idle connection can live
        config.setMaxLifetime(1_800_000);    // force-recycle connections after 30 min

        // Validate connections are still alive before handing them out
        config.setConnectionTestQuery("SELECT 1");

        return new HikariDataSource(config);
    }
}

Pool sizing: bigger is not always better. The classic formula for CPU-bound workloads is 2 × core_count + effective_spindle_count. For most web apps 5–20 connections per JVM process is the right range. A pool that is too large wastes memory and causes lock contention on the database side.

Using the pool — same API, zero friction

From the caller's perspective, nothing changes. You call dataSource.getConnection() instead of DriverManager.getConnection(), and you call connection.close() when done — but close() now returns the connection to the pool instead of destroying it.

import javax.sql.DataSource;
import java.sql.*;

public class UserRepository {

    private final DataSource dataSource;

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

    public String findUsernameById(int id) throws SQLException {
        String sql = "SELECT username FROM users WHERE id = ?";

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

            ps.setInt(1, id);

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return rs.getString("username");
                }
                return null;
            }
        }
        // conn.close() here just returns the connection to the pool
    }
}

try-with-resources — closing everything correctly

JDBC uses three AutoCloseable resources: Connection, Statement / PreparedStatement, and ResultSet. Failing to close any one of them leaks the underlying OS handle and, for Connection, starves the pool.

The Java 7+ try-with-resources statement guarantees close() is called on every declared resource in reverse declaration order, even if an exception is thrown:

// CORRECT — all three resources are closed automatically
try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement("SELECT * FROM products WHERE category = ?");
     ResultSet rs = ps.executeQuery()) {   // ← note: executeQuery() inside the declaration

    // BAD — you cannot call setString before the try block above opens ps
}

// Better split: declare rs inside the body so you can set parameters first
try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement("SELECT * FROM products WHERE category = ?")) {

    ps.setString(1, "electronics");

    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getString("name"));
        }
    } // rs closed here
}   // ps and conn closed here (in reverse order)

Never nest try-with-resources carelessly. A common mistake is declaring ResultSet alongside Connection in one try header when you need to call setters on the statement first. Split into an outer try for Connection and PreparedStatement, and an inner try for ResultSet.

What happens when the pool is exhausted

If all connections are checked out and a new request arrives, dataSource.getConnection() blocks for up to connectionTimeout milliseconds, then throws a SQLException. This is intentional: it creates back-pressure that prevents your application from silently queuing thousands of requests and crashing the database.

// Always handle pool exhaustion gracefully in production code
try (Connection conn = dataSource.getConnection()) {
    // ...
} catch (SQLTimeoutException e) {
    // log and return HTTP 503 Service Unavailable
    throw new ServiceUnavailableException("Database pool exhausted", e);
} catch (SQLException e) {
    throw new DataAccessException("Query failed", e);
}

Lifecycle: one pool per application, shared across threads

HikariDataSource is thread-safe. Create it once — in a static field, a singleton bean, or a DI container — and share it everywhere. Creating a new HikariDataSource per request negates all pooling benefits.

// Application entry point — create pool once
public class App {

    private static final DataSource DATA_SOURCE = DataSourceFactory.createPool();

    public static void main(String[] args) {
        UserRepository repo = new UserRepository(DATA_SOURCE);
        System.out.println(repo.findUsernameById(1));

        // On shutdown, close the pool to release all connections
        ((HikariDataSource) DATA_SOURCE).close();
    }
}

Spring Boot users: if you declare a DataSource bean (or rely on auto-configuration with spring.datasource.* properties), Spring creates and manages the HikariCP pool for you. You just inject DataSource and call getConnection(). The pool is closed automatically on application shutdown.

Summary

Connection pools reuse physical database connections, cutting connection overhead from milliseconds per query down to microseconds. HikariCP is the standard choice: configure it once at startup, tune maximumPoolSize conservatively, and always use try-with-resources so connections and statements are returned to the pool immediately after use. A leaked connection that is never returned will exhaust the pool under load, causing all subsequent requests to fail — try-with-resources makes that class of bug impossible.