Using Apache DBCP2 to create a Connection Pool

If you want to scale your database queries but limit the consumption of database resources you can use the Apache DBCP library (Database Connection Pools) to, as its own name explains, create a limited pool of connections which will be automatically maintained e.g. keep the connection open, take advantage of the pool to run as many queries as possible at the same time and recreate it when necessary.

You can add it to your maven project by adding the following dependency in your pom.xml:


    org.apache.commons
    commons-dbcp2
    2.0.1

It is very easy to use, you just need to create a BasicDataSource instance, set up the connection config and define the pool size. After that, ask for a connection whenever you need one and the BasicDataSource object will supply it:

public MySQLDataPool() {
    try {
        this.connectionPool = new BasicDataSource();
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        connectionPool.setUrl(DB_URL);
        connectionPool.setUsername(DB_USER);
        connectionPool.setPassword(DB_PASS);
        connectionPool.setDriverClassName("com.mysql.jdbc.Driver");
        connectionPool.setInitialSize(CONN_POOL_SIZE);
    } catch (Exception e) {
        e.printStackTrace();
    }

}

public Connection getConnection() throws SQLException {
    return connectionPool.getConnection();
}

You can also take advantage of the BasicDataSource class in order to have connection pools for more than one database. An easy wrapping class example:

public class MySQLDataPool {

    private BasicDataSource connectionPool;

    private static String DB1_URL;
    private static String DB2_URL;
    private static String DB1_USER;
    private static String DB2_USER;
    private static String DB1_PASS;
    private static String DB2_PASS;
    private static final int CONN_POOL_SIZE = 10;

    public enum Database {
        DB1, DB2
    }

    public MySQLDataPool(Database db) {
        try {
            this.connectionPool = new BasicDataSource();
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connectionPool.setUrl(db == Database.DB1 ? DB1_URL : DB2_URL);
            connectionPool.setUsername(db == Database.DB1 ? DB1_USER : DB2_USER);
            connectionPool.setPassword(db == Database.DB1 ? DB1_PASS : DB2_PASS);
            connectionPool.setDriverClassName("com.mysql.jdbc.Driver");
            connectionPool.setInitialSize(CONN_POOL_SIZE);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public Connection getConnection() throws SQLException {
        return connectionPool.getConnection();
    }

}

This way you can have two pools, one for each database:

MySQLDataPool db1DataPool = new MySQLDataPool(Database.DB1);
MySQLDataPool db2DataPool = new MySQLDataPool(Database.DB2);

Now you know how to create connection pools for MySQL databases.


This is the second post of a series of three. The first part was An introduction to using JDBC to connect with MySQL in Java. In the last one I’ll show how to use reflection to use a common mapper for all of your select statements.

If you have any question, correction or anything to add to this post, please feel free to leave a comment or get in touch!

Leave a Reply

Your email address will not be published. Required fields are marked *


*