In the following article I'll try show you how to set up a database connection pool with BoneCP and integrate it to the Spring Boot application. The database will be MySQL, and the source code for this article is based upon the source code of Spring Boot MVC application from one of the previous posts. It's recommended to get familiar with this for the people new to Spring Boot.

What is a connection pool?

Simply speaking It's a technique where instead of creating a database connection every time whenever it's needed the pool of connections is created up front, ready to be used in the future.

The advantages are such that:

  • it reduces latency associated with creating and closing the connection,
  • created connections can be re-used by different threads (i.e. servlet serving requests for each user),
  • the size of the pool can be managed, so instead of failing due to hitting the database connection limit, the thread can simply wait for the connection to be available.

Connection pool can be made so it "adapts" itself to the current usage. A minimum number of opened connections can be set. As the demand gets higher, the more connections are added to the pool, up to the maximum which could be set depending on the capacity of the underlying infrastructure.

BoneCP is an implementation of such connection pool for databases that are supported by JDBC.

BoneCP vs Hibernate

This heading is kindof stupid, but I understand there might be a confusion. Hibernate is an JPA provider, so it's mostly an API to "talk" to the database and map the data to objects (ORM). It connects to the DataSource to perform it's operations.

Simple DataSource could be a JDBC connection factory that just creates new connections. BoneCP on the other hand acts as a DataSource that just does some more than that.

Maven

Dependencies needed for this are BoneCP and MySQL driver for JDBC. For as long as it's Spring Boot application we don't need Hibernate, as it comes by default thanks to spring-boot-starter-data-jpa. Things to add to pom.xml:

<!-- BoneCP -->

<dependency>
    <groupId>com.jolbox</groupId>
    <artifactId>bonecp</artifactId>
    <version>0.8.0.RELEASE</version>
</dependency>

<!-- MySql Java Connector -->

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.30</version>
</dependency>

If you modify the code from this article by hand, just remember to remove HSQLDB dependency, as we're using MySQL instead here.

Configuring BoneCP as a DataSource

We need a @Configuration class for this, that will expose a DataSource Bean:

@Configuration
public class BoneCPConfig {

    @Value("${bonecp.url}")
    private String jdbcUrl;

    @Value("${bonecp.username}")
    private String jdbcUsername;

    @Value("${bonecp.password}")
    private String jdbcPassword;

    @Value("${bonecp.driverClass}")
    private String driverClass;

    @Value("${bonecp.idleMaxAgeInMinutes}")
    private Integer idleMaxAgeInMinutes;

    @Value("${bonecp.idleConnectionTestPeriodInMinutes}")
    private Integer idleConnectionTestPeriodInMinutes;

    @Value("${bonecp.maxConnectionsPerPartition}")
    private Integer maxConnectionsPerPartition;

    @Value("${bonecp.minConnectionsPerPartition}")
    private Integer minConnectionsPerPartition;

    @Value("${bonecp.partitionCount}")
    private Integer partitionCount;

    @Value("${bonecp.acquireIncrement}")
    private Integer acquireIncrement;

    @Value("${bonecp.statementsCacheSize}")
    private Integer statementsCacheSize;

    @Bean(destroyMethod = "close")
    public DataSource dataSource() {
        BoneCPDataSource dataSource = new BoneCPDataSource();
        dataSource.setDriverClass(driverClass);
        dataSource.setJdbcUrl(jdbcUrl);
        dataSource.setUsername(jdbcUsername);
        dataSource.setPassword(jdbcPassword);
        dataSource.setIdleConnectionTestPeriodInMinutes(idleConnectionTestPeriodInMinutes);
        dataSource.setIdleMaxAgeInMinutes(idleMaxAgeInMinutes);
        dataSource.setMaxConnectionsPerPartition(maxConnectionsPerPartition);
        dataSource.setMinConnectionsPerPartition(minConnectionsPerPartition);
        dataSource.setPartitionCount(partitionCount);
        dataSource.setAcquireIncrement(acquireIncrement);
        dataSource.setStatementsCacheSize(statementsCacheSize);
        return dataSource;
    }

}

Here we have a bunch of parameters coming from external .property file, so it may be externally configured. Then the instance of BoneCPDataSource is created, and after setting properties, it's returned. For explanation of what they do, see below.

Setting the properties

We will add the following properties to src/main/resources/application.properties:

# JPA
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect

This tells Hibernate which SQL dialect should it use to talk to the database. No surprise it's MySQL dialect here.

# BoneCP
bonecp.driverClass=com.mysql.jdbc.Driver
bonecp.url=jdbc:mysql://localhost:3306/test?useUnicode=yes&characterEncoding=UTF-8
bonecp.username=test
bonecp.password=test
bonecp.idleMaxAgeInMinutes=240
bonecp.idleConnectionTestPeriodInMinutes=60
bonecp.maxConnectionsPerPartition=10
bonecp.minConnectionsPerPartition=1
bonecp.partitionCount=2
bonecp.acquireIncrement=5
bonecp.statementsCacheSize=100

The meaning of those properties is as follows:

  • driverClass - indicates which database driver should be used, this is the one coming from mysql-connector-java.
  • url - JDBC url to the data source. The way it should look like is usually documented by the JDBC driver, here is an example for MySQL with additional parameters forcing connection charset to be UTF-8. It connects to MySQL instance on localhost, port 3306 and test database.
  • username, password - a name and password of the database user
  • idleMaxAgeInMinutes - when there are idle connections in the pool, for how long should they be maintained until they are closed (default: 240)
  • idleConnectionTestPeriodInMinutes - sets the interval for testing idle connections (default: 240, here: 60). When a connection is idle BoneCP sends a test query to the database to see if the connection is active. This also prevents database from closing the connections due to the timeout.
  • maxConnectionsPerPartition - maximum number of the connections to create
  • minConnectionPerPartition - minimum number of connections that is maintained even when they are not used
  • partitionCount - simple speaking BoneCP uses connection partitions to help with multi-threading and this indicates how many of them should be created (default: 1, here: 2, recommended: 2-4). What is important here, is that the max. number of connections to the database in total will be maxConnectionsPerPartition x partitionCount, because max/min limits are per partition.
  • acquireIncrement - say when there are few idle connections left for use in the pool, and the demand is rising, how many additional connections should added to the pool. This works until the maximum limit of connections is reached.
  • statementsCacheSize - how many statements should be cached.

Most of those settings depend on the application itself and how do you expect it will be used, so to really optimize the pool for your needs the proper observation is needed.

Closing remarks

Connection pooling (either for database or the other resources) is generally a good idea for performance reasons.

You can play with the source code here and see for yourself. If you have a lot of time, you can even simulate the demand with JMeter and see how different pool properties behave in different scenarios, for example when the demand is high but steady, or when it rapidly fluctuates.

And if you'd like to change the database to something different, this should only be the matter of:

  • adding JDBC driver to Maven dependencies
  • setting up the dialect for Hibernate in application.properties (spring.jpa.database-platform)
  • setting up BoneCP to use it in application.properties (bonecp.driverClass, bonecp.url)

This site uses cookies. By continuing to browse the site, you are agreeing to our use of cookies. Find out more in Privacy and Cookies Policy.