JDBC Tutorial Part 3: How to Use 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:

\
JDBC Tutorial part 3/3 – Database connection pools

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

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.


Also Published Here

Leave a Reply

Your email address will not be published. Required fields are marked *

Instagram

Why do People Say: "Developers are Lazy"?
The saying “work smart, not hard” is applicable for programmers.
.
https://hackernoon.com/why-do-people-say-developers-are-lazy

.
Author: Aga Wozniak
.
.
.
.
.
#blog #100Daysofcode #javascript #vuejs #datascientist #peoplewhocode #learntocode #coding #developerlife #frontenddeveloper #backenddeveloper #fullstackdeveloper #developer #webdeveloper #thedevlife #phpdeveloper #computerscience #programmer #programmingisfun #codingdays
...

Mitigating the DDOS Threats Facing Banks and Fintechs
As much as digitization and cyber simplified banking, the Fintech sector has left digital payment activity exposed to malicious and suspicious activity.
.
https://hackernoon.com/mitigating-the-ddos-threats-facing-banks-and-fintechs

.
Author: Josh Horowitz
.
.
.
.
.
#blog #100Daysofcode #javascript #vuejs #datascientist #peoplewhocode #learntocode #coding #developerlife #frontenddeveloper #backenddeveloper #fullstackdeveloper #developer #webdeveloper #thedevlife #phpdeveloper #computerscience #programmer #programmingisfun #codingdays
...

24 Best JavaScript Blogs and Websites
In this overview, we have compiled a list of popular sites, as well as JS blogs that are worth reading and keeping in your bookmarks.
.
https://hackernoon.com/24-best-javascript-blogs-and-websites

.
Author: natashatsybliyenko
.
.
.
.
.
#blog #100Daysofcode #javascript #vuejs #datascientist #peoplewhocode #learntocode #coding #developerlife #frontenddeveloper #backenddeveloper #fullstackdeveloper #developer #webdeveloper #thedevlife #phpdeveloper #computerscience #programmer #programmingisfun #codingdays
...

The Projects Working to Lower Ethereum Gas Fees
As more investors try their hand at DeFi, gas fees are shooting over the roof, making engaging with decentralized apps uneconomical for most users.
.
https://hackernoon.com/ethereum-gas-fees-are-there-any-projects-working-to-optimize-eth-gas-fees

.
Author: CryptoVirally SLR
.
.
.
.
.
#blog #100Daysofcode #javascript #vuejs #datascientist #peoplewhocode #learntocode #coding #developerlife #frontenddeveloper #backenddeveloper #fullstackdeveloper #developer #webdeveloper #thedevlife #phpdeveloper #computerscience #programmer #programmingisfun #codingdays
...

On the Edge of a New Year: IT Predictions for 2022
The single biggest cause of network errors are people.
.
https://hackernoon.com/an-interview-with-uplogix-ceo-lisa-frankovitch

.
Author: Mignonette Garnier
.
.
.
.
.
#blog #100Daysofcode #javascript #vuejs #datascientist #peoplewhocode #learntocode #coding #developerlife #frontenddeveloper #backenddeveloper #fullstackdeveloper #developer #webdeveloper #thedevlife #phpdeveloper #computerscience #programmer #programmingisfun #codingdays
...

How to Modernize IBM i Applications
If you’re like most IBM i users, you know how much value your IBM i data and applications bring to your business. Your end-users, however, may not. In today’s world of rich user experience, fast-paced application development, and constantly evolving customer expectations, IBM i applications are unde…
.
https://hackernoon.com/how-to-modernize-ibm-i-applications

.
Author: Lansa
.
.
.
.
.
#blog #100Daysofcode #javascript #vuejs #datascientist #peoplewhocode #learntocode #coding #developerlife #frontenddeveloper #backenddeveloper #fullstackdeveloper #developer #webdeveloper #thedevlife #phpdeveloper #computerscience #programmer #programmingisfun #codingdays
...