How to create a Multi-Database Pool in HikariCP

Hey, Tea lovers! Today we will go over a hack/way to create a multi-database pool in HikariCP. It is a continuation of the post “JDBC Connection Pooling Explained with HikariCP“, where we talked about the connection pooling and how we can create it using hikariCP.

This post is not an explanation post, rather showing one of the ways I used to create a common Class to access different connection pools of various databases. It is my personal solution from experience and not a standard approach. However, you are free to use the code as it is very generic and can be used right away.

Why will you be Using this Program

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 is a definition of boilerplate code.

I know, most of the projects are built with the framework such as Spring JPA or Hibernate, but sometimes you have to go to the basement. This post is about a program you can use if you are using multiple databases, data sources for connection pooling, using core Java and has scattered this all around the place, this program is the best fit for you.

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

Before Creating the Multi-Database HikariCP pool

Before diving into the code, you should be familiar with the JDBC connection pooling, What is a Datasource, and how to create a connection pool in HikariCP.

If you are having difficulty with this topic I would highly recommend the post “JDBC Connection Pooling Explained with HikariCP“. You can drop the comment if you like and also can discuss with us on social media @coderstea.

Bits and Pieces of the Multi-Database HikariCP Code

I will divide the code block to explain it thoroughly, as each block is contributing to a greater good. The base structure is very simple, and it will be a single class only. You are free to divide the code more to organize it better but to keep it simple, I will create a single util class with static functions only.

I will be posting the whole java class Code at the end after I explain its various components.

Database Credentials File

We need to have a single place where we will have all the information needed for the databases such as the URL, User, password, pool size etc.

I will be creating a properties file, but I recommend using the YAML file, as it is much cleaner.

# to get how many databases we have and automatically pick the appropriate credentials
# after adding new database add it in teh end separated by comma
databases=mysql1,postgres1
# configuration for mysql1
mysql1.url=url1
mysql1.user=user1
mysql1.password=password1
mysql1.driver=drivernameofmysql
mysql1.poolsize=12
# configuration for postgres1
postgres1.url=url2
postgres1.user=user2
postgres1.password=password2
postgres1.driver=drivernameofpostgres
postgres1.poolsize=10

databases contains the comma-separated list of the database we have and each value is the key for its own configuration.

To read the application.properties file, following is the code. And, yes I named the class MultiDbConnectionPool.

public class MultiDbConnectionPool {
    private static Properties prop;
    
    // read the properties file to get the credentials of databases
    private static Properties getProperties() {
        //return the existing properties if loaded earlier
        if(prop != null){
            return prop;
        }

        System.out.println("Loading the configuration File");
        String propertiesFileName = "application.properties";

        try (InputStream istream = MultiDbConnectionPool.class.getClassLoader().getResourceAsStream(propertiesFileName)) {
            Properties properties = new Properties();
            properties.load(istream);
            // save to global prop obkect
            prop = properties;
            return properties;

        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }
}

Database Factory Class

Now, we will go from the user’s perspective of the code, i.e how you will call the get the database connection, and build the flow accordingly.

getConnection(String dbName)

Let’s start with the function getConnection(dbName) that returns the Connection Object. dbName is the database connection it wants. In our case, it would be either mysql1 or postgres1.

Inside that function, we need to have some mechanism to get the connection from the given database’s connection pool. Because we have to uniquely store, identify, and retrieve the connection pool, we need to store it somewhere. As we have a dbName associated with each database, we will be using the Map<String, DataSource>. Key is the database name, and Datasource is the connection pool. Following is how our class would look like. I also made it synchronized in the case of a multithreading environment.

private final static Map<String, DataSource> DB_POOLMAP = new ConcurrentHashMap<>(2);

public static synchronized Connection getConnection(String dbName) throws Exception {
    if (!DB_POOLMAP.containsKey(dbName)) {
        createDataSource(dbName);
    }
    DataSource dataSource = DB_POOLMAP.get(dbName);
    return dataSource.getConnection();
}

Create HikariCP Config: HikariConfig

In the above code, we getting the data source from the map, but we need to add it to the map right? I am choosing lazy initialization here, unless you ask for a connection, you won’t be creating any pool until then.

First let’s create the HikariConfig, with the help of dbName and the properties file. Here I have used the Function to reduce the code redundancy. However, you can also use external function. You can check out more about the Function in my post, Be More Functional with Java’s Functional Interfaces.

private static HikariConfig getHikariConfig(String dbName) throws Exception {
    Properties properties = getProperties();
    if (properties == null || properties.get(dbName + ".url") == null) {
        throw new Exception("Database not defined");
    }

    HikariConfig hikaConfig = new HikariConfig();

    // to reduce the code duplication, using Function to get the value
    Function<String, String> getValue = (key) -> properties.get(dbName + "." + key).toString();

    //This is same as passing the Connection info to the DriverManager class.
    //your jdbc url. in my case it is mysql.
    hikaConfig.setJdbcUrl(getValue.apply("url"));
    //username
    hikaConfig.setUsername(getValue.apply("user"));
    //password
    hikaConfig.setPassword(getValue.apply("password"));
    //driver class name
    hikaConfig.setDriverClassName(getValue.apply("driver"));

    // Information about the pool
    //pool name. This is optional you don't have to do it.
    hikaConfig.setPoolName(dbName);

    //the maximum connection which can be created by or resides in the pool
    hikaConfig.setMaximumPoolSize(Integer.parseInt(getValue.apply("poolsize")));

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

Create and Store the HikariCP Datasource

Now that we have our HikariConfig ready, you have to create the HikariDataSource by providing this config. Next, add that DataSource with the given key into the DB_POOL_MAP.

private static void createDataSource(String dbName) throws Exception {
    System.out.println("Creting the dataspurce for " + dbName);
    HikariConfig hikariConfig = getHikariConfig(dbName);
    HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig);

    System.out.println("Adding the datasource to the global map");
    DB_POOLMAP.put(dbName, hikariDataSource);
}

The Final Code

Ok, I think I have shown you all the pieces of the code, now let’s arrange them in order and put them into the class. The final code will look like the following.

package howto;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.time.Duration;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;
import java.util.function.Function;

public class MultiDbConnectionPool {
    private final static Map<String, DataSource> DB_POOLMAP = new ConcurrentHashMap<>(2);
    private static Properties prop;

    public static synchronized Connection getConnection(String dbName) throws Exception {
        if (!DB_POOLMAP.containsKey(dbName)) {
            createDataSource(dbName);
        }
        DataSource dataSource = DB_POOLMAP.get(dbName);
        return dataSource.getConnection();
    }

    private static void createDataSource(String dbName) throws Exception {
        System.out.println("Creting the dataspurce for " + dbName);
        HikariConfig hikariConfig = getHikariConfig(dbName);
        HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig);

        System.out.println("Adding the datasource to the global map");
        DB_POOLMAP.put(dbName, hikariDataSource);
    }

    private static HikariConfig getHikariConfig(String dbName) throws Exception {
        Properties properties = getProperties();
        if (properties == null || properties.get(dbName + ".url") == null) {
            throw new Exception("Database not defined");
        }

        HikariConfig hikaConfig = new HikariConfig();

        // to reduce the code duplication, using Function to get the value
        Function<String, String> getValue = (key) -> properties.get(dbName + "." + key).toString();

        //This is same as passing the Connection info to the DriverManager class.
        //your jdbc url. in my case it is mysql.
        hikaConfig.setJdbcUrl(getValue.apply("url"));
        //username
        hikaConfig.setUsername(getValue.apply("user"));
        //password
        hikaConfig.setPassword(getValue.apply("password"));
        //driver class name
        hikaConfig.setDriverClassName(getValue.apply("driver"));

        // Information about the pool
        //pool name. This is optional you don't have to do it.
        hikaConfig.setPoolName(dbName);

        //the maximum connection which can be created by or resides in the pool
        hikaConfig.setMaximumPoolSize(Integer.parseInt(getValue.apply("poolsize")));

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

    // read the properties file to get the credentials of databases
    private static Properties getProperties() {
        //return the existing properties if loaded earlier
        if (prop != null) {
            return prop;
        }

        System.out.println("Loading the configuration File");
        String propertiesFileName = "application.properties";

        try (InputStream istream = MultiDbConnectionPool.class.getClassLoader().getResourceAsStream(propertiesFileName)) {
            Properties properties = new Properties();
            properties.load(istream);
            // save to global prop obkect
            prop = properties;
            return properties;

        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }
}

As you can see, it is highly customozable and flexible code. However, there are lots of ways we can make it even better, but just for the sake of simplicity, I will stop here, also I need to go for dinner.

One other way could be to use the singleton object, so no excessive use of static keyword needed. You can overload the function with the respective database, so you don’t need to pass the dbName again and again.

Conclusion

Thats it for this post. I hope you liked the post. to understand it fully please go over to my post on Hikari CP, “JDBC Connection Pooling Explained with HikariCP“. You also want to improve the performance of the JDBC with the help of following.

Hope you will use this code in one way or another. If you do, please share your thoughts in teh comment. You can discuss with us on social media @coderstea on insta, facebook, LinkedIn, and twitter.

The code can be found on GitHub and the entire project here.

See you in the next post. HAKUNA MATATA!!