CodersTea - Sip & Learn
Java

Make your life easy by HikariCP: Connection Pooling

 0

 0

 10

By CodersTea-Team

 On Feb 8, 2020 at 06:41 pm

 

Introduction

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 it and its implementation using HikariCP. HikariCP is one of the fastest connection pooling available in the market. For much more simplicity, this example or tutorial shows you the easiest way of creating a connection pool using HikariCP. Make your tea and be ready. You can find the code on github here or the full project here

The What

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 ThreadPool which we discussed in “Don’t buy, reuse the old” post.

The Why

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 ( 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. And suppose connection is taking 5msbut 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 and we know recycling help the environment (our application at least). Through this recycling resource overhead decreases. You can limit the number of connections as well which can help in traffic control to the database.

The When

I think we should use it whenever possible as it reduces much of the work. Some 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 then this can be easily done, or you don’t want each thread to create their DB connections.

Liabilities

One thing you need to be careful about closing the connection. If you don’t close the after using it won’t go back in the pool. And another part of your program might be waiting for it. It adds the more code which can be a good thing because through this you are not writing code for JDBC driver, putting username, password, and URL in each of the code which uses JDBC. 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 which is not that much of a task since you can just forward this post to them just in case (Self-promotion ;-) ).

The How: Time for HikariCP

At this point, I hope you have understood what JDBC connection pooling is, why 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 lightweight Java connection pool. The API and overall codebase are relatively small (a good 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.

Manual work

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.

Automated work

We don’t have that much time. Let’s just automate it, shall we? Use Maven or Gradle whichever suits your preference. 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 will 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>

 

Gradle
//Java 8 to 11compile "com.zaxxer:HikariCP:3.4.1"//java 7compile "com.zaxxer:HikariCP-java7:2.4.13"//Java 6compile "com.zaxxer:HikariCP-java6:2.3.13"

Using the HikariCP

Connection pooling is performed in the background and does not affect how an application is coded; however, the application must use a DataSource object (an object implementing the DataSource interface) to obtain a connection instead of using the DriverManager class. A class implementing the DataSource interface may or may not provide connection pooling.

Before beginning it, I will just clarify some things. As we have discussed earlier, the same pool is used again and again (you can create other pools of the same or different database also.) meaning it should be globally used. We have to make a HikariCP class containing a static factory method (encapsulation of object creation) getConnecttion that will return a connection object via static DataSource object of HikariCP which will be returned by getDataSource(). It first checks the static DataSoure variable for null. If true then it creates a new one, else returns the already created. For the creation of the HikariDataSource, we need HikariConfig which allows us to set the configuration such as JDBC URL, username, pool size, etc. This object is passed to the constructor of the HikariDataSource. The following are the essential and common configuration settings and their descriptions.

Methods Descreptions
setJdbcUrl(String) Set the JDBC URL
setUsername(String) 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
setIdleTimeout(long) 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.
 

The Final Showdown: Example

I have put the comments to show what exactly it is doing. I have written 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.

The Code

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 JdbcConnection {  // 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;  }}

 

So, guys, that’s it for this tea break. Hope you learned something today. Please share this if you have liked the post. We will meet at another tea break. You can find the code on github here or the full project here. I will be making a post on the best practices of JDBC which will maximize your performance and readability of the code in the next Healthy tea category. Hakuna Matata!!!

   javajdbcconnectionpooling

About Contributer

Questions / Comments


Comments

Be the first to comment on this post.

Questions / Comments

×