JDBC Connection Pooling Explained with HikariCP

Hey! tea lovers. Have you ever heard about connection pooling in JDBC? 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 connection. 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 the GitHub here or the full project here.

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 create 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 “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 noticeable for a few times, but doing so for thousands of times, the very frequency of your application for needing to call the databases, it 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 for 1000 times and you can feel the difference. More than 30% of the time is wasting in creating the connection.

Another scenario would, suppose 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 creating their own 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 your new colleague/ friend about 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 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 prerequisites.

Adding HikariCP in Your Project

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

Manually Adding HikariCP library

For 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

For 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 or 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>

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"

Creating JDBC Connection Pool with HikariCP

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

MethodsDescreptions
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.
Hkari 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, execute a query. Let us see how the example works.

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

In getDataSource if dataSource is null,static DataSource object, then we create a one with the createDataSource and configuring the pool with getHikariConfig. In the 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;
  }
}

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 application 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.

Leave a Reply

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