How to create a jdbc connection pool based on HikariCP & cache using Guava Cache in SpringBoot ?

Contents

Why HikariCP ?
How to create a jdbc connection pool using HikariCP ?
Hikari Configuration
Cache configuration
Cache Key
Putting all of it together
Executing queries

Hikari connection pool is the best in terms of performance and flexibility in configuration at the moment. Hikari creates JDBC connection pools and manages the life cycle of them.Today I will show how we can use Hikari to create JDBC connection pools and how we can create multiple connection pools and cache them in a google guava cache.

In Cloud native development, you can have a central micro service which is responsible for maintaining c

communication with multiple databases where each database corresponds to a particular customer. This blog post shows how we can create Hikari Data Sources at runtime and manage several Hikari Datasource in a data source cache. for this purpose use google guava cache. This is because we can use a Hikari data source to connect with a particular database. This solution provides a way to connect multiple clients to connect to multiple databases. You can imagine it as a middleware that manages Hikari Connection Pool for N clients to M databases (N * M).

Why HikariCP ?

Some of the good features in HikariCP is

We can change the few of the configuration parameters dynamically after a Hikari Data Source is created, such as the number of JDBC connections.
We can change the JDBC connection credentials (user, pass). Hikari will change the credentials in the existing connections after the existing connections finish working.

This blog discusses in depth analysis of HikariCP and also shows how to create a JDBC connection pool using HikariCP.

How to create a jdbc connection pool using HikariCP ?

Hikari is a highly configurable API. Using HIkari we can configure the JDBC connections from how the connection pool will be created to how long a connection is kept open. There are many configurations possible. First we need to create a Hikari configuration. Using the configuration we can create a Hikari Data Source. A Data source wraps a pool of connection in it. Once a Data source is created we can simply retrieve a connection from the data source. We don't even realize that Hikari is managing a pool of connections and giving back an open and free connection object from the pool.

Hikari Configuration

First we need to create a Hikari Configuration that tells Hikari how to manage each JDBC connection and the JDBC connection pool as a whole. Note that each pool provides connection to one database. Here in Hikari we call it a data source.

We can define these parameters in the application.properties file as shown below.

application.properties file

hikari.cp.conf.autoCommit=true

hikari.cp.conf.connectionTimeout=300000

hikari.cp.conf.idleTimeout=600000

hikari.cp.conf.maxLifetime=1800000

hikari.cp.conf.minimumIdle=2

hikari.cp.conf.maximumPoolSize=12

Then we can initialize the configuration in a spring configuration file. The configuration will be used by the application at run time to create a JDBC connection pool using Hikari.

import java.unit.Properties;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.beans.factory.annotation.Bean;
import org.springframework.beans.factory.annotation.Configuration;
import org.springframework.beans.factory.annotation.Profile;
 
@Configuration
@Profile("cloud")
public class MyAppConfiguration {

    @Bean(name = "hikariDsConfiguration")
    public Properties  hikariDsConfiguration(
      @Value("${hikari.cp.conf.autoCommit}") String autoCommit ,
      @Value("${hikari.cp.conf.connectionTimeout}") String connectionTimeout ,
      @Value("${hikari.cp.conf.idleTimeout}") String idleTimeout,
      @Value("${hikari.cp.conf.maxLifetime}") String maxLifetime,
      @Value("${hikari.cp.conf.minimumIdle}") String minimumIdle,
      @Value("${hikari.cp.conf.maximumPoolSize}") String maximumPoolSize) {
         Properties properties = new  Properties();
          properties.setProperty("autoCommit", autoCommit);
          properties.setProperty("connectionTimeout", connectionTimeout);
          properties.setProperty("idleTimeout", idleTimeout); 
          properties.setProperty("maxLifetime", maxLifetime); 
          properties.setProperty("minimumIdle", minimumIdle); 
          properties.setProperty("maximumPoolSize", maximumPoolSize);
        return properties;    
    }
}

Using this Hikari configuration we can create a Hikari data source. At the time of data source creation the HikariCP will establish a minimum number of JDBC connections as defined in the configuration. After a Hikari data source is created we can ask for a JDBC connection to the data source. We will create data sources in the JdbcManager class.

Cache Configuration

Similarly to provide the google guava cache configuration we can define the parameter values in application. Properties

application.properties file

datasource.cache.maximumSize=1000

datasource.cache.expireAfterWrite=10

datasource.cache.timeUnit=HOURS

In the same spring configuration file we are initializing the guava cache.

import java.unit.concurrent.TimeUnit; 
import javax.sql.Datasource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.beans.factory.annotation.Bean;
import org.springframework.beans.factory.annotation.Configuration;
import org.springframework.beans.factory.annotation.Profile;

import com.google.common.cache.Cache;
import com.google.common.cache.CacheBuilder;
 
 
@Configuration
@Profile("cloud")
public class MyAppConfiguration {


    @Bean(name= "hikariDataSourceCache")
    public cache<DataSourceKey, DataSource>  hikariDataSourceCache (
      @Value("${datasource.cache.maximumSize}") int maximumSize , 
      @Value(" ${datasource.cache.expireAfterWrite}") long  expireAfterWrite,  
      @Value(" ${datasource.cache.timeUnit}") String timeUnit)  {
          return CacheBuilder.newBuilder()
                                          .maximumSize(maximumSize)
                                          .expireAfterWrite(expireAfterWrite, TimeUnit.valueOf( timeUnit))
                                          .build();
    } 
}

As we want to store many data sources in the cache the data source objects will be the value of the key value pair. We will need to define a key, for this reason we are defining our own key object. The DataSourceKey class provides the definition of the key. As we are using this object as a key in the datasource cache we need to override the equals and hashCode so that the key comparison can be done properly by the guava cache.

Cache Key

import java.unit.Properties;

public class DataSourceKey {
   private String clientID;
   private String dbHostUrl;
   private String userName;
   private String password;
   private Properties connProperties;  

   public DataSourceKey(String clientID,String dbHostUrl, String userName,
  String password) {
      this.clientID =  clientID;
      this.dbHostUrl = dbHostUrl;
      this.userName = userName;
      this.password = password;
      this.connProperties = generateConnProperties(dbHostUrl,userName,password);
   }
   //add getters
 
   public String getKey() { 
return clientID + "_" +  dbHostUrl + "_" +   userName;
   }
 
   private Properties generateConnProperties(String dbHostUrl, String userName, 
  String password ) {
      connectionProperties = new Properties();
      connectionProperties.setProperty("jdbc-url", dbHostUrl);
      connectionProperties.setProperty("user", userName ); 
      connectionProperties.setProperty("password", password );  
      return connectionProperties;
   }

 @Override
   public boolean equals(Object obj) {
       if(obj == null || !(obj instanceof DatasourceKey)) { return false; }
       if(obj == this) {return true};

       DataSourceKey foreignObject = (DataSourceKey) obj;
       if(foreignObject.getClientID().equals(this.clientID) &&
          foreignObject.getDbHostUrl().equals(this.dbHostUrl) &&
          foreignObject.getUserName().equals(this.userName) &&
          foreignObject.getPassword().equals(this.password) { 
            return true;
       } else {
            return false;
       } 
  }

  @Override 
  public int hashCode() {
       String hashString = this.clientID + this.dbHostUrl + 
this.userName +  this.password;
       return  hashString.hashCode();
  } 
}

Putting all of it together

Now Let's create a JdbcManager class that creates the DataSource Cache, Hikari DataSources and provides JDBC connections using DataSources. This class will be a spring service.

import java.sql.Connection;
import java.util.Properties;
import java.util.concurrent.Callable;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.google.common.cache.Cache;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

@ Service  
public class JdbcManager {
    @Autowired
     private Cache<DataSourcekey, DataSource> hikariDataSourceCache;
    @Autowired
     private Properties hikariDSConfiguration;

     public  JdbcManager() {
     }
     public Connection getConnection( String clientID, String dbHostUrl, String userName, 
                                                         String password)  throws RuntimeException {
        DataSourceKey  dataSourceKey = new  DataSourceKey( clientId, dbHostUrl, userName, password );
        try {
           DataSource dataSouce = hikariDataSourceCache.get(dataSourceKey,  
createDataSource( dataSourceKey , hikariDSConfiguration));
           return dataSource.getConnection();
        } catch (Exception e) { 
          throw new RuntimeException(e.getMessage());
        }
     }
     Callable <DataSource> createDataSource(DataSourceKey dataSourceKey,
                                                                      Properties hikariDSConfiguration) {
        return new Callable<DataSource>() {
          @Override
          public DataSource call() throws Exception {
              HikariConfig config = new  HikariConfig(hikariDSConfiguration);
              config.setPoolName(dataSourceKey.getKey());
              config.setJdbcUrl(dataSourceKey.getConnectionProperties().getProperty("jdbc-url"));
              config.setUsername(dataSourceKey.getConnectionProperties().getProperty("user")); 
              config.setPassword(dataSourceKey.getConnectionProperties().getProperty("password"));  
              DataSource dataSource = new HikariDataSource(config);
              return  dataSource;
          }
        };
     }
}

Executing queries

The QueryExecuter class is a REST controller that handles all incoming requests and uses the JdbcManager service to execute queries in Database. Here QueryExecuter is used to fetch all employee details. The Employee class is a plain POJO so it's not shown here explicitly. A list of Employees are sent as a response when the /path/getEmployees REST endpoint is called. Spring boot automatically converts the employee class to a JSON. Here the list of employees will be sent as a JSON array.

Note that I have not specified the database URL, host and port here. I leave it up to you, the developer, to fetch these details from a secured place. Remember the solution is to manage JDBC pools for multiple databases and we can assume here each cloud application instance provisioned for a customer will have its own database. Even if there is only one cloud application shared by different users there should be some data separation between user data. That could be a different database or same database, different tables or schema. The clientID is an identifier using which we can identify the client and can fetch corresponding database details.

import javax.ws.rs.core.Response;
import org.springframework.web.bind.annotation.RestController;  
import org.springframework.web.bind.annotation.RequestMapping; 
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestHeader; 
 
@RestController
public class QueryExecuter {

    private  JdbcManager  jdbcManager;
   @Autowired
   public  QueryExecuter( JdbcManager  jdbcManager) {
    //as its autowired the jdbc manager will be injected to the controller by spring boot 
    this.jdbcManager = jdbcManager ; 
   }

  @RequestMapping(value="/path/getEmployees", method= {RequestMethod.GET})
   public Response getResult(@RequestHeader HttpHeaders headers) {
     //fetch clientId from session context
     //fetch host, user, pass from some secure store for the clientId
     String SQL_QUERY = "select * from emp";
     List<Employee> employees = null; 
     try( Connection jdbcConn = 
jdbcManager.getConnection(<clientId>, <dbHostUrl>, <userName>, <password>);
       PreparedStatement pst = jdbcConn.prepareStatement( SQL_QUERY );
        ResultSet rs = pst.executeQuery();) {
            employees = new ArrayList<>();
            Employee employee;
            while ( rs.next() ) {
                employee = new Employee();
                employee.setEmpNo( rs.getInt( "empno" ) );
                employee.setEname( rs.getString( "ename" ) );
                employee.setJob( rs.getString( "job" ) );
                employee.setMgr( rs.getInt( "mgr" ) );
                employee.setHiredate( rs.getDate( "hiredate" ) );
                employee.setSal( rs.getInt( "sal" ) );
                employee.setComm( rs.getInt( "comm" ) );
                employee.setDeptno( rs.getInt( "deptno" ) );
                employees.add( employee );
            }
       }
       return  Response.ok(employees).build();
}

Remember you will need a JDBC driver plugin to communicate with your database. The plugin depends on the database you use. This code can be leverages irrespective of the type of database used.