Home Best Practice JDBC Connection Pooling Explained with HikariCP

JDBC Connection Pooling Explained with HikariCP

Published: Last Updated on 6 comments

Hey, tea lovers! Have you ever heard about connection pooling in JDBC or HikariCP? If not, no worries at all, that is what this post is all about. We will talk about all the things about the JDBC connection pool and its implementation using HikariCP.

HikariCP is one of the fastest connection pooling available in the market for JDBC connections. It makes connection pooling very easy. get ready to learn more about this beast. But before, make your tea to sip and learn. And for the example used in the post, you can find it on GitHub here or the full project here.


I would be happy to connect with you guys on social media. It’s @coderstea on TwitterLinkedinFacebook, Instagram, and YouTube.

Please Subscribe to the newsletter to know about the latest posts from CodersTea.


What is Connection Pooling?

Connection pooling in JDBC allows us to reuse the JDBC connection rather than creating each every time. When called upon it returns the JDBC connection which is not in use( or creates a new one if its first request or pool limit is not yet reached). Think of it as your resource pooling like printers in a shared network or DHCP or Thread Pool which we discussed in the “Recycle The Threads And Save The Resources With Thread Pool” post.

Why Use Connection Pool?

Creating a connection with the database is an expensive process. It takes time to connect with the database. It is not noticed a few times, but doing so thousands of times, the very frequency of your application for needing to call the databases, can become very sluggish. Suppose it is taking 5ms to connect to DB and 10 ms for a query so around 15 ms for the first time. Do it 1000 times and you can feel the difference. More than 30% of the time is wasted on creating the connection.

Another scenario would suppose the connection is taking 5ms but the query is only taking 4ms then more than 60% is going for creation. You get the idea.

But in connection pooling, you are not creating a connection each time, instead, you are reusing it. Recycling not only helps the environment but also our system performance. Through this recycling resource overhead decreases. You can limit the number of connections as well which can help in traffic control to the database. There are also other factors for improving performance. You can learn about those in my other article “How To Achieve Greatness In JDBC Performance“;

When to use Connection Pool?

Ok, it does have lots of advantages, but there could be scenarios where it doesn’t make sense to use connection pooling. I think we should use it whenever possible as it reduces much of the work. Such as auto-connect to the database in case of timeout. It should be your primary choice, in case your application frequently calls the database or it is heavily dependent on the database.

For a simple application where you call the database once in a while like a day or something then you probably don’t need it. Or you can just set the pool limit to one so that there will only be one connection in the pool. In a multithreaded environment, if you want all the threads to use the same connection instead of each thread to create their DB connections, connection pooling is the savior for you.

Things to Keep in Mind When Using Connection Pooling

One thing you need to be careful about closing the connection. If you don’t close the connection after using it, it won’t go back to the pool. And another part of your program might be waiting for it. The best approach is to use try-with-resources, which you see in detail here. And you might have to explain to your new colleague/ friend what, why, and how to use connection pooling if he or she does not know. That is not that much of a big task since you can just share this post with them just in case (Self-promotion ?).

Explore JDBC Connection Poling with HikariCP

At this point, I hope you have understood what JDBC connection pooling is, why and when to use it, and its advantage and disadvantages. One thing that remains is, how to use it. It can be done very easily by HikariCP and gives much more performance than any other connection pooling library. HikariCP is a very fast and lightweight Java connection pool library out there. The API and overall codebase are relatively small, a great thing, and highly optimized. It is very easy to set up and use.

Before using it let us go through the prerequisites.

Adding HikariCP to Your Project

There are multiple ways you can load the HikariCP onto your project.

Manually Adding HikariCP library

For a plain Java project, you can download the jar from Maven Repo and download the latest ‘HikariCP’. There is support for older Java versions as well, which can be easily identified on the download page.

Adding HikariCP with Build Frameworks

To build frameworks, such as Maven or Gradle you can dependencies. At the time of the creation of this blog, 3.4.1 was the latest. Please check the latest one when using it, which can be found on MvnRepository. It can give you dependencies text in both Maven and Gradle format.

Maven

<!--Java 8 to 11 maven artifact-->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <!-- use the latest verion if possible.
     This was the latest on the birth of this blog -->
    <version>3.4.1</version>
</dependency>
<!-- Java 7 maven artifact (maintenance mode) -->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP-java7</artifactId>
    <version>2.4.13</version>
</dependency>
<!-- Java 6 maven artifact (maintenance mode) -->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP-java6</artifactId>
    <version>2.3.13</version>
</dependency>Code language: HTML, XML (xml)

Gradle

//Java 8 to 11
compile "com.zaxxer:HikariCP:3.4.1"
//java 7
compile "com.zaxxer:HikariCP-java7:2.4.13"
//Java 6
compile "com.zaxxer:HikariCP-java6:2.3.13"Code language: JavaScript (javascript)

Creating JDBC Connection Pool with HikariCP

While Creating the HikariCP DataSource object, an interface for Connection Pool provided by Java, we need to configure the pool. This includes JDBC URL, user, password, and pool size among other things. The following are the essential and common configuration settings and their descriptions.

MethodsDescriptions
setJdbcUrl(String)Set the JDBC URL
setUsername(String)Set the Username
setPassword(String)Set the password
setDriverClassName(String)Pass the driver class name to it
setMaximumPoolSize(int)The maximum connection which can be created by or resides in the pool
setConnectionTimeout(long)How much time a user can wait to get a connection from the pool? if it exceeds the time limit then an SQlException is thrown
setMaximumPoolSize(int)The maximum time a connection can sit idle in the pool. If it exceeds the time limit it is removed from the pool. If you don’t want to retire the connections simply put 0.
setPoolName(String)It sets the current pool name. This is optional you don’t have to do it.
Hikari CP Configuration

Example and Code for HikariCP

I have put the comments to show what exactly it is doing. I have written the code in the reverse order of creating the Config, passing it to the data source, getting the connection from the data source, and finally, executing a query. Let us see how the example works.

We call the static getConnection method for a connection. Which getDataSource() for DataSource object. These objects getConnection give us the connection from the pool.

In getDataSource if dataSource is null,static DataSource object, then we create one with the createDataSource and configure the pool with getHikariConfig. In main we just do some simple queries.

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.Duration;
public class HikariCP {
  // static global DataSource object.
  private static DataSource dataSource;
  public static void main(String[] args) {
    String query = "SELECT COUNT(*) FROM employee";
    //Using try-with-resources for auto closing connection, pstmt, and rs.
    try (Connection connection = getConnection();
         PreparedStatement pstmt = connection.prepareStatement(query);
         ResultSet rs = pstmt.executeQuery();
    ) {
      if (rs.next()) {
        System.out.println("Total employees are " + rs.getInt(1));
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  //Call the get connection method.
  static Connection getConnection() throws SQLException {
    return getDataSource().getConnection();
  }
  //Get the DataSource. If not available create the new one
  //It is not threadsafe. I didn't wanted to complicate things.
  private static DataSource getDataSource() {
    if (null == dataSource) {
      System.out.println("No DataSource is available. We will create a new one.");
      createDataSource();
    }
    return dataSource;
  }
  //To create a DataSource and assigning it to variable dataSource.
  private static void createDataSource() {
    HikariConfig hikariConfig = getHikariConfig();
    System.out.println("Configuration is ready.");
    System.out.println("Creating the HiakriDataSource and assigning it as the global");
    HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig);
    dataSource = hikariDataSource;
  }
   //returns HikariConfig containing JDBC connection properties
   //which will be used by HikariDataSource object.
  private static HikariConfig getHikariConfig() {
    System.out.println("Creating the config with HikariConfig with maximum pool size of 5");
    HikariConfig hikaConfig = new HikariConfig();
    //This is same as passing the Connection info to the DriverManager class.
    //your jdbc url. in my case it is mysql.
    hikaConfig.setJdbcUrl("jdbc:mysql://localhost:3306/test");
    //username
    hikaConfig.setUsername("root");
    //password
    hikaConfig.setPassword("IWontTellYouThis ;)");
    //driver class name
    hikaConfig.setDriverClassName("com.mysql.jdbc.Driver");
    // Information about the pool
    //pool name. This is optional you don't have to do it.
    hikaConfig.setPoolName("MysqlPool-1");
    //the maximum connection which can be created by or resides in the pool
    hikaConfig.setMaximumPoolSize(5);
    //how much time a user can wait to get a connection from the pool.
    //if it exceeds the time limit then a SQlException is thrown
    hikaConfig.setConnectionTimeout(Duration.ofSeconds(30).toMillis());
    //The maximum time a connection can sit idle in the pool.
    // If it exceeds the time limit it is removed form the pool.
    // If you don't want to retire the connections simply put 0.
    hikaConfig.setIdleTimeout(Duration.ofMinutes(2).toMillis());
    return hikaConfig;
  }
}Code language: JavaScript (javascript)

Create Multi-Database Connection Pool

There are multiple reasons you need to access multiple databases in a single project or codebase. The most common is to migrate the data from one server to another, or it is an architectural requirement. Whatever the reason is, the moment you have to do the same work, you tend to copy-paste the code, And JDBC is in itself a definition of boilerplate code.

All the details about creating a multi-database connection pool are described in this post: How to create a Multi-Database Pool in HikariCP

Multi-Database HikariCP pool code aims to reduce the boilerplate code to a bare minimum, giving you a small and readable code.

Conclusion

In this post, we talked about what is connection pooling, why to use it, and when to use it. It greatly improves the performance of database heavy applications but it can be further improved with some tricks in my other post “How To Achieve Greatness In JDBC Performance“. We explore how to use connection pooling with the help of HikariCP, the fastest and lightweight connection pool library for java at the time of writing this post. You can find the code of the example on GitHub here or the full project here.

Please share this if you have liked the post. We will meet at another tea break. And Let me know in the comments if you have any questions.


I would be happy to connect with you guys on social media. It’s @coderstea on TwitterLinkedinFacebook, Instagram, and YouTube.

Please Subscribe to the newsletter to know about the latest posts from CodersTea.


Subscribe
Notify of
guest
6 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Ads
Ads
Ads

@2023 All Right Reserved. Designed and Developed by CodersTea

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More