JOOQ & transactions

Question

I've been reading about transactions & jooq but I struggle to see how to implement it in practice.

Let's say I provide JOOQ with a custom ConnectionProvider which happens to use a connection pool with autocommit set to false.

The implementation is roughly:

@Override public Connection acquire() throws DataAccessException {
    return pool.getConnection();
}

@Override public void release(Connection connection) throws DataAccessException {
    connection.commit();
    connection.close();
}

How would I go about wrapping two jooq queries into a single transaction?

It is easy with the DefaultConnectionProvider because there's only one connection - but with a pool I'm not sure how to go about it.

Answer 1

jOOQ 3.4 Transaction API

With jOOQ 3.4, a transaction API has been added to abstract over JDBC, Spring, or JTA transaction managers. This API can be used with Java 8 as such:

DSL.using(configuration)
   .transaction(ctx -> {
       DSL.using(ctx)
          .update(TABLE)
          .set(TABLE.COL, newValue)
          .where(...)
          .execute();
   });

Or with pre-Java 8 syntax

DSL.using(configuration)
   .transaction(new TransactionRunnable() {
       @Override
       public void run(Configuration ctx) {
           DSL.using(ctx)
              .update(TABLE)
              .set(TABLE.COL, newValue)
              .where(...)
              .execute();
       }
   });

The idea is that the lambda expression (or anonymous class) form the transactional code, which:

• Commits upon normal completion
• Rolls back upon exception

The org.jooq.TransactionProvider SPI can be used to override the default behaviour, which implements nestable transactions via JDBC using Savepoints.

A Spring example

The current documentation shows an example when using Spring for transaction handling:

• http://www.jooq.org/doc/latest/manual/getting-started/tutorials/jooq-with-spring/

This example essentially boils down to using a Spring TransactionAwareDataSourceProxy

<!-- Using Apache DBCP as a connection pooling library.
     Replace this with your preferred DataSource implementation -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    init-method="createDataSource" destroy-method="close">
    <property name="driverClassName" value="org.h2.Driver" />
    <property name="url" value="jdbc:h2:~/maven-test" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean>

<!-- Using Spring JDBC for transaction management -->
<bean id="transactionManager"
    class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource" />
</bean>

<bean id="transactionAwareDataSource"
    class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
    <constructor-arg ref="dataSource" />
</bean>

<!-- Bridging Spring JDBC data sources to jOOQ's ConnectionProvider -->
<bean class="org.jooq.impl.DataSourceConnectionProvider" 
      name="connectionProvider">
    <constructor-arg ref="transactionAwareDataSource" />
</bean>

A running example is available from GitHub here:

• https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/jOOQ-spring-example

A Spring and Guice example

Although I personally wouldn't recommend it, some users have had success replacing a part of Spring's DI by Guice and handle transactions with Guice. There is also an integration-tested running example on GitHub for this use-case:

• https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/jOOQ-spring-guice-example