August 18, 2022

HikariCP Connection Pooling Java Example

In this post we’ll see how to configure connection pooling using HikariCP in your JDBC application.

Maven dependency for HikariCP

If you are using Maven then you can add the following dependency in pom.xml which works for Java 8 to Java 11.

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

You can also download jars from here- https://search.maven.org/search?q=com.zaxxer.hikaricp

HikariCP Connection pooling Java example

In the example DB used is MySQL it connects to knpcode schema and table is EMPLOYEE with columns as id, FIRST_NAME, LAST_NAME and DEPARTMENT.

Best way to create a HIkariCP DataSource is to instantiate an instance of HikariConfig class and provide the properties for connecting to DB and for connection pool. Then pass that config instance to create HikariDataSource object.

DB related configurations that you need to set are Driver class, URL, user name and password.

Some of the connection pool related properties that you can set are as follows-

  • autoCommit- This property controls the default auto-commit behavior of connections returned from the pool. Default is true.
  • connectionTimeout- This property controls the maximum number of milliseconds that a client will wait for a connection from the pool. Default is 30 seconds.
  • idleTimeout- This property controls the maximum amount of time that a connection is allowed to sit idle in the pool. Default is 10 minutes.
  • maxLifetime- This property controls the maximum lifetime of a connection in the pool. Default is 30 minutes.
  • maximumPoolSize- This property controls the maximum size that the pool is allowed to reach, including both idle and in-use connections. Default is 10.

For the example DB credentials and connection pool related configuration properties are kept in a properties file (db.properties).

DB.DRIVER_CLASS=com.mysql.cj.jdbc.Driver
DB.DB_URL=jdbc:mysql://localhost:3306/knpcode
DB.DB_USER=root
DB.DB_PASSWORD=admin
DB.MAX_POOL_SIZE=5

Following class is used to create a HikariDataSource by setting DB properties and connection pool properties which are read from the properties file.

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import javax.sql.DataSource;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class DSCreator {
  private static HikariDataSource ds;
  static {
    try {
      Properties properties = new Properties();
      // Loading properties file from classpath
      InputStream inputStream = DSCreator.class
                                         .getClassLoader()
                                         .getResourceAsStream("db.properties");
      if(inputStream == null){
        throw new IOException("File not found");
      }
      properties.load(inputStream);	
      HikariConfig config = new HikariConfig();
      // This property is optional now 
      config.setDriverClassName(properties.getProperty("DB.DRIVER_CLASS"));
      config.setJdbcUrl(properties.getProperty("DB.DB_URL"));
      config.setUsername(properties.getProperty("DB.DB_USER"));
      config.setPassword(properties.getProperty("DB.DB_PASSWORD"));
      config.setMaximumPoolSize(Integer.parseInt(properties.getProperty("DB.MAX_POOL_SIZE")));
      config.addDataSourceProperty("cachePrepStmts", "true");
      config.addDataSourceProperty("prepStmtCacheSize", "250");
      config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
      // Create DataSource
      ds = new HikariDataSource(config);
    }catch(IOException e) {
      e.printStackTrace();
    }
  }
  public static DataSource getDataSource() {
    return ds;
  } 
}

Test class that creates a connection using the returned DataSource object and queries the DB.

public class DSDemo {
  public static void main(String[] args) {
    DSDemo dsDemo = new DSDemo();
    dsDemo.displayEmployeeById(16);
  }
  private void displayEmployeeById(int id){
    Connection connection = null; 
    String selectSQL = "SELECT * FROM EMPLOYEE WHERE id = ?";
    PreparedStatement prepStmt = null;
    try {
      DataSource ds = DSCreator.getDataSource();
      connection = ds.getConnection();
      prepStmt = connection.prepareStatement(selectSQL);
      prepStmt.setInt(1, id);
      ResultSet rs = prepStmt.executeQuery();
      while(rs.next()){
        System.out.println("id: " + rs.getInt("id"));
        System.out.println("First Name: " + rs.getString("FIRST_NAME"));
        System.out.println("Last Name: " + rs.getString("LAST_NAME"));
        System.out.println("Department: " + rs.getString("DEPARTMENT"));
      }
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      }
    }
  }
}

That's all for the topic HikariCP Connection Pooling Java Example. If something is missing or you have something to share about the topic please write a comment.


You may also like

No comments:

Post a Comment