What is a database connection pool?

A database connection pool stores ready-to-use database connections that threads can borrow when they need them, and return when they finish the work with the database. This improves performance in terms of memory and processing consumption, especially in web applications such as websites and REST web services. The technique is also known as pooling. Here’s how to use it in Java apps.

HikariCP is one, if not the most, popular JDBC connection pool. You can add it to your project using Maven ( check the latest version):

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

The connection pool can be configured programmatically or through a configuration file (src/main/resources/database.properties):

jdbcUrl=jdbc:mariadb://localhost:3306/some_database
dataSource.username=the_database_user
dataSource.password=the_password_for_that_user

There are more properties available, like the size of the pool, whether to use auto-commit or not, timeouts, etc. Check the documentation for details.

Now you can create a JDBC DataSource object from which to get Statement or PreparedStatement objects to execute SQL statements:

HikariConfig hikariConfig = new HikariConfig("/database.properties");
HikariDataSource dataSource = new HikariDataSource(hikariConfig);
try (Connection connection = dataSource.getConnection()) {
    // ... run SQL queries here ...
}

If you don’t use a try-with-resources block, remember to return the connection object to the pool by calling connection.close() preferably in a finally block:

try {
    // ... code ...
} finally {
    dataSource.close();
}

See JDBC Tutorial Part 3: Using database connection pools, for a more detailed tutorial or watch me coding an example Java application using Connector/J with a MariaDB database: