mysql - jooq连接池不释放连接

Question

我们尝试在 jooq 环境中使用 dbcp 和 c3p0 作为数据库连接池。两者都适用于 SELECT 语句，但 CREATE 和 UPDATE 语句不会释放连接。

我们初始化了 dbcp，如下所示:

public static DataSource setupDataSource(String dbUrl, String dbUserName, String dbPassword) {
    ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(dbUrl, dbUserName, dbPassword);
    PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
    ObjectPool<PoolableConnection> connectionPool = new GenericObjectPool<>(poolableConnectionFactory);
    poolableConnectionFactory.setPool(connectionPool);
    PoolingDataSource<PoolableConnection> dataSource = new PoolingDataSource<>(connectionPool);
    return dataSource;
}

然后我们获得每个查询的连接:

Connection dbConnection = null;
    try {
        dbConnection = dataSource.getConnection();
} ....
DSLContext dslContext = DSL.using(connection, dialect);

Out create 语句如下所示:

protected final DSLContext jooq;
public E add(E entity) throws Exception {
    E transformedEntity = null;
    try {
        R persisted;
        persisted = jooq.insertInto(transformator.getTable())
                .set(transformator.createRecord(entity))
                .returning()
                .fetchOne();

        transformedEntity = transformator.getEntityFromTableRecord(persisted);
    } catch (DataAccessException e) {
        ...
    }
    return transformedEntity;
}

最后我们关闭连接:

dbConnection.close();

问题是连接保持打开状态，连接池已满后无法创建连接。我需要关闭报表和结果集吗？如果是，我该如何用 jooq 做到这一点？

Answer 1

使用 jOOQ 执行此操作的最简单方法是将数据源直接传递给 jOOQ。而不是:

Connection dbConnection = null;
    try {
        dbConnection = dataSource.getConnection();
} ....
DSLContext dslContext = DSL.using(connection, dialect);

...写:

DSLContext dslContext = DSL.using(dataSource, dialect);

这样，jOOQ 将为您管理连接生命周期。否则，我怀疑您只是在某些情况下连接仍然泄漏并且未正确关闭