DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Spring Microservice Tip: Abstracting the Database Hostname With Environment Variable
  • Mastering Thread-Local Variables in Java: Explanation and Issues
  • How To Verify Database Connection From a Spring Boot Application
  • Java EE 6 Pet Catalog with GlassFish and MySQL

Trending

  • Building Scalable and Resilient Data Pipelines With Apache Airflow
  • Java's Quiet Revolution: Thriving in the Serverless Kubernetes Era
  • Enhancing Avro With Semantic Metadata Using Logical Types
  • Understanding Java Signals
  1. DZone
  2. Data Engineering
  3. Data
  4. Database Connection Pooling in Java With HikariCP

Database Connection Pooling in Java With HikariCP

Connection pooling is a great technique for database optimization, and HikariCP is among the best. Let's set up multiple connection pools with different configurations.

By 
Bill O'Neil user avatar
Bill O'Neil
·
May. 08, 17 · Tutorial
Likes (12)
Comment
Save
Tweet
Share
240.3K Views

Join the DZone community and get the full member experience.

Join For Free

Connection pooling is a technique used to improve performance in applications with dynamic database-driven content. Opening and closing database connections may not seem like a costly expense, but it can add up rather quickly. Let's assume it takes 5ms to establish a connection and 5ms to execute your query (completely made up numbers), 50% of the time is establishing the connection. Extend this to thousands or tens of thousands of requests and there is a lot of wasted network time. Connection pools are essentially a cache of open database connections. Once you open and use a database connection instead of closing it you add it back to the pool. When you go to fetch a new connection, if there is one available in the pool, it will use that connection instead of establishing another.

Why Use a Connection Pool?

  • Constantly opening and closing connections can be expensive. Cache and reuse.
  • When activity spikes you can limit the number of connections to the database. This will force code to block until a connection is available. This is especially helpful in distributed environments.
  • Split out common operations into multiple pools. For instance, you can have a pool designated for OLAP connections and a pool for OLTP connections each with different configurations.

HikariCP

HikariCP is a very fast lightweight Java connection pool. The API and overall codebase are relatively small (a good thing) and highly optimized. It also does not cut corners for performance like many other Java connection pool implementations. The Wiki is highly informative and dives really deep. If you are not as interested in the deep dives, you should at least read and watch the video on connection pool sizing.

Creating Connection pools

Let's create two connections pools one for OLTP (named transactional) queries and one for OLAP (named processing). We want them split so we can have a queue of reporting queries back up but allow critical transactional queries to still get priority (this is up to the database of course, but we can help a bit). We can also easily configure different timeouts or transaction isolation levels. For now we just change their names and pool sizes.

Configuring the Pools

HikariCP offers several options for configuring the pool. Since we are fans of roll your own and already created our own Typesafe Configuration, we will reuse that. Notice we are using some of Typesafe's configuration inheritance.

pools {
    default {
        jdbcUrl = "jdbc:hsqldb:mem:testdb"
        maximumPoolSize = 10
        minimumIdle = 2
        username = "SA"
        password = ""
        cachePrepStmts = true
        prepStmtCacheSize = 256
        prepStmtCacheSqlLimit = 2048
        useServerPrepStmts = true
    }

    // This syntax inherits the config from pools.default.
    // We can then override or add additional properties.
    transactional = ${pools.default} {
        poolName = "transactional"
    }

    processing = ${pools.default} {
        poolName = "processing"
        maximumPoolSize = 30
    }
}

View on GitHub.

ConnectionPool Factory

Since we don't need any additional state a static factory method passing our config, MetricRegistry and HealthCheckRegistry are sufficient. Once again, Dropwizard Metrics makes an appearance hooking into our connection pool now. This will provide us with some very useful pool stats in the future.

public class ConnectionPool {
    private ConnectionPool() {

    }
    /*
     * Expects a config in the following format
     *
     * poolName = "test pool"
     * jdbcUrl = ""
     * maximumPoolSize = 10
     * minimumIdle = 2
     * username = ""
     * password = ""
     * cachePrepStmts = true
     * prepStmtCacheSize = 256
     * prepStmtCacheSqlLimit = 2048
     * useServerPrepStmts = true
     *
     * Let HikariCP bleed out here on purpose
     */
    public static HikariDataSource getDataSourceFromConfig(
        Config conf
        , MetricRegistry metricRegistry
        , HealthCheckRegistry healthCheckRegistry) {

        HikariConfig jdbcConfig = new HikariConfig();
        jdbcConfig.setPoolName(conf.getString("poolName"));
        jdbcConfig.setMaximumPoolSize(conf.getInt("maximumPoolSize"));
        jdbcConfig.setMinimumIdle(conf.getInt("minimumIdle"));
        jdbcConfig.setJdbcUrl(conf.getString("jdbcUrl"));
        jdbcConfig.setUsername(conf.getString("username"));
        jdbcConfig.setPassword(conf.getString("password"));

        jdbcConfig.addDataSourceProperty("cachePrepStmts", conf.getBoolean("cachePrepStmts"));
        jdbcConfig.addDataSourceProperty("prepStmtCacheSize", conf.getInt("prepStmtCacheSize"));
        jdbcConfig.addDataSourceProperty("prepStmtCacheSqlLimit", conf.getInt("prepStmtCacheSqlLimit"));
        jdbcConfig.addDataSourceProperty("useServerPrepStmts", conf.getBoolean("useServerPrepStmts"));

        // Add HealthCheck
        jdbcConfig.setHealthCheckRegistry(healthCheckRegistry);

        // Add Metrics
        jdbcConfig.setMetricRegistry(metricRegistry);
        return new HikariDataSource(jdbcConfig);
    }
}

View on GitHub.

Interested in knowing when your application cannot connect to the database? Take a look at Monitoring your Java Services with Dropwizard Health Checks.

ConnectionPool Implementation

Now that we have two separate configs for our transactional and processing pools, let's initialize them. Once again, we are not using DI and instead are using the enum singleton pattern for lazy initialized singletons. Feel free to use DI in your own implementations. We now have two different pools with different configs that each lazily wire themselves up on demand.

public class ConnectionPools {
    private static final Logger logger = LoggerFactory.getLogger(ConnectionPools.class);
    /*
     * Normally we would be using the app config but since this is an example
     * we will be using a localized example config.
     */
    private static final Config conf = new Configs.Builder()
                                                  .withResource("examples/hikaricp/pools.conf")
                                                  .build();
    /*
     *  This pool is made for short quick transactions that the web application uses.
     *  Using enum singleton pattern for lazy singletons
     */
    private enum Transactional {
        INSTANCE(ConnectionPool.getDataSourceFromConfig(conf.getConfig("pools.transactional"), Metrics.registry(), HealthChecks.getHealthCheckRegistry()));
        private final DataSource dataSource;
        private Transactional(DataSource dataSource) {
            this.dataSource = dataSource;
        }
        public DataSource getDataSource() {
            return dataSource;
        }
    }
    public static DataSource getTransactional() {
        return Transactional.INSTANCE.getDataSource();
    }

    /*
     *  This pool is designed for longer running transactions / bulk inserts / background jobs
     *  Basically if you have any multithreading or long running background jobs
     *  you do not want to starve the main applications connection pool.
     *
     *  EX.
     *  You have an endpoint that needs to insert 1000 db records
     *  This will queue up all the connections in the pool
     *
     *  While this is happening a user tries to log into the site.
     *  If you use the same pool they may be blocked until the bulk insert is done
     *  By splitting pools you can give transactional queries a much higher chance to
     *  run while the other pool is backed up.
     */
    private enum Processing {
        INSTANCE(ConnectionPool.getDataSourceFromConfig(conf.getConfig("pools.processing"), Metrics.registry(), HealthChecks.getHealthCheckRegistry()));
        private final DataSource dataSource;
        private Processing(DataSource dataSource) {
            this.dataSource = dataSource;
        }
        public DataSource getDataSource() {
            return dataSource;
        }
    }

    public static DataSource getProcessing() {
        return Processing.INSTANCE.getDataSource();
    }

    public static void main(String[] args) {
        logger.debug("starting");
        DataSource processing = ConnectionPools.getProcessing();
        logger.debug("processing started");
        DataSource transactional = ConnectionPools.getTransactional();
        logger.debug("transactional started");
        logger.debug("done");
    }
}

View on GitHub.

ConnectionPool Implementation Log

Notice how the config properly inherited its values and each config is lazily loaded.

2017-02-08 22:43:04.428 [main] INFO  com.stubbornjava.common.Configs - Loading configs first row is highest priority, second row is fallback and so on
2017-02-08 22:43:04.428 [main] INFO  com.stubbornjava.common.Configs - examples/hikaricp/pools.conf
2017-02-08 22:43:04.439 [main] DEBUG com.stubbornjava.common.Configs - Logging properties. Make sure sensitive data such as passwords or secrets are not logged!
2017-02-08 22:43:04.439 [main] DEBUG com.stubbornjava.common.Configs - {
    "pools" : {
        "default" : {
            "cachePrepStmts" : true,
            "jdbcUrl" : "jdbc:hsqldb:mem:testdb",
            "maximumPoolSize" : 10,
            "minimumIdle" : 2,
            "password" : "",
            "prepStmtCacheSize" : 256,
            "prepStmtCacheSqlLimit" : 2048,
            "useServerPrepStmts" : true,
            "username" : "SA"
        },
        "processing" : {
            "cachePrepStmts" : true,
            "jdbcUrl" : "jdbc:hsqldb:mem:testdb",
            "maximumPoolSize" : 30,
            "minimumIdle" : 2,
            "password" : "",
            "poolName" : "processing",
            "prepStmtCacheSize" : 256,
            "prepStmtCacheSqlLimit" : 2048,
            "useServerPrepStmts" : true,
            "username" : "SA"
        },
        "transactional" : {
            "cachePrepStmts" : true,
            "jdbcUrl" : "jdbc:hsqldb:mem:testdb",
            "maximumPoolSize" : 10,
            "minimumIdle" : 2,
            "password" : "",
            "poolName" : "transactional",
            "prepStmtCacheSize" : 256,
            "prepStmtCacheSqlLimit" : 2048,
            "useServerPrepStmts" : true,
            "username" : "SA"
        }
    }
}

2017-02-08 22:43:04.440 [main] DEBUG c.s.e.hikaricp.ConnectionPools - starting
2017-02-08 22:43:04.525 [main] DEBUG com.zaxxer.hikari.HikariConfig - processing - configuration:
2017-02-08 22:43:04.539 [main] DEBUG com.zaxxer.hikari.HikariConfig - allowPoolSuspension.............false
2017-02-08 22:43:04.539 [main] DEBUG com.zaxxer.hikari.HikariConfig - autoCommit......................true
2017-02-08 22:43:04.539 [main] DEBUG com.zaxxer.hikari.HikariConfig - catalog.........................none
2017-02-08 22:43:04.539 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionInitSql...............none
2017-02-08 22:43:04.539 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTestQuery.............none
2017-02-08 22:43:04.540 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTimeout...............30000
2017-02-08 22:43:04.540 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSource......................none
2017-02-08 22:43:04.540 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceClassName.............none
2017-02-08 22:43:04.540 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceJNDI..................none
2017-02-08 22:43:04.543 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceProperties............{password=<masked>, prepStmtCacheSqlLimit=2048, cachePrepStmts=true, useServerPrepStmts=true, prepStmtCacheSize=256}
2017-02-08 22:43:04.543 [main] DEBUG com.zaxxer.hikari.HikariConfig - driverClassName.................none
2017-02-08 22:43:04.543 [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckProperties...........{}
2017-02-08 22:43:04.544 [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckRegistry.............com.codahale.metrics.health.HealthCheckRegistry@34123d65
2017-02-08 22:43:04.544 [main] DEBUG com.zaxxer.hikari.HikariConfig - idleTimeout.....................600000
2017-02-08 22:43:04.544 [main] DEBUG com.zaxxer.hikari.HikariConfig - initializationFailFast..........true
2017-02-08 22:43:04.544 [main] DEBUG com.zaxxer.hikari.HikariConfig - initializationFailTimeout.......1
2017-02-08 22:43:04.545 [main] DEBUG com.zaxxer.hikari.HikariConfig - isolateInternalQueries..........false
2017-02-08 22:43:04.545 [main] DEBUG com.zaxxer.hikari.HikariConfig - jdbc4ConnectionTest.............false
2017-02-08 22:43:04.545 [main] DEBUG com.zaxxer.hikari.HikariConfig - jdbcUrl........................."jdbc:hsqldb:mem:testdb"
2017-02-08 22:43:04.546 [main] DEBUG com.zaxxer.hikari.HikariConfig - leakDetectionThreshold..........0
2017-02-08 22:43:04.546 [main] DEBUG com.zaxxer.hikari.HikariConfig - maxLifetime.....................1800000
2017-02-08 22:43:04.546 [main] DEBUG com.zaxxer.hikari.HikariConfig - maximumPoolSize.................30
2017-02-08 22:43:04.546 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricRegistry..................com.codahale.metrics.MetricRegistry@59474f18
2017-02-08 22:43:04.546 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricsTrackerFactory...........none
2017-02-08 22:43:04.547 [main] DEBUG com.zaxxer.hikari.HikariConfig - minimumIdle.....................2
2017-02-08 22:43:04.547 [main] DEBUG com.zaxxer.hikari.HikariConfig - password........................<masked>
2017-02-08 22:43:04.547 [main] DEBUG com.zaxxer.hikari.HikariConfig - poolName........................"processing"
2017-02-08 22:43:04.547 [main] DEBUG com.zaxxer.hikari.HikariConfig - readOnly........................false
2017-02-08 22:43:04.547 [main] DEBUG com.zaxxer.hikari.HikariConfig - registerMbeans..................false
2017-02-08 22:43:04.547 [main] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutor...............none
2017-02-08 22:43:04.548 [main] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutorService........internal
2017-02-08 22:43:04.548 [main] DEBUG com.zaxxer.hikari.HikariConfig - threadFactory...................internal
2017-02-08 22:43:04.548 [main] DEBUG com.zaxxer.hikari.HikariConfig - transactionIsolation............default
2017-02-08 22:43:04.548 [main] DEBUG com.zaxxer.hikari.HikariConfig - username........................"SA"
2017-02-08 22:43:04.548 [main] DEBUG com.zaxxer.hikari.HikariConfig - validationTimeout...............5000
2017-02-08 22:43:04.551 [main] INFO  com.zaxxer.hikari.HikariDataSource - processing - Starting...
2017-02-08 22:43:05.084 [main] INFO  com.zaxxer.hikari.pool.PoolBase - processing - Driver does not support get/set network timeout for connections. (feature not supported)
2017-02-08 22:43:05.089 [main] DEBUG com.zaxxer.hikari.pool.HikariPool - processing - Added connection org.hsqldb.jdbc.JDBCConnection@66982506
2017-02-08 22:43:05.109 [main] INFO  com.zaxxer.hikari.HikariDataSource - processing - Start completed.
2017-02-08 22:43:05.110 [main] DEBUG c.s.e.hikaricp.ConnectionPools - processing started
2017-02-08 22:43:05.112 [main] DEBUG com.zaxxer.hikari.HikariConfig - transactional - configuration:
2017-02-08 22:43:05.115 [main] DEBUG com.zaxxer.hikari.HikariConfig - allowPoolSuspension.............false
2017-02-08 22:43:05.115 [main] DEBUG com.zaxxer.hikari.HikariConfig - autoCommit......................true
2017-02-08 22:43:05.115 [main] DEBUG com.zaxxer.hikari.HikariConfig - catalog.........................none
2017-02-08 22:43:05.115 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionInitSql...............none
2017-02-08 22:43:05.115 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTestQuery.............none
2017-02-08 22:43:05.116 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTimeout...............30000
2017-02-08 22:43:05.116 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSource......................none
2017-02-08 22:43:05.116 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceClassName.............none
2017-02-08 22:43:05.116 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceJNDI..................none
2017-02-08 22:43:05.116 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceProperties............{password=<masked>, prepStmtCacheSqlLimit=2048, cachePrepStmts=true, useServerPrepStmts=true, prepStmtCacheSize=256}
2017-02-08 22:43:05.116 [main] DEBUG com.zaxxer.hikari.HikariConfig - driverClassName.................none
2017-02-08 22:43:05.116 [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckProperties...........{}
2017-02-08 22:43:05.117 [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckRegistry.............com.codahale.metrics.health.HealthCheckRegistry@34123d65
2017-02-08 22:43:05.117 [main] DEBUG com.zaxxer.hikari.HikariConfig - idleTimeout.....................600000
2017-02-08 22:43:05.117 [main] DEBUG com.zaxxer.hikari.HikariConfig - initializationFailFast..........true
2017-02-08 22:43:05.117 [main] DEBUG com.zaxxer.hikari.HikariConfig - initializationFailTimeout.......1
2017-02-08 22:43:05.117 [main] DEBUG com.zaxxer.hikari.HikariConfig - isolateInternalQueries..........false
2017-02-08 22:43:05.118 [main] DEBUG com.zaxxer.hikari.HikariConfig - jdbc4ConnectionTest.............false
2017-02-08 22:43:05.118 [main] DEBUG com.zaxxer.hikari.HikariConfig - jdbcUrl........................."jdbc:hsqldb:mem:testdb"
2017-02-08 22:43:05.118 [main] DEBUG com.zaxxer.hikari.HikariConfig - leakDetectionThreshold..........0
2017-02-08 22:43:05.118 [main] DEBUG com.zaxxer.hikari.HikariConfig - maxLifetime.....................1800000
2017-02-08 22:43:05.118 [main] DEBUG com.zaxxer.hikari.HikariConfig - maximumPoolSize.................10
2017-02-08 22:43:05.119 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricRegistry..................com.codahale.metrics.MetricRegistry@59474f18
2017-02-08 22:43:05.119 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricsTrackerFactory...........none
2017-02-08 22:43:05.119 [main] DEBUG com.zaxxer.hikari.HikariConfig - minimumIdle.....................2
2017-02-08 22:43:05.119 [main] DEBUG com.zaxxer.hikari.HikariConfig - password........................<masked>
2017-02-08 22:43:05.119 [main] DEBUG com.zaxxer.hikari.HikariConfig - poolName........................"transactional"
2017-02-08 22:43:05.119 [main] DEBUG com.zaxxer.hikari.HikariConfig - readOnly........................false
2017-02-08 22:43:05.120 [main] DEBUG com.zaxxer.hikari.HikariConfig - registerMbeans..................false
2017-02-08 22:43:05.120 [main] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutor...............none
2017-02-08 22:43:05.120 [main] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutorService........internal
2017-02-08 22:43:05.120 [main] DEBUG com.zaxxer.hikari.HikariConfig - threadFactory...................internal
2017-02-08 22:43:05.120 [main] DEBUG com.zaxxer.hikari.HikariConfig - transactionIsolation............default
2017-02-08 22:43:05.120 [main] DEBUG com.zaxxer.hikari.HikariConfig - username........................"SA"
2017-02-08 22:43:05.120 [main] DEBUG com.zaxxer.hikari.HikariConfig - validationTimeout...............5000
2017-02-08 22:43:05.121 [main] INFO  com.zaxxer.hikari.HikariDataSource - transactional - Starting...
2017-02-08 22:43:05.121 [main] INFO  com.zaxxer.hikari.pool.PoolBase - transactional - Driver does not support get/set network timeout for connections. (feature not supported)
2017-02-08 22:43:05.122 [main] DEBUG com.zaxxer.hikari.pool.HikariPool - transactional - Added connection org.hsqldb.jdbc.JDBCConnection@69c81773
2017-02-08 22:43:05.123 [main] INFO  com.zaxxer.hikari.HikariDataSource - transactional - Start completed.
2017-02-08 22:43:05.123 [main] DEBUG c.s.e.hikaricp.ConnectionPools - transactional started
2017-02-08 22:43:05.123 [main] DEBUG c.s.e.hikaricp.ConnectionPools - done
Database connection Java (programming language)

Published at DZone with permission of Bill O'Neil. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Spring Microservice Tip: Abstracting the Database Hostname With Environment Variable
  • Mastering Thread-Local Variables in Java: Explanation and Issues
  • How To Verify Database Connection From a Spring Boot Application
  • Java EE 6 Pet Catalog with GlassFish and MySQL

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!