Java – jooq and transactions

I've been reading about transactions & jooq, but it's hard for me to see how to implement it in practice

Suppose I provide jooq with a custom connectionprovider, which just sets a connection pool and autocommit to false

The implementation is as follows:

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

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

How do I wrap two jooq queries into a single transaction?

Defaultconnectionprovider is easy because there is only one connection - but there is a pool and I don't know what to do

Solution

jOOQ 3.4 Transaction API

Using jooq 3.4, a transaction API has been added to abstract JDBC, spring or JTA transaction managers This API can be used with Java 8:

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

Or use the syntax before Java 8

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 lambda expressions (or anonymous classes) form transaction code, where:

>Commit on normal completion > rollback on exceptions

org. jooq. The transactionprovider SPI can be used to override the default behavior, which enables nested transactions through JDBC using savepoints

An example of spring

The current document shows an example of transaction processing using spring:

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

This example basically boils down to using 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>

You can see a running example from GitHub:

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

Examples of spring and nobility

Although I personally won't recommend it, some users have successfully replaced part of Guice's spring Di and handled transactions with Guice For such use cases, GitHub also has an integration tested running example:

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