JOOQ and Spring

Many people are using jOOQ with Spring or Spring Boot

  • https://www.baeldung.com/jooq-with-spring
  • https://www.baeldung.com/spring-boot-support-for-jooq

There is also a very good tutorial by Petri Kainulainen, explaining every step to set up a project, here:

  • Using jOOQ with Spring: Configuration
  • Using jOOQ with Spring: Code Generation
  • Using jOOQ with Spring: CRUD
  • Using jOOQ with Spring: Sorting and Pagination

Here's a blog post about how to use jOOQ with Spring Boot, especially useful when you need the commercial distributions of jOOQ:

  • https://blog.jooq.org/how-to-use-jooqs-commercial-distributions-with-spring-boot/

All you need to do/know to make jOOQ work with spring:

  1. Get the java.sql.Connection bound to the thread by the transaction manager.
  2. Handle transactions properly through exception translation
  3. Understand that the jOOQ Factory objects (despite the name) are not threadsafe. and thus will require instantiating a new object per use (Do not do this other answer).

So for the first and second case I offer this gist: https://gist.github.com/3669307 which does what Lukas recommends.

For the third case you can either create basically a factory of a factory (which contains the DataSource) or just instantiate a new Factory object in each method using the wired DataSource in your spring component.

@Service
public class MyDaoOrService {
    @Autowired
    private void DataSource dataSource;

    @Transactional
    public void doSomeJooq(){
        Settings s = new Settings();
        //You could instead put this jooq configuration xml
         s.getExecuteListeners().add("com.snaphop.jooq.SpringExceptionTranslationExecuteListener");
        MyGeneratedFactory f = new MyGeneratedFactory(dataSource, s);
        f.select(); //etc
    }
}

As for the settings listener you can JOOQ's configuration support to avoid the programmatic creation.

I won't cover how you setup a DataSource in Spring as that is covered in myriad of other/better places.


getting spring transactions running with jOOQ is a lot simpler (unless I forgot something):

just wrap your data source into

org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy

optional: to delay opening a jdbc connection until the first actual sql statement happens use

org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy

so as an sample do this to create a jOOQ factory with 'transactions' and 'lazyness' applied

DataSource rawDS = /* your actual data source */
// (optional) make access lazy
final DataSource lazyDS = new LazyConnectionDataSourceProxy(rawDataSource);
// make spring transactions available in plain jdbc context
final DataSource txDS = new TransactionAwareDataSourceProxy(lazyDS);
// create jOOQ factory
Factory jooq = new Factory(txDS, /* dialect */, /* settings */)
// voila!

I was looking to use jOOQ as an builder library for providing queries to Spring's JdbcTemplate and related classes. Unfortunately, jOOQ appears to combine two concepts into the same set of classes: SQL generation and query execution. In my case, I want the former but want to let Spring handle the latter. It does work, though. For example, you can do something like this (using jOOQ 2.x API):

Factory create = new Factory(null, SQLDialect.ORACLE);
getJdbcTemplate().query(
    create.select(create.field(ID_COL),
                  create.field(VALUE_COL))
        .from(FOO_TABLE)
        .where(create.field(ID_COL).equals("ignored"))
        .getSQL(),
    myRowMapper,
    id);

Tags:

Sql

Java

Spring

Jooq

Recent Posts