Database Connection Pool in Spring

In a database driven application, creating a new connection object is a costly operation (cumulative time in establishing connections, network traffic, burden on the garbage collector etc). 

A typical database connection cycle will follow the following steps;

• An application requests for a database connection (via a DataSource using a database driver).
• A new database connection is created, and a TCP socket is opened for read/write operations.
• The application then performs read and write operation to the database.
• When a database connection is no longer need, it is closed.
• The TCP socket is finally closed.

For most enterprise database intense application, the cost of creating new database connection, the network traffic will impact on the overall performance of the application without employing a database connection pooling pattern.

Database Connection pooling is a pattern used by software applications to connect to databases using already created set of reusable connection objects. The connection pool acts as a cache of the open database connections.

When a new connection is requested, an existing available connection is retrieved from the pool instead of creating a new one. Similarly, a connection is placed back to the pool when it is no longer needed.

Connection pooling reduces the database management system I/O overhead of connecting to a database by decreasing network traffic and ensuring that new connections are only created when there is no available connection in the pool.

Every new connection requires some database resources, so in order to prevent the database resources from being depleted, the connection pool acts a circular or bounded buffer for incoming new connection requests.

JDBC Connection pooling

There are several JDBC connection pooling implementations but the most popularly used ones are HikariCP, Tomcat JDBC Connection Pool, Apache Commons DBCP 2 and C3P0.

1. C3P0

c3p0 is a mature, highly concurrent JDBC Connection pooling library, with support for caching and reuse of PreparedStatements.

Using C3P0

        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.5</version>
        </dependency>

C3P0 javax.sql.DataSource config

    @Bean
    public DataSource dataSource() {
        ComboPooledDataSource pooledDataSource = new ComboPooledDataSource();
        pooledDataSource.setJdbcUrl(configProperties.getUrl());
        pooledDataSource.setUser(configProperties.getUsername());
        pooledDataSource.setPassword(configProperties.getPassword());
       
        return pooledDataSource;
    }

2. Apache Commons DBCP 2.

DBCP 2 is based on Commons Pool 2 and provides increased performance, JMX support as well as numerous other new features compared to DBCP 1.x.

Using Apache DBCP 2

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.7.0</version>
        </dependency>

Apache DBCP 2  javax.sql.DataSource​​​​​​​ config

    @Bean
    public DataSource dataSource() {
        BasicDataSource dbcpDataSource = new BasicDataSource();
        dbcpDataSource.setUrl(configProperties.getUrl());
        dbcpDataSource.setUsername(configProperties.getUsername());
        dbcpDataSource.setPassword(configProperties.getPassword());
        dbcpDataSource.setInitialSize(1);
        return dbcpDataSource;
    }

3. Tomcat JDBC Connection Pool

The JDBC Connection Pool org.apache.tomcat.jdbc.pool is a replacement or an alternative to the Apache Commons DBCP connection pool.

Apache Commons DBCP 1.x is single threaded, slow, uses static interfaces and over 60 Java classes and hence the need for the Tomcat JDBC connection pool that is simpler and offers numerous feature advantages over Apache Commons DBCP connection pool as discussed on apache tomcat website.

Using Tomcat JDBC Connection Pool

        <dependency>
            <groupId>org.apache.tomcat</groupId>
            <artifactId>tomcat-jdbc</artifactId>
            <version>9.0.36</version>
        </dependency>

Tomcat JDBC Connection Pool javax.sql.DataSource​​​​​​​ config

    @Bean
    public DataSource dataSource() {

        PoolProperties p = new PoolProperties();
        p.setUrl(configProperties.getUrl());
        p.setDriverClassName("com.mysql.cj.jdbc.Driver");
        p.setUsername(configProperties.getUsername());
        p.setPassword(configProperties.getPassword());

        //more setting
        p.setJmxEnabled(true);
        p.setTestWhileIdle(false);
        p.setTestOnBorrow(true);
        p.setValidationQuery("SELECT 1");
        p.setTestOnReturn(false);
        p.setValidationInterval(30000);
        p.setTimeBetweenEvictionRunsMillis(30000);
        p.setMaxActive(100);
        p.setInitialSize(10);
        p.setMaxWait(10000);
        p.setRemoveAbandonedTimeout(60);
        p.setMinEvictableIdleTimeMillis(30000);
        p.setMinIdle(10);
        p.setLogAbandoned(true);
        p.setRemoveAbandoned(true);
        p.setJdbcInterceptors(
                "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
                + "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");

        org.apache.tomcat.jdbc.pool.DataSource ds = new org.apache.tomcat.jdbc.pool.DataSource(p);
        return ds;

    }

4. HikariCP

A lightweight, fast, reliable and arguable the most recommended high performance, production ready JDBC connection pooling implementation.

Using HikariCP

        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>3.4.5</version>
        </dependency>

HikariCP javax.sql.DataSource​​​​​​​ config

    @Bean
    public DataSource dataSource() {

        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(configProperties.getUrl());
        config.setUsername(configProperties.getUsername());
        config.setPassword(configProperties.getPassword());

        //more config
        config.setPoolName("example-pool");
        config.addDataSourceProperty("cachePrepStmts", true);
        config.addDataSourceProperty("prepStmtCacheSize", 250);
        config.addDataSourceProperty("prepStmtCacheSqlLimit", 2048);
        config.addDataSourceProperty("useServerPrepStmts", true);
        config.addDataSourceProperty("rewriteBatchedStatements", true);
        config.addDataSourceProperty("cacheResultSetMetadata", true);
        config.addDataSourceProperty("cacheServerConfiguration", true);
        config.addDataSourceProperty("elideSetAutoCommits", true);
        config.addDataSourceProperty("maintainTimeStats", false);

        HikariDataSource ds = new HikariDataSource(config);
        return ds;
    }

Testing the Connections 

We can test our configured javax.sql.DataSource as configured in the profiles.

@SpringJUnitConfig({ ConfigProperties.class, PoolConfig.class})
public class PoolConfigTest {

    @Autowired
    private DataSource dataSourceHikariCP;
    @Autowired
    private DataSource dataSourceDBCP;
    @Autowired
    private DataSource dataSourceCp30;
    @Autowired
    private DataSource dataSourceTomcatJdbc;


    /**
     * Test of dataSourceCp30 method, of class PoolConfig.
     */
    @Test
    public void testDataSourceCp30() {

        assertThat(dataSourceCp30).isNotNull();

        assertThat(dataSourceCp30).isInstanceOf(ComboPooledDataSource.class);

    }

    /**
     * Test of dataSourceDBCP method, of class PoolConfig.
     */
    @Test
    public void testDataSourceDBCP2() {

        assertThat(dataSourceDBCP).isNotNull();

        assertThat(dataSourceDBCP).isInstanceOf(BasicDataSource.class);

    }

    /**
     * Test of dataSourceHikariCP method, of class PoolConfig.
     */
    @Test
    public void testDataSourceHikariCP() {
        assertThat(dataSourceHikariCP).isNotNull();

        assertThat(dataSourceHikariCP).isInstanceOf(HikariDataSource.class);

    }

    /**
     * Test of dataSourceTomcatJdbc method, of class PoolConfig.
     * @throws java.sql.SQLException
     */
    @Test
    public void testDataSourceTomcatJdbc() throws SQLException {

        assertTrue(dataSourceTomcatJdbc.getConnection().isValid(1));

        assertThat(dataSourceTomcatJdbc).isInstanceOf(org.apache.tomcat.jdbc.pool.DataSource.class);

    }

}

The example code does not use spring boot and 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