JOOQ & transactions

Easiest way,(I have found) to use Spring Transactions with jOOQ, is given here: http://blog.liftoffllc.in/2014/06/jooq-and-transactions.html

Basically we implement a ConnectionProvider that uses org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(ds) method to find and return the DB connection that holds transaction created by Spring.

Create a TransactionManager bean for your DataSource, example shown below:

  <bean
   id="dataSource"
   class="org.apache.tomcat.jdbc.pool.DataSource"
   destroy-method="close"

   p:driverClassName="com.mysql.jdbc.Driver"
   p:url="mysql://locahost:3306/db_name"
   p:username="root"
   p:password="root"
   p:initialSize="2"
   p:maxActive="10"
   p:maxIdle="5"
   p:minIdle="2"
   p:testOnBorrow="true"
   p:validationQuery="/* ping */ SELECT 1"
  />

  <!-- Configure the PlatformTransactionManager bean -->
  <bean
   id="transactionManager"
   class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
   p:dataSource-ref="dataSource"
  />
  <!-- Scan for the Transactional annotation -->
  <tx:annotation-driven/>

Now you can annotate all the classes or methods which uses jOOQ's DSLContext with

@Transactional(rollbackFor = Exception.class)

And while creating the DSLContext object jOOQ will make use of the transaction created by Spring.

This is probably not the best way but it seems to work. The caveat is that it is not the release but the commit method which closes the connection and returns it to the pool, which is quite confusing and could lead to issues if some code "forgets" to commit...

So the client code looks like:

final PostgresConnectionProvider postgres =
            new PostgresConnectionProvider("localhost", 5432, params.getDbName(), params.getUser(), params.getPass())

private static DSLContext sql = DSL.using(postgres, SQLDialect.POSTGRES, settings);

//execute some statements here
sql.execute(...);

//and don't forget to commit or the connection will not be returned to the pool
PostgresConnectionProvider p = (PostgresConnectionProvider) sql.configuration().connectionProvider();
p.commit();

And the ConnectionProvider:

public class PostgresConnectionProvider implements ConnectionProvider {
    private static final Logger LOG = LoggerFactory.getLogger(PostgresConnectionProvider.class);

    private final ThreadLocal<Connection> connections = new ThreadLocal<>();
    private final BoneCP pool;

    public PostgresConnectionProvider(String serverName, int port, String schema, String user, String password) throws SQLException {
        this.pool = new ConnectionPool(getConnectionString(serverName, port, schema), user, password).pool;
    }

    private String getConnectionString(String serverName, int port, String schema) {
        return "jdbc:postgresql://" + serverName + ":" + port + "/" + schema;
    }

    public void close() {
        pool.shutdown();
    }

    public void commit() {
        LOG.debug("Committing transaction in {}", Thread.currentThread());
        try {
            Connection connection = connections.get();
            if (connection != null) {
                connection.commit();
                connection.close();
                connections.set(null);
            }
        } catch (SQLException ex) {
            throw new DataAccessException("Could not commit transaction in postgres pool", ex);
        }
    }

    @Override
    public Connection acquire() throws DataAccessException {
        LOG.debug("Acquiring connection in {}", Thread.currentThread());
        try {
            Connection connection = connections.get();
            if (connection == null) {
                connection = pool.getConnection();
                connection.setAutoCommit(false);
                connections.set(connection);
            }
            return connection;
        } catch (SQLException ex) {
            throw new DataAccessException("Can't acquire connection from postgres pool", ex);
        }
    }

    @Override
    //no-op => the connection won't be released until it is commited
    public void release(Connection connection) throws DataAccessException {
        LOG.debug("Releasing connection in {}", Thread.currentThread());
    }
}

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