JDBC Connection Pooling ​

When discussing multithreading, we mentioned that creating threads is an expensive operation. If there are many small tasks to execute and threads are frequently created and destroyed, it can consume significant system resources. Often, the time spent creating and destroying threads exceeds the time spent executing tasks. To improve efficiency, a thread pool can be used.

Similarly, when performing CRUD operations with JDBC, opening a connection, executing the operation, and then closing the connection each time incurs a significant overhead. To avoid the frequent creation and destruction of JDBC connections, we can use a connection pool to reuse already established connections.

The JDBC connection pool has a standard interface called javax.sql.DataSource. Note that this class is part of the Java standard library, but it is just an interface. To use a JDBC connection pool, we must choose an implementation of the JDBC connection pool. Common JDBC connection pools include:

• HikariCP
• C3P0
• BoneCP
• Druid

Currently, the most widely used connection pool is HikariCP. To use a JDBC connection pool with HikariCP, first add the HikariCP dependency as follows:

com.zaxxer:HikariCP:2.7.1

Next, we need to create an instance of DataSource, which serves as the connection pool:

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/test");
config.setUsername("root");
config.setPassword("password");
config.addDataSourceProperty("connectionTimeout", "1000"); // Connection timeout: 1 second
config.addDataSourceProperty("idleTimeout", "60000"); // Idle timeout: 60 seconds
config.addDataSourceProperty("maximumPoolSize", "10"); // Maximum connections: 10
DataSource ds = new HikariDataSource(config);

Note that creating a DataSource is also an expensive operation, so the DataSource instance is typically stored as a global variable and remains throughout the application’s lifecycle.

Using the Connection Pool ​

How do we use the connection pool? The process is similar to the previous code; just change DriverManager.getConnection() to ds.getConnection():

try (Connection conn = ds.getConnection()) { // Obtain connection here
    ...
} // "Close" connection here

When obtaining a connection from the pool, there is no need to specify JDBC-related URLs, usernames, passwords, etc., because this information is already stored in the pool (held by the HikariConfig passed during the creation of HikariDataSource). Initially, the connection pool does not have any connections, so the first time ds.getConnection() is called, it will force the connection pool to create a Connection and return it to the client. When we call the conn.close() method (at the end of the try block), it does not actually "close" the connection; instead, it releases it back to the pool for reuse.

Thus, the connection pool maintains several Connection instances. When ds.getConnection() is called, it selects an idle connection, marks it as "in use," and returns it. When close() is called on the Connection, it is marked as "idle" again, awaiting the next request. This way, we maintain a limited number of connections while being able to frequently execute a large number of SQL statements.

Typically, connection pools offer numerous configurable parameters, such as the minimum and maximum active connections and the automatic closure of a connection after a period of idleness. These parameters should be configured reasonably based on the application's load. Additionally, most connection pools provide detailed real-time status monitoring.

Practice ​

Use a JDBC connection pool.

Summary ​

A database connection pool is a component that allows for the reuse of Connection objects, avoiding the need to repeatedly create new connections and improving the runtime efficiency of JDBC code.

Connection pools can be configured with detailed parameters and monitored effectively.