If you've been writing JDBC code for a while, you've likely fallen into the habit of opening a connection, running a query, and closing that connection immediately. In a small project or a local development environment, this feels fine. It's clean, it's explicit, and it works. But if we're talking about a production-grade User Profile service that handles thousands of requests per second, that "clean" approach is actually a performance killer.

The hidden cost of the handshake

Imagine your code looks something like this: every time a request hits your getUserProfile(int userId) method, you call DriverManager.getConnection(url, user, pass). To you, that's one line of code. To the network and the database, it's a heavy operation. The application has to establish a TCP connection, perform a TLS handshake, and the database has to authenticate the user and allocate memory for that specific session.

// The naive way: High overhead per request
public User getUserProfile(int userId) {
    try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE id = ?")) {
        ps.setInt(1, userId);
        // ... execute and return user
    } catch (SQLException e) {
        log.error("Database error", e);
    }
    return null;
}

I've seen this break in spectacular fashion during load tests. When your traffic spikes, you'll notice your response times climbing not because the queries are slow, but because your app is spending 80% of its time just shaking hands with the database. Eventually, you'll hit the max_connections limit on your PostgreSQL or MySQL server, and your application will start throwing "Too many connections" errors, effectively DOS-ing your own database.

Why we use HikariCP

This is where connection pooling comes in. Instead of creating and destroying connections, we maintain a "pool" of warm, already-authenticated connections. When your code asks for a connection, HikariCP hands you one that's already open. When you "close" it, HikariCP doesn't actually kill the connection; it just puts it back in the pool for the next request to use. I recommend HikariCP because it's incredibly lightweight and has become the industry standard for Java applications.

To get this working, you move the connection logic out of your method and into a singleton or a shared configuration bean. You initialize a HikariDataSource once when the application starts, and then you use that data source throughout the lifetime of the app.

// The professional way: Using a connection pool
public class DatabasePool {
    private static HikariDataSource ds;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/user_db");
        config.setUsername("prod_user");
        config.setPassword("secure_pass");
        
        // Tuning the pool
        config.setMaximumPoolSize(10); 
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30000); // 30 seconds
        config.setIdleTimeout(600000);     // 10 minutes
        
        ds = new HikariDataSource(config);
    }

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
}

// Now the service method is lightning fast
public User getUserProfile(int userId) {
    try (Connection conn = DatabasePool.getConnection(); 
         PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE id = ?")) {
        ps.setInt(1, userId);
        // ... execute and return user
    } catch (SQLException e) {
        log.error("Database error", e);
    }
    return null;
}

Balancing the pool size

You might be tempted to set setMaximumPoolSize(100) thinking more is better. I'll warn you now: that's usually a mistake. A database server has a finite amount of CPU and disk I/O. If you have 100 connections all trying to execute heavy queries simultaneously, they'll fight for the same disk head or CPU cache, and your overall throughput will actually drop. It's often better to have a smaller pool of 10-20 connections that are utilized efficiently than a massive pool that causes contention on the database server.

The magic of the try-with-resources block remains the same here. Even though conn.close() no longer physically disconnects from the server, calling it is still mandatory. If you forget to close the connection, you're "leaking" it. The connection stays checked out, and eventually, your pool will run dry, and your application will hang indefinitely while waiting for a connection to become available.

📋 Practical Task

Migrating a Legacy UserDAO to HikariCP

You have been handed a legacy UserDAO class that is currently using DriverManager.getConnection() inside every single method. This is causing severe latency in the production environment.

Your Task:

• Create a ConnectionManager class that implements a singleton HikariDataSource.
• Configure the pool with a maximum size of 10 and a connection timeout of 20 seconds.
• Refactor the UserDAO.findUserById(int id) method to retrieve its connection from your new ConnectionManager instead of using DriverManager.
• Ensure that the connection is properly returned to the pool using a try-with-resources block.