How do I create a database connection pool?

This example show you how to create a connection pool implementation using the Apache Commons DBCP library.

package org.kodejava.example.commons.dbcp;

import org.apache.commons.dbcp2.*;
import org.apache.commons.pool2.impl.GenericObjectPool;
import org.apache.commons.pool2.impl.GenericObjectPoolConfig;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class ConnectionPoolExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    private GenericObjectPool<PoolableConnection> connectionPool = null;

    public DataSource setUp() {
        // Creates a connection factory object which will be use by
        // the pool to create the connection object. We passes the
        // JDBC url info, username and password.
        ConnectionFactory cf = new DriverManagerConnectionFactory(
            ConnectionPoolExample.URL,
            ConnectionPoolExample.USERNAME,
            ConnectionPoolExample.PASSWORD);

        // Creates a PoolableConnectionFactory that will wraps the
        // connection object created by the ConnectionFactory to add
        // object pooling functionality.
        PoolableConnectionFactory pcf = new PoolableConnectionFactory(cf, null);
        pcf.setValidationQuery("SELECT 1");

        // Creates an instance of GenericObjectPool that holds our
        // pool of connections object.
        GenericObjectPoolConfig config = new GenericObjectPoolConfig();
        config.setMaxTotal(10);
        connectionPool = new GenericObjectPool<>(pcf);
        pcf.setPool(connectionPool);

        return new PoolingDataSource<>(connectionPool);
    }

    private GenericObjectPool getConnectionPool() {
        return connectionPool;
    }

    public static void main(String[] args) throws Exception {
        ConnectionPoolExample demo = new ConnectionPoolExample();
        DataSource dataSource = demo.setUp();
        demo.printStatus();

        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement("SELECT * FROM authors")) {
            demo.printStatus();

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println("Name: " + rs.getString("name"));
            }
        }

        demo.printStatus();
    }

    /**
     * Prints connection pool status.
     */
    private void printStatus() {
        System.out.println("Max   : " + getConnectionPool().getNumActive() + "; " +
                "Active: " + getConnectionPool().getNumActive() + "; " +
                "Idle  : " + getConnectionPool().getNumIdle());
    }
}

The code show the following status as output example:

Max   : 0; Active: 0; Idle  : 0
Max   : 1; Active: 1; Idle  : 0
Name: Raoul-Gabriel Urma
Name: Mario Fusco
Name: Alan Mycroft
Max   : 0; Active: 0; Idle  : 1

Maven Dependencies

<!-- https://search.maven.org/remotecontent?filepath=org/apache/commons/commons-dbcp2/2.6.0/commons-dbcp2-2.6.0.jar -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.6.0</version>
</dependency>
<!-- https://search.maven.org/remotecontent?filepath=org/apache/commons/commons-pool2/2.6.2/commons-pool2-2.6.2.jar -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-pool2</artifactId>
    <version>2.6.2</version>
</dependency>
<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

Maven Central
Maven Central
Maven Central

Wayan Saryada

Founder at Kode Java Org
I am a programmer, a runner, a recreational diver, currently live in the island of Bali, Indonesia. Mostly programming in Java, Spring Framework, Hibernate / JPA. You can support my works by donating here. Thank you 🥳

3 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.