Spring Boot Database Connection Pool

In the previous post, we looked at various JDBC implementation of connection pooling with example code that focused on usage in vanilla Spring. In this post, we will look at how to configure HikariCP, Tomcat JDBC and Apache Commons DBCP 2 connection pool in a Spring Boot application.

Spring Boot Connection Pool Default Selection

The Spring Boot looks up the classpath for the presence of any of the aforementioned connection pool implementations.

By default, Spring Boot uses HikariCP for the obvious reason which includes performance if found in the classpath. But falls back to Tomcat JDBC in the absence of HikarCP if Tomcat JDBC is present in the classpath.

Finally, in the absence of HikariCP and Tomcat JDBC, Spring Boot will choose Apache DBCP2 if present in the classpath.

To signal to Spring Boot our choice of connection pool implementation to use we need to do the following;

1. Exclude the unwanted dependencies in the order of preference as mentioned above.
2. Use profiles (Spring, Maven etc) to instruct spring to load our preferred configuration.

Maven profile –

Define maven profiles with desired configuration including dependencies.

    <profiles>
        <profile>
            <id>hikaricp</id>
            <activation>
                <activeByDefault>false</activeByDefault>
            </activation>
            <properties>
                <activatedProperties>hikaricp</activatedProperties>
            </properties>
            <dependencies>
                <dependency>
                    <groupId>com.zaxxer</groupId>
                    <artifactId>HikariCP</artifactId>
                </dependency>
                <dependency>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-data-jpa</artifactId>
                </dependency>
            </dependencies>
        </profile>
        <profile>
            <id>tomcat</id> 
            <activation>
                <activeByDefault>true</activeByDefault>
            </activation> 
            <properties>
                <activatedProperties>tomcat</activatedProperties>
            </properties>
            <dependencies>
                <dependency>
                    <groupId>org.apache.tomcat</groupId>
                    <artifactId>tomcat-jdbc</artifactId>
                </dependency>
                <dependency>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-data-jpa</artifactId>
                    <exclusions>
                        <exclusion>
                            <groupId>com.zaxxer</groupId>
                            <artifactId>HikariCP</artifactId>
                        </exclusion>
 
                    </exclusions>
                </dependency>
            </dependencies>
 
        </profile>
        <profile>
            <id>dbcp2</id> 
            <properties>
                <activatedProperties>dbcp2</activatedProperties>
            </properties> 
            <dependencies>
                <dependency>
                    <groupId>org.apache.commons</groupId>
                    <artifactId>commons-dbcp2</artifactId>
                 
                </dependency>
                <dependency>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-data-jpa</artifactId>
                    <exclusions>
                        <exclusion>
                            <groupId>com.zaxxer</groupId>
                            <artifactId>HikariCP</artifactId>
                        </exclusion>
                    </exclusions>
                </dependency>
            </dependencies>
            
        </profile>
    </profiles>

Run the application with our preferred profile

mvn spring-boot:run -P dbcp2

Please note, if no profile is selected, the default (user defined active profile) will be used, or no configuration will be loaded.

Secondly, it’s important to note that the DataSource configured by spring Boot will have the default configuration, so it is recommended to adjust the connection pool setting to our needs.

Fine-tuning the connection pool properties of our project need is the key to achieving optimal performance result.

Application Property Files -

Next step is to define our custom pool settings in the property or YAML file.

# ===============================
# = HIKARI CONNECTION POOL 
# ===============================
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.connection-timeout=10000
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle= 1
spring.datasource.hikari.pool-name= example-HikariPool

Include activatedProperties in our pom profile definition and then include that in our application.properties file as shown below

spring.profiles.include=@activatedProperties@

Run the application with the profile of our choice to load the appropriate DataSource.

Usage –

We can use our configured javax.sql.DataSource to perform an operation.

@Service
class ApplicationOperationImpl implements ApplicationOperation {

    private final DataSource dataSource;

    public ApplicationOperationImpl(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public void doSomething(boolean cleanUp) throws SQLException {
        Connection connection = dataSource.getConnection();
        Statement stmt = connection.createStatement();
        log.info("creating a database table with connection " + connection.getCatalog());

        stmt.executeUpdate("CREATE TABLE IF NOT EXISTS test (timer timestamp)");
        log.info("inserting data to created table ......");

        stmt.executeUpdate("INSERT INTO test VALUES (now())");

        ResultSet rs = stmt.executeQuery("SELECT timer FROM test");

        while (rs.next()) {

            log.info("Result : " + rs.getTimestamp("timer") + "\n");
        }

        if (cleanUp) {
            String sql = "DROP TABLE test ";
            log.info("deleting table ....");
            stmt.executeUpdate(sql);
        }
    }

}

Testing –

import javax.sql.DataSource;
import static org.assertj.core.api.Assertions.assertThat;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class ConnectionPoolingApplicationTests {

    @Autowired
    private DataSource dataSource;

    @Test
    void contextLoads() {
        assertThat(dataSource).isNotNull();
    }

}

The example source code is available on GitHub.

Reference

Apache tomcat JDBC pool - https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html

Apache commons DBCP - https://commons.apache.org/proper/commons-dbcp/

HikariCP - https://github.com/brettwooldridge/HikariCP