Skip to main content

What is a database connection pool?

·258 words·2 mins
Programming Databases
Alejandro Duarte
Author
Alejandro Duarte
Alejandro Duarte is a Software Engineer, published author, and award winner. He currently works for MariaDB plc as a Developer Relations Engineer. Starting his coding journey at 13 with BASIC on a rudimentary black screen, Alejandro quickly transitioned to C, C++, and Java during his academic years at the National University of Colombia. Relocating first to the UK and then to Finland, Alejandro deepened his involvement in the open-source community. He’s a recognized figure in Java circles, credited with articles and videos amassing millions of views, and presentations at international events.

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:

If you liked this post, consider  following me on Twitter and subscribing to  Programming Brain on Youtube. Happy coding!

Related

How to execute SQL queries from Java (and prevent SQL injections)
·231 words·2 mins
Programming Databases
To run SQL queries in Java, you need a Connection object.
How to open and close JDBC connections
·153 words·1 min
Programming Databases
To open and close database connections in Java, get a JDBC driver for your database.
Testing MariaDB ColumnStore performance
·1288 words·7 mins
SQL Databases
MariaDB’s ColumnStore is an engine that stores data in a columnar fashion.