JDBC Tutorial Part 3: Using Database Connection Pools

In part 3 of this tutorial series, you’ll learn what a database connection pool is and how to use it. Connection pools are a must in web applications that require handling simultaneous requests that consume a database.

Note: We’ll continue where the previous step of the tutorial left off. Refer to part 1 and part 2 of this tutorial for more details. The source code is available on GitHub.

Here’s a video version of this article, in case you want to see the concepts in action:

What Is a Connection Pool?

A pool (or pooling) is a software component that maintains a set of pre-configured resources. Clients of a pool (Java methods, classes, or in general, other software components) can “borrow” a resource, use it, and return it to the pool so that other clients can use it.

In our case, the resources are database connections, hence the name database connection pool. This kind of pool keeps database connections ready to use, that is, JDBC Connection objects. Typically, Java threads request a Connection object from the pool, perform CRUD operations, and close the connection, effectively returning it to the pool. In fact, threads are another common resource that uses pools. If you want to see this in action, I recorded a video about it:

Adding HikariCP to the Project

In this tutorial, we’ll use the popular HikariCP implementation. Here’s the dependency to add to the pom.xml file:

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

Add an SLF4J binding if you want to see HikariCP logs. SLF4J is a logging facade used by library vendors to allow developers to decide which logging framework to use in their applications. In this example, we’ll use the slf4j-simple binding, but you should pick a more suitable binding in real-world applications. Here’s the snippet for the pom.xml file:

<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-simple</artifactId>
    <version>1.8.0-beta4</version>
</dependency>

Note: If you use a MariaDB database, you don’t need to add HikariCP to use connection pools. Instead, you can use the MariaDbPoolDataSource class. I created an example app that uses it.

Initializing the Connection Pool

In part 1 of this tutorial, we created the initDatabaseConnection() method to initialize a static Connection object. We won’t be using this object anymore. Instead, we’ll get a Connection object from the pool whenever we need it. Therefore, instead of initializing a connection, we need to initialize a connection pool. Let’s start by removing the Connection field in the Application class and adding a connection pool instead:

public class Application {

    private static HikariDataSource dataSource;
    ...
}

For clarity, let’s rename the initDatabaseConnection() method to initDatabaseConnectionPool(). There, we can set the database connection details directly in Java for now:

private static void initDatabaseConnectionPool() {
    dataSource = new HikariDataSource();
    dataSource.setJdbcUrl("jdbcUrl=jdbc:mariadb://localhost:3306/jdbc_demo");
    dataSource.setUsername("user");
    dataSource.setPassword("pasword");
}

Similarly, the closeDatabaseConnection() should be now initDatabaseConnectionPool(). Implementing this method is straightforward:

private static void closeDatabaseConnectionPool() {
    dataSource.close();
}

Using the Connection Pool

Previously, we declared a static field to store a single shared Connection object in the Application class. We don’t have this instance object anymore. Instead, we need to ask the connection pool (the dataSource object) for a Connection. Here’s how the code would look like in the createData(String, int) method:

private static void createData(String name, int rating) throws SQLException {
    try (Connection connection = dataSource.getConnection()) {
        try (PreparedStatement statement = connection.prepareStatement("""
                    INSERT INTO programming_language(name, rating)
                    VALUES (?, ?)
                """)) {
            statement.setString(1, name);
            statement.setInt(2, rating);
            int rowsInserted = statement.executeUpdate();
            System.out.println("Rows inserted: " + rowsInserted);
        }
    }
}

We are using a try-with-resources block to call dataSource.getConnection(), which returns one of the Connection objects in the pool, if available. All the other CRUD methods should get a Connection object in the same fashion. We won’t list the code here, but you can use the same pattern in every CRUD method.

Configuring the Database Connection in a Properties File

At this point, we have the database connection details hardcoded in Java. However, HikariCP supports properties files for its configuration. For this, you need a HikariConfig object that contains the name of the properties file to load:

private static void initDatabaseConnectionPool() {
    HikariConfig hikariConfig = new HikariConfig("/database.properties");
    dataSource = new HikariDataSource(hikariConfig);
}

The database.properties should be placed in the src/main/resources/ directory. Don’t forget to add a / character in the string that references the file in Java. Here’s the content of the configuration file:

jdbcUrl=jdbc:mariadb://localhost:3306/jdbc_demo
dataSource.username=user
dataSource.password=password

Connection pools are configurable, and different implementations have different parameters that you can adjust. For example, you might want to configure the maximum number of connection objects that the pool maintains or the maximum lifetime of a connection in the pool. Refer to the pool implementation documentation for more information.