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 connection pooling and how we can create it using hikariCP.
This post is not an explanation post, but rather shows one of the ways I used to create a common Class to access different connection pools of various databases. It is my 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.
I would be happy to connect with you guys on social media. It’s @coderstea on Twitter, Linkedin, Facebook, Instagram, and YouTube.
Please Subscribe to the newsletter to know about the latest posts from CodersTea.
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 a definition of boilerplate code.
I know, most of the projects are built with a 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, and 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 HikariCP Multi-Database 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. Also, you should know about the Factory Design Pattern, if not, read this post: How to Easily use Factory Design Pattern in Java.
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 it with us on social media at @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 the 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.
Multi-Database HikariCP 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=10Code language: PHP (php)databases contains the comma-separated list of the database we have and each value is the key for its configuration.
To read the application.properties file, the 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;
}
}
}Code language: JavaScript (javascript)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 what 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();
}Code language: PHP (php)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 functions. 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;
}Code language: PHP (php)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);
}Code language: JavaScript (javascript)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;
}
}
}Code language: JavaScript (javascript)As you can see, it is a highly customizable 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 keywords is needed. You can overload the function with the respective database, so you don’t need to pass the dbName again and again.
Conclusion
That’s 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 the following.
Hope you will use this code in one way or another. If you do, please share your thoughts in the 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!!
I would be happy to connect with you guys on social media. It’s @coderstea on Twitter, Linkedin, Facebook, Instagram, and YouTube.
Please Subscribe to the newsletter to know about the latest posts from CodersTea.
