How do I create a database connection pool?

By Wayan in Apache Commons, Commons DBCP 5 Comments

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

package org.kodejava.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 = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    private GenericObjectPool<PoolableConnection> connectionPool = null;

    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 author")) {
            demo.printStatus();

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

        demo.printStatus();
    }

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

        // Creates a PoolableConnectionFactory that will wrap 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 connection objects.
        GenericObjectPoolConfig<PoolableConnection> config = new GenericObjectPoolConfig<>();
        config.setTestOnBorrow(true);
        config.setMaxTotal(10);
        connectionPool = new GenericObjectPool<>(pcf, config);
        pcf.setPool(connectionPool);

        return new PoolingDataSource<>(connectionPool);
    }

    private GenericObjectPool<PoolableConnection> getConnectionPool() {
        return connectionPool;
    }

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

The code shows the following status as output example:

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

Maven Dependencies

<dependencies>
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-dbcp2</artifactId>
        <version>2.10.0</version>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.1.0</version>
    </dependency>
</dependencies>

Maven Central Maven Central

Wayan
Latest posts by Wayan (see all)
Views: 16,052

5 Comments

    • getConnectionPool() is not used anywhere. Where is the method to release the connection back to pool?

      Reply

      • Hi Deepak,

        The getConnectionPool() method is use by the printStatus() method to read connection pool information, at the end of the code snippet above.

        In the code above we use try-with-resources statement, it will automatically closes the resource, in this case the connection object. So when the connection closed it will be returned back to the pool.

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