jooq logohttp://www.jooq.org

Using jOOQ with Spring Transactions

Update 10.02.2014: Recently, Lukas from the jOOQ development team has created a sample GitHub project on how to set Spring + Guice up. Please, refer comments. Recently, we decided to introduce jOOQ to our project. Mostly, because we wanted strong-typed SQL queries. However, since customer was using his own framework and wanted to keep it as lightweight as possible, we also used jOOQ’s built-in DataSource object management. It works fine, but it has an important drawback – it does not support declarative transaction management. Our main goal was to implement transaction management in a way that won’t enforce us to do many changes in existing code. A natural idea was to use some well-known solution that provides transaction management. The choice was Spring JDBC. We were also using Google Guice as our DI container.

Spring JDBC transactions don’t work with Google Guice and jOOQ

Our first step was to configure DataSource and DataSourceTransactionManager as Google Guice’s providers:

[java]@Provides
@Singleton
DataSource provideDataSource(IExternalSettings settings) {
    Jdbc3PoolingDataSource dataSource = new Jdbc3PoolingDataSource();
    // configuring DataSource<br />    return dataSource;
}

@Provides
@Singleton
DataSourceTransactionManager provideDataSourceTransactionManager(DataSource dataSource) {
    return new DataSourceTransactionManager(new TransactionAwareDataSourceProxy(dataSource));
}[/java]

Then we injected our transaction manager to the PersistenceFacade:

[java]
@Inject
public PersistenceFacade(final DataSourceTransactionManager transactionManager) {
    this.dataSource = transactionManager.getDataSource();
    this.transactionManager = transactionManager;
}
[/java]

The DataSource was used while creating jOOQ’s factory, like new Factory(dataSource, ...). The next part was to use Google Guice’s interceptors to be able to use @Transactional annotation. The interceptor we created was:

[java]
class TransactionalMethodInterceptor implements MethodInterceptor {
    @Inject
    private DataSourceTransactionManager transactionManager;

    @Override
    public Object invoke(final MethodInvocation invocation) throws Throwable {
        DefaultTransactionDefinition transactionDefinition = new DefaultTransactionDefinition();
        TransactionStatus transaction = transactionManager.getTransaction(transactionDefinition);
        try {
            Object result = invocation.proceed();
            transactionManager.commit(transaction);
            return result;
        } catch (Exception e) {
            transactionManager.rollback(transaction);
            throw e;
        }
    }
}
[/java]

Of course, we also had to configure the interceptor in configure() method of Guice module

[java]
TransactionalMethodInterceptor transactionalMethodInterceptor = new TransactionalMethodInterceptor();
requestInjection(transactionalMethodInterceptor);
bindInterceptor(Matchers.any(), Matchers.annotatedWith(Transactional.class), transactionalMethodInterceptor);
[/java]

jOOQ still uses non-transactional connection objects

We annotated appropriate methods with the @Transactional annotation and we thought we’re ready to go. It looks like it should work, doesn’t it? Well, not so fast, apparently. The transaction basically weren’t rolled back. Using a debugger, we could see that the control flow reaches the interceptor rollback() call, but it has no real effect! We even posted a Stack Overflow question, but got no direct replies. However, one of the SO users (tx, Alen!) put us on the right track, suggesting that getConnection() method may return different connections. And that actually was true! Now we know the reason of this weird behavior, but how to change it?

Using jOOQ’s ExecuteListener for getting Spring-managed connections

Knowing the issue, we started investigating. Quite soon, we’ve found this answer and this snippet. It appeared that jOOQ’s ExecuteListener is a key to resolving the issue. It allows us to catch many events related to queries, routines and result sets. What is this snippet responsible for? It gets a Spring-managed transactional connection and puts it in jOOQ’s ExecuteContext each time a query starts. Then, it closes the statement if any exception is being thrown. It also re-throws the exception as Spring’s data access strategy-agnostic exception using SQLExceptionTranslator. Thanks to that, we’re working with transaction-aware connections and handle them correctly. So, we registered the listener in a factory

[java]
public Factory createFactory() {
    Settings settings = new Settings();
    settings.getExecuteListeners().add(
            "full.qualified.name.SpringExceptionTranslationExecuteListener");
    return new Factory(dataSource, SQLDialect.POSTGRES, settings);
}
[/java]

We also removed the DataSourceUtils.releaseConnection(con, dataSource) part from SpringExceptionTranslationExecuteListener.exception method, because connections are already being closed in Guice’s interceptor. So the final version of the method looks like

[java]
@Override
public void exception(ExecuteContext ctx) {
    SQLException ex = ctx.sqlException();
    Statement stmt = ctx.statement();
    Connection con = ctx.getConnection();
    DataSource dataSource = ctx.getDataSource();
    JdbcUtils.closeStatement(stmt);
    ctx.exception(getExceptionTranslator(dataSource).translate("jOOQ", ctx.sql(), ex));
}
[/java]

That’s all, it started to work properly! We managed to integrate jOOQ, Spring JDBC and Google Guice in nice and elegant way.

/by