\
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](https://dev.to/alejandro_du/jdbc-tutorial-part-1-connecting-to-a-database-kfc) and [part 2](https://dev.to/alejandro_du/jdbc-tutorial-part-2-running-sql-queries-1lai) of this tutorial for more details. The source code is available on [GitHub](https://github.com/mariadb-developers/java-quickstart/tree/main/jdbc).

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

\
[  ![JDBC Tutorial part 3/3 – Database connection pools](https://cdn.hackernoon.com/images/ckydem-8-fw-00900-as-60-o-71642-y.jpg)](https://www.youtube.com/watch?v=Fq5HgBX6nec)

## 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:

[  ![Database connection and thread pools in Java](https://cdn.hackernoon.com/images/ckydem-8-fx-00910-as-6-asu-2-daf-4.jpg)](https://www.youtube.com/watch?v=kRdRne-pIoY)

## Adding HikariCP to the Project

In this tutorial, we’ll use the popular [HikariCP](https://github.com/brettwooldridge/HikariCP) implementation. Here’s the dependency to add to the **pom.xml** file:

\
```xml
<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](https://www.slf4j.org/manual.html#swapping) a more suitable binding in real-world applications. Here’s the snippet for the **pom.xml** file:

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

\
**Note:** If you use a [MariaDB](https://mariadb.com/) database, you don’t need to add HikariCP to use connection pools. Instead, you can use the `MariaDbPoolDataSource` class. I created an [example app](https://github.com/Programming-Brain/resource-pool-example) 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:

\
```java
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:

\
```java
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:

\
```java
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:

\
```java
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:

\
```java
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](https://github.com/brettwooldridge/HikariCP) for more information.


---

*Also Published [Here](https://dzone.com/articles/jdbc-tutorial-part-3-using-database-connection-poo)*

YouTube

2022 - HackerNoon Contributor of the Year - Analytics

2022 - HackerNoon Contributor of the Year - Database

2022 - HackerNoon Contributor of the Year - Docker

2022 - HackerNoon Contributor of the Year - Git

2022 - HackerNoon Contributor of the Year - Java

2022 - HackerNoon Contributor of the Year - Sql

Visit my personal Blog!

Nominated for 2022 - HackerNoon Contributor of the Year - Database

Nominated for 2022 - HackerNoon Contributor of the Year - Git

Nominated for 2022 - HackerNoon Contributor of the Year - Java

Nominated for 2022 - HackerNoon Contributor of the Year - Docker

Nominated for 2022 - HackerNoon Contributor of the Year - Sql

Nominated for 2022 - HackerNoon Contributor of the Year - Analytics

Too Long; Didn't Read

Cassandra Forward a free Cassandra-focused community event

JDBC Tutorial Part 3: How to Use Database Connection Pools

JDBC Tutorial Part 3: How to Use Database Connection Pools

Too Long; Didn't Read

Company Mentioned

@alejandroduarte

RELATED STORIES