Implementing a Custom MySQL Connection Pool with Apache Commons Pool2 in Java

After experimenting with the Apache commons-pool2 library and finding little performance improvement in HTTP tests, the author realized the library is ideal for creating custom connection pools such as Redis's Jedis pool.

The author defines a poolable object com.funtester.db.mysql.FunMySql, a lightweight wrapper around a single MySQL connection, and provides its full implementation.

package com.funtester.db.mysql;

import com.funtester.base.interfaces.IMySqlBasic;
import com.funtester.config.SqlConstant;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * mysql操作的基础类
 * <p>用于存储数据，多用于爬虫</p>
 */
public class FunMySql extends SqlBase implements IMySqlBasic {
    String url;
    String database;
    String user;
    String password;
    Connection connection;
    Statement statement;

    /**
     * 私有构造方法
     * @param url      连接地址,包括端口
     * @param database 库
     * @param user     用户名
     * @param password 密码
     */
    public FunMySql(String url, String database, String user, String password) {
        this.url = url;
        this.database = database;
        this.user = user;
        this.password = password;
        getConnection(database);
    }

    /** 初始化连接 */
    @Override
    public void getConnection() {
        getConnection(EMPTY);
    }

    /** 执行sql语句，非query语句，并不关闭连接 */
    @Override
    public void executeUpdateSql(String sql) {
        SqlBase.executeUpdateSql(connection, statement, sql);
    }

    /** 查询功能 */
    @Override
    public ResultSet executeQuerySql(String sql) {
        return SqlBase.executeQuerySql(connection, statement, sql);
    }

    /** 关闭query连接 */
    @Override
    public void over() {
        SqlBase.close(connection, statement);
    }

    @Override
    public void getConnection(String database) {
        if (connection == null)
            connection = SqlBase.getConnection(SqlConstant.FUN_SQL_URL.replace("ip", url).replace("database", database), user, password);
        if (statement == null) statement = SqlBase.getStatement(connection);
    }
}

The pool factory class creates and wraps FunMySql instances, handling object destruction by closing the underlying connection.

private class FunTester extends BasePooledObjectFactory<FunMySql> {
    @Override
    FunMySql create() throws Exception {
        return new FunMySql(url, database, user, password);
    }

    @Override
    PooledObject<FunMySql> wrap(FunMySql obj) {
        return new DefaultPooledObject<FunMySql>(obj);
    }

    @Override
    void destroyObject(PooledObject<FunMySql> p) throws Exception {
        p.getObject().over();
        super.destroyObject(p);
    }
}

The custom pool com.funtester.db.mysql.MysqlPool configures a GenericObjectPool with max total, idle limits, and wait times, then provides methods to borrow and return FunMySql objects.

class MysqlPool extends PoolConstant {
    private static final Logger logger = LogManager.getLogger(MysqlPool.class);
    String url;
    String database;
    String user;
    String password;
    private GenericObjectPool<FunMySql> pool;

    MysqlPool(String url, String database, String user, String password) {
        this.url = url;
        this.database = database;
        this.user = user;
        this.password = password;
        init();
    }

    def init() {
        GenericObjectPoolConfig poolConfig = new GenericObjectPoolConfig();
        poolConfig.setMaxTotal(MAX);
        poolConfig.setMinIdle(MIN_IDLE);
        poolConfig.setMaxIdle(MAX_IDLE);
        poolConfig.setMaxWaitMillis(MAX_WAIT_TIME);
        poolConfig.setMinEvictableIdleTimeMillis(MAX_IDLE_TIME);
        pool = new GenericObjectPool<FunMySql>(new FunTester(), poolConfig);
    }
}

A thin API wrapper hides the pooling details from callers. The borrow() method obtains a FunMySql from the pool, while back() returns it. Convenience methods execute() and query() run update and query statements respectively, ensuring the connection is always returned.

def borrow() {
    try {
        return pool.borrowObject()
    } catch (e) {
        logger.warn("获取${JSONObject.class} 失败", e)
    } finally {
        new JSONObject()
    }
}

def back(FunMySql funMySql) {
    pool.returnObject(funMySql)
}

def execute(def sql) {
    def driver = borrow()
    try {
        driver.executeUpdateSql(sql)
    } catch (e) {
        logger.warn("执行:{}失败", sql)
    } finally {
        back(driver)
    }
}

def query(def sql) {
    def driver = borrow()
    try {
        return driver.executeQuerySql(sql)
    } catch (e) {
        logger.warn("执行:{}失败", sql)
    } finally {
        back(driver)
    }
}

Images illustrate the testing process, and a list of related articles and community resources is provided at the end.