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.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

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

public class ConnectionPoolExample {
    public static final String DRIVER = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost/kodejava";
    public static final String USERNAME = "kodejava";
    public static final String PASSWORD = "kodejava123";

    private GenericObjectPool connectionPool = null;

    public DataSource setUp() throws Exception {
        // Load JDBC Driver class.
        Class.forName(ConnectionPoolExample.DRIVER).newInstance();

        // Creates an instance of GenericObjectPool that holds our
        // pool of connections object.
        connectionPool = new GenericObjectPool();
        connectionPool.setMaxActive(10);

        // 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, connectionPool,
                        null, null, false, true);
        return new PoolingDataSource(connectionPool);
    }

    public GenericObjectPool getConnectionPool() {
        return connectionPool;
    }

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

        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = dataSource.getConnection();
            demo.printStatus();

            stmt = conn.prepareStatement("SELECT * FROM m_users");
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println("Username: " + rs.getString("username"));
            }
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }

        demo.printStatus();
    }

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

The code show the following status as output example:

Max   : 10; Active: 0; Idle  : 0
Max   : 10; Active: 1; Idle  : 0
Username: wsaryada
Username: jdoe
Max   : 10; Active: 0; Idle  : 1
Wayan Saryada

Wayan Saryada

A programmer, runner, recreational diver, currently living in the island of Bali, Indonesia. Mostly programming in Java, creating web based application with Spring Framework, JPA, etc. If you need help on Java programming you can hire me on Fiverr.
Wayan Saryada

3 Comments

Leave a Reply