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.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();

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

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


    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(

        // 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<>();
        connectionPool = new GenericObjectPool<>(pcf, config);

        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

    <!-- -->

Maven Central Maven Central


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

      • 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.

Leave a Reply to Deepak Lalchandani Cancel reply

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