AutoCommit did not work with jOOQ 2.6.1 and Tomcat pooling

Question

I use jOOQ 2.6.1 and the pooling from Tomcat (docs) and I have set autocommit to true.

PoolProperties p = new PoolProperties();
p.setDefaultAutoCommit(true); 
p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;" +
                      "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
p.setRemoveAbandoned(true);
p.setRemoveAbandonedTimeout(10);

then I created an Apache DataSource object:

org.apache.tomcat.jdbc.pool.DataSource dataSource = new DataSource
dataSource.setPoolProperties(p);

and I use the LazyConnectionDataSourceProxy from Spring:

LazyConnectionDataSourceProxy lazyConnection = new LazyConnectionDataSourceProxy(dataSource);

From jOOQ 2.6.1 is use the Factory:

public Factory createFactory() {
    Settings settings = new Settings();
    settings.getExecuteListeners().add(
            "de.si.srv.data.SpringExceptionTranslationExecuteListener");
    return new Factory(dataSource, SQLDialect.POSTGRES, settings);
}

if I do for example a select like this:

createFactory().select().from().fetch()

... jOOQ should close the connection automatically! But jOOQ does not close the connection.. All connections are after some time Abandoned.

Does anybody know a solution for my problem? I want that jOOQ closes the connections automatically! Or should I use an other pooling framework?

Which works better with jOOQ?

Answer 1

There have been quite a few changes and fixes around that area in jOOQ 3.x, the most important one being:

• https://github.com/jOOQ/jOOQ/issues/2373

or should i use an other pooling framework ?

I don't think another pooling framework will help, here.

If you want to continue working with jOOQ 2.6.1 (instead of upgrading to jOOQ 3.2), I guess you might need to either:

• Patch jOOQ and fix this issue
• Handle the DataSource / Connection lifecycle yourself and pass a Connection to jOOQ