How to Use Connection Pooling in Java for Efficient Database Access

Use DataSource implementations like HikariCP or Apache DBCP to create a connection pool in Java. Configure the pool with settings like max connections, then get connections from the pool instead of opening new ones each time.

Syntax

Connection pooling in Java typically uses a DataSource object configured with pool settings. You get a connection by calling getConnection() on the pool, then close it to return it to the pool.

• DataSource pool: Manages a set of reusable connections.
• getConnection(): Fetches a connection from the pool.
• close(): Returns the connection to the pool instead of closing it.

java

HikariDataSource ds = new HikariDataSource();
ds.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
ds.setUsername("user");
ds.setPassword("pass");
ds.setMaximumPoolSize(10);

try (Connection conn = ds.getConnection()) {
    // use connection
}
// connection is returned to pool automatically

Example

This example shows how to use HikariCP, a popular connection pool library, to get and release connections efficiently.

java

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ConnectionPoolingExample {
    public static void main(String[] args) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:h2:mem:testdb");
        config.setUsername("sa");
        config.setPassword("");
        config.setMaximumPoolSize(5);

        try (HikariDataSource ds = new HikariDataSource(config)) {
            try (Connection conn = ds.getConnection()) {
                conn.createStatement().execute("CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(100))");
                conn.createStatement().execute("INSERT INTO users VALUES(1, 'Alice'), (2, 'Bob')");
            }

            try (Connection conn = ds.getConnection();
                 PreparedStatement ps = conn.prepareStatement("SELECT * FROM users");
                 ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    System.out.println("User: " + rs.getInt("id") + ", " + rs.getString("name"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Output

User: 1, Alice
User: 2, Bob

Common Pitfalls

Common mistakes when using connection pooling include:

• Not closing connections properly, which leaks connections and exhausts the pool.
• Setting pool size too small or too large, causing performance issues.
• Using raw DriverManager.getConnection() calls instead of the pool.

Always use try-with-resources or finally blocks to close connections.

java

/* Wrong way: Not closing connection returns none to pool */
Connection conn = ds.getConnection();
// use connection
// forgot conn.close(); leads to connection leak

/* Right way: Use try-with-resources to auto-close */
try (Connection conn2 = ds.getConnection()) {
    // use connection
} // conn2.close() returns connection to pool automatically

Quick Reference

Tips for using connection pooling in Java:

• Use a reliable pool library like HikariCP or Apache DBCP.
• Configure pool size based on your app's load.
• Always close connections to return them to the pool.
• Use DataSource instead of DriverManager.
• Monitor pool usage to avoid exhaustion.

Key Takeaways

Use a DataSource-based connection pool like HikariCP for efficient database connections.

Always close connections to return them to the pool and avoid leaks.

Configure pool size to match your application's concurrency needs.

Avoid using DriverManager directly when pooling is enabled.

Use try-with-resources to manage connection lifecycle safely.