This example show 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 = "root";
    private static final String PASSWORD = "";

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

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

        demo.printStatus();
    }

    public DataSource setUp() {
        // Creates a connection factory object which will be use 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 connections object.
        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 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

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

• Author
• Recent Posts

Wayan

Founder at Kode Java Org

A programmer, runner, recreational diver, live in the island of Bali, Indonesia. Programming in Java, Spring, Hibernate / JPA. You can support me working on this project, buy me a cup of coffee ☕, every little bit helps, thank you 🙏

Latest posts by Wayan (see all)

• How do I build simple search page using ZK and Spring Boot? - March 8, 2023
• How do I calculate days between two dates excluding weekends and holidays? - January 12, 2023
• How do I discover the quarter of a given date? - January 11, 2023