jooq connection pooling does not release connection

Question

we tried to use dbcp and c3p0 as database connection pooling in our jooq environment. Both work fine for SELECT statements but CREATE and UPDATE statements does not release the connection.

We initialized the dbcp like:

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;
}

Then we get connection for each query:

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

Out create statement looks like:

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;
}

And in the end we close the connection with:

dbConnection.close();

The problem is that the connections stay open and after the connection pool is full no connection can be created. Do I need to close the statements and resultsets? And if yes, how can I do this with jooq?

Answer 1

The easiest way to do this with jOOQ is to pass the data source directly to jOOQ. Instead of:

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

... write:

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

That way, jOOQ will manage connection lifecycles for you. Otherwise, I suspect you simply have some situations where your connection still leaks and isn't closed properly