Connection pool with HikariCP to multiple databases

Question

I am developing a monitoring plugin that queries multiple database. I would like to use HikariCP in order to keep the connection open, but I do not know how the connection pool has to be instantiated.

Does HikariCP use just one pool for several databases? or just one pool for one database, and it is my responsibility to instantiate as many pools as database I will use.

score 10 · Answer 1 · answered Nov 14 '14 at 15:32

The latter: a pool is associated to a single database configuration parameters, and it is your responsibility to instantiate as many pools as database I will use. Create the pools accordingly.

I have a DataSourceFactory to accomplish this:

public final class DataSourceFactory {

    private static final Logger LOG = LoggerFactory.getLogger(DataSourceFactory.class);

    //connection to MySQL
    private static DataSource mySQLDataSource;
    //connection to PostgreSQL
    private static DataSource postgresDataSource;

    private DataSourceFactory() { }

    //generic method to create the DataSource based on configuration
    private static DataSource getDataSource(String configurationProperties) {
        Properties conf = new Properties();
        try {
            conf.load(DataSourceFactory.class.getClassLoader().getResourceAsStream(configurationProperties));
        } catch (IOException e) {
            LOG.error("Can't locate database configuration", e);
        }
        HikariConfig config = new HikariConfig(conf);
        HikariDataSource dataSource = new HikariDataSource(config);
        return dataSource;
    }

    //retrieve the datasource for MySQL
    public static DataSource getMySQLDataSource() {
        LOG.debug("Retrieving data source for MySQL");
        if (mySQLDataSource == null) {
            synchronized(DataSourceFactory.class) {
                if (mySQLDataSource == null) {
                    LOG.debug("Creating data source for MySQL");
                    mySQLDataSource = getDataSource("mysql-connection.properties");
                }
            }
        }
        return mySQLDataSource;
    }

    //retrieve the datasource for Postgres
    public static DataSource getPostgresDataSource() {
        LOG.debug("Retrieving data source for Postgres");
        if (postgresDataSource == null) {
            synchronized(DataSourceFactory.class) {
                if (postgresDataSource == null) {
                    LOG.debug("Creating data source for Postgres");
                    postgresDataSource = getDataSource("postgres-connection.properties");
                }
            }
        }
        return postgresDataSource;
    }
}

Here's a file configuration example:

dataSourceClassName=com.mysql.jdbc.jdbc2.optional.MysqlDataSource
dataSource.url=jdbc:mysql://theHostName:thePort/nameOfDatabase
dataSource.user=user
dataSource.password=thIsIsN07mYR3alPa$s
dataSource.cachePrepStmts=true
dataSource.prepStmtCacheSize=100
dataSource.prepStmtCacheSqlLimit=2048
dataSource.useServerPrepStmts=true
autoCommit=false
maximumPoolSize=10

The biggest concern I have with spinning up multiple pools is that a pool can never have less than 1 connection active at a time. So, if you spin up 30 connection pools, they'll atleast have 30 connections even at idle stage — Murtaza Hasan, Nov 05 '21 at 15:02

Connection pool with HikariCP to multiple databases

1 Answers1