Using jOOQ With Spring: Configuration

I have had my share of performance problems caused by ORMs. Although I have to admit that most of these problems were really caused by yours truly, I have started to think that using ORMs in read-only operations is not worth it.

I started to look for alternative ways to implement these operations.

That is how I ran into jOOQ which states that:

jOOQ generates Java code from your database and lets you build typesafe SQL queries through its fluent API.

This looks very interesting. That is why I decided to give jOOQ a shot and share my findings with you.

This blog post is the first part of my Using jOOQ with Spring series. It describes how we can get the required dependencies and configure the application context of our application.

Let's get started.

Getting the Required Dependencies with Maven

The dependencies of our application are:

  • Spring Framework 4.1.2.RELEASE. At this point our example uses the aop, beans, core, context, context-support, jdbc, and tx modules.
  • cglib 3.1.
  • BoneCP 0.8.0. We use BoneCP as the connection pool of our example application.
  • jOOQ 3.4.4.
  • H2 1.3.174. We use H2 as the database of our example application.
If you want to get more information about the modules of Spring Framework, read section 2.2 of the Spring Framework Reference Documentation.

The relevant part of the pom.xml file looks as follows:

<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-aop</artifactId>
	<version>4.1.2.RELEASE</version>
</dependency>
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-beans</artifactId>
	<version>4.1.2.RELEASE</version>
</dependency>
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-core</artifactId>
	<version>4.1.2.RELEASE</version>
</Dependency>
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-context</artifactId>
	<version>4.1.2.RELEASE</version>
</dependency>
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-context-support</artifactId>
	<version>4.1.2.RELEASE</version>
</dependency>
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-expression</artifactId>
	<version>4.1.2.RELEASE</version>
</dependency>
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
	<version>4.1.2.RELEASE</version>
</dependency>
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-tx</artifactId>
	<version>4.1.2.RELEASE</version>
</dependency>
        
<dependency>
	<groupId>cglib</groupId>
	<artifactId>cglib</artifactId>
	<version>3.1</version>
</dependency>

<dependency>
	<groupId>com.jolbox</groupId>
	<artifactId>bonecp</artifactId>
	<version>0.8.0.RELEASE</version>
</dependency>

<dependency>
	<groupId>org.jooq</groupId>
	<artifactId>jooq</artifactId>
	<version>3.4.4</version>
</dependency>

<dependency>
	<groupId>com.h2database</groupId>
	<artifactId>h2</artifactId>
	<version>1.3.174</version>
</dependency>
The example application of this blog post has other dependencies as well. You can see the full dependency list by taking a look at the pom.xml file.

Let's move on find out how we can transform the exceptions thrown by jOOQ to Spring DataAccessExceptions.

Transforming jOOQ Exceptions into Spring DataAccessExceptions

Why should we transform the exceptions thrown by jOOQ into Spring DataAccessExceptions?

One reason to do so is that we want that our integration works in the same way than the DAO support of Spring Framework. One essential part of this support is a consistent exception hierarchy:

Spring provides a convenient translation from technology-specific exceptions like SQLException to its own exception class hierarchy with the DataAccessException as the root exception. These exceptions wrap the original exception so there is never any risk that one might lose any information as to what might have gone wrong.

In other words, if we want that our application is "a good citizen", it makes sense to ensure that our configuration transforms the exceptions thrown by jOOQ into Spring DataAccessExceptions.

We can create a component which provides this functionality by following these steps:

  1. Create a JOOQToSpringExceptionTransformer class which extends the DefaultExecuteListener class. The DefaultExecuteListener class is the public default implementation of the ExecuteListener interface which provides listener methods for different life cycle events of a single query execution.
  2. Override the exception(ExecuteContext ctx) method of the DefaultExecuteListener class. This method is called if an exception is thrown at any moment of the execution life cycle. Implement this method by following these steps:
    1. Get a SQLDialect object from the jOOQ configuration.
    2. Create an object which implements the SQLExceptionTranslator interface by following these rules:
      1. If the configured SQL dialect is found, create a new SQLErrorCodeSQLExceptionTranslator object and pass the name of the SQL dialect as a constructor argument. This class "selects" the right DataAccessException by analyzing vendor specific error codes.
      2. If the SQL dialect isn't found, create a new SQLStateSQLExceptionTranslator object. This class "selects" the right DataAccessException by analyzing the SQL state stored to the SQLException.
    3. Create the DataAccessException object by using the created SQLExceptionTranslator object.
    4. Pass the thrown DataAccessException forward to the ExecuteContext object given as a method argument.

The source code of the JOOQToSpringExceptionTransformer class looks as follows:

import org.jooq.ExecuteContext;
import org.jooq.SQLDialect;
import org.jooq.impl.DefaultExecuteListener;
import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
import org.springframework.jdbc.support.SQLExceptionTranslator;
import org.springframework.jdbc.support.SQLStateSQLExceptionTranslator;


public class JOOQToSpringExceptionTransformer extends DefaultExecuteListener {

    @Override
    public void exception(ExecuteContext ctx) {
        SQLDialect dialect = ctx.configuration().dialect();
        SQLExceptionTranslator translator = (dialect != null)
                ? new SQLErrorCodeSQLExceptionTranslator(dialect.name())
                : new SQLStateSQLExceptionTranslator();

        ctx.exception(translator.translate("jOOQ", ctx.sql(), ctx.sqlException()));
    }
}

Our job isn't done yet. Let's put all pieces together and finish our job by configuring the application context of our example application.

Configuring the Application Context

This section explains how we can configure the application context of our application by using Java configuration.

Let's start by creating a properties file which contains the configuration of our example application.

The build process of the actual application is based on Maven profiles. This ensures that we can use different configuration in different environments. You can get more information about this by reading my blog post titled Creating Profile Specific Configuration Files with Maven.

Creating the Properties File

We can create the properties file by following these steps:

  1. Configure the database connection. We need to configure the JDBC driver class, JDBC url, username of the database user, and password of the database user.
  2. Configure the name of the used SQL dialect.
  3. Configure the name of the SQL script which creates the database of our example application (This is an optional step which isn't required if your application doesn’t use an embedded database).

The application.properties file looks as follows:

#Database Configuration
db.driver=org.h2.Driver
db.url=jdbc:h2:target/jooq-example
db.username=sa
db.password=

#jOOQ Configuration
jooq.sql.dialect=H2

#DB Schema
db.schema.script=schema.sql
The Javadoc of the SQLDialect Enum specifies the list of database dialects supported by jOOQ.

Let's move on and find out how we can configure the application context of our application by using Java configuration.

Creating the Configuration Class

We can configure the application context of our application by following these steps:

  1. Create a PersistenceContext class.
  2. Ensure that the created class is recognized as a configuration class by annotating the class with the @Configuration annotation.
  3. Ensure that the jOOQ repositories of our application are found during the component scan. We can do this by annotating the configuration class with the @ComponentScan annotation.
  4. Enable the annotation-driven transaction management by annotating the configuration class with the @EnableTransactionManagement annotation.
  5. Ensure that the configuration of our application is loaded from the application.properties file which is found from the classpath. We can use do this by annotation the configuration class with the @PropertySource annotation.
  6. Add an Environment field to the configuration class and annotate the field with the @Autowired annotation. We use the Environment object to get the property values of the configuration properties which are loaded from the application.properties file.
  7. Configure the DataSource bean. Because our application uses BoneCP, we have create a new BoneCPDataSource object and use it as our DataSource bean.
  8. Configure the LazyConnectionDataSourceProxy bean. This bean ensures that the database connection are fetched lazily (i.e. when the first statement is created).
  9. Configure the TransactionAwareDataSourceProxy bean. This bean ensures that all JDBC connection are aware of Spring-managed transactions. In other words, JDBC connections participate in thread-bound transactions.
  10. Configure the DataSourceTransactionManager bean. We must pass the LazyConnectionDataSourceProxy bean as as constructor argument when we create a new DataSourceTransactionManager object.
  11. Configure the DataSourceConnectionProvider bean. jOOQ will get the used connections from the DataSource given as a constructor argument. We must pass the TransactionAwareDataSourceProxy bean as a constructor argument when we create a new DataSourceConnectionProvider object. This ensures that the queries created by jOOQ participate in Spring-managed transactions.
  12. Configure the JOOQToSpringExceptionTransformer bean.
  13. Configure the DefaultConfiguration bean. This class is the default implementation of the Configuration interface, and we can use it to configure jOOQ. We have to configure three things:
    1. We have to set the ConnectionProvider which is used to obtain and release database connections.
    2. We have to configure the custom execute listeners. In other words, we have to add JOOQToSpringExceptionTransformer bean to the created DefaultConfiguration object. This ensures that the exceptions thrown by jOOQ are transformed into Spring DataAccessExceptions.
    3. We have to configure the used SQL dialect.
  14. Configure the DefaultDSLContext bean. We use this bean when we are creating database queries with jOOQ.
  15. Configure the DataSourceInitializer bean. We use this bean to create the database schema of the H2 database when our application is started (If you don't use an embedded database, you don't have to configure this bean).

The source code of the PersistenceContext class looks as follows:

import com.jolbox.bonecp.BoneCPDataSource;
import org.jooq.SQLDialect;
import org.jooq.impl.DataSourceConnectionProvider;
import org.jooq.impl.DefaultConfiguration;
import org.jooq.impl.DefaultDSLContext;
import org.jooq.impl.DefaultExecuteListenerProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy;
import org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;

@Configuration
@ComponentScan({"net.petrikainulainen.spring.jooq.todo"})
@EnableTransactionManagement
@PropertySource("classpath:application.properties")
public class PersistenceContext {

    @Autowired
    private Environment env;

    @Bean(destroyMethod = "close")
    public DataSource dataSource() {
        BoneCPDataSource dataSource = new BoneCPDataSource();

        dataSource.setDriverClass(env.getRequiredProperty("db.driver"));
        dataSource.setJdbcUrl(env.getRequiredProperty("db.url"));
        dataSource.setUsername(env.getRequiredProperty("db.username"));
        dataSource.setPassword(env.getRequiredProperty("db.password"));

        return dataSource;
    }

    @Bean
    public LazyConnectionDataSourceProxy lazyConnectionDataSource() {
        return new LazyConnectionDataSourceProxy(dataSource());
    }

    @Bean
    public TransactionAwareDataSourceProxy transactionAwareDataSource() {
        return new TransactionAwareDataSourceProxy(lazyConnectionDataSource());
    }

    @Bean
    public DataSourceTransactionManager transactionManager() {
        return new DataSourceTransactionManager(lazyConnectionDataSource());
    }

    @Bean
    public DataSourceConnectionProvider connectionProvider() {
        return new DataSourceConnectionProvider(transactionAwareDataSource());
    }

    @Bean
    public JOOQToSpringExceptionTransformer jooqToSpringExceptionTransformer() {
        return new JOOQToSpringExceptionTransformer();
    }

    @Bean
    public DefaultConfiguration configuration() {
        DefaultConfiguration jooqConfiguration = new DefaultConfiguration();

        jooqConfiguration.set(connectionProvider());
        jooqConfiguration.set(new DefaultExecuteListenerProvider(
            jooqToSpringExceptionTransformer()
        ));

        String sqlDialectName = env.getRequiredProperty("jooq.sql.dialect");
        SQLDialect dialect = SQLDialect.valueOf(sqlDialectName);
        jooqConfiguration.set(dialect);

        return jooqConfiguration;
    }

    @Bean
    public DefaultDSLContext dsl() {
        return new DefaultDSLContext(configuration());
    }

    @Bean
    public DataSourceInitializer dataSourceInitializer() {
        DataSourceInitializer initializer = new DataSourceInitializer();
        initializer.setDataSource(dataSource());

        ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
        populator.addScript(
                new ClassPathResource(env.getRequiredProperty("db.schema.script"))
        );

        initializer.setDatabasePopulator(populator);
        return initializer;
    }
}
If you want to configure the application context by using XML configuration files, the example application has a working XML configuration file as well.

Credits:

How do we know that this configuration works? That is a good question. We will talk about that in the following section.

Does This Really Work?

When I started to investigate how I can ensure that the database queries created with jOOQ participate in Spring-managed transactions, I noticed that it isn't an easy problem to solve.

The example application of this blog post has a few integration tests which ensure that transactions (commit and rollback) are working in a very simple scenario. However, there are two things which we must take into account when we are using the solution described in this blog post:

1. All database queries created with jOOQ must be executed inside a transaction.

The Javadoc of the TransactionAwareDataSourceProxy class states:

Delegates to DataSourceUtils for automatically participating in thread-bound transactions, for example managed by DataSourceTransactionManager. getConnection calls and close calls on returned Connections will behave properly within a transaction, i.e. always operate on the transactional Connection. If not within a transaction, normal DataSource behavior applies.

In other words, if you perform multiple complex operations without a transaction, jOOQ will use a different connection for each operation. This can lead into race condition bugs.

I noticed this issue when I read this comment written by Ben Manes.

2. Using TransactionAwareDataSourceProxy is not recommend by its Javadoc.

The Javadoc of the TransactionAwareDataSourceProxy class has a section which goes like this:

This proxy allows data access code to work with the plain JDBC API and still participate in Spring-managed transactions, similar to JDBC code in a J2EE/JTA environment. However, if possible, use Spring's DataSourceUtils, JdbcTemplate or JDBC operation objects to get transaction participation even without a proxy for the target DataSource, avoiding the need to define such a proxy in the first place.

That is a pretty vague comment because it offers no explanation why we shouldn't use it. Adam Zell suggested that because the class uses reflection, using it might cause performance problems.

If you run in performance problems, you might want to use the approach described in Adam Zell's Gist.

Summary

We have now successfully configured the application context of our example application. This tutorial has taught four things:

  • We learned how we can get the required dependencies with Maven.
  • We learned how we can transform the exceptions thrown by jOOQ into Spring DataAccessExceptions.
  • We learned how we can configure the application context of an application which uses jOOQ and Spring.
  • We took a quick look at the things that we have to take into account when we are using the approach described in this blog post.

The next part of this tutorial describes we can use the code generation support of jOOQ.

P.S. You can get the example application of this blog post from Github.

If you want to use jOOQ with Spring Framework, you should read my Using jOOQ With Spring tutorial.
20 comments… add one
  • Shoaib Ahmad Gondal Jan 6, 2014 @ 7:24

    That is an amazing article. This project can be used for any new development for Spring+JOOQ as it already contains main configuration.

    • Petri Jan 6, 2014 @ 10:50

      Thanks! I appreciate your kind words.

      I added the code generation configuration to the example application yesterday. It should be a pretty good starting point if you want to use jOOQ with Spring Framework.

  • Alex Jan 7, 2014 @ 22:55

    Perti, I think you should be aware of one thing that I found a month ago - DSLContext is not a thread-safe class and you better to inject Configuration everywhere and use DSL.using(config) for creating context. Or could be a real problems with many concurrent transactions.

    • Petri Jan 7, 2014 @ 23:30

      Hi Alex,

      Thank you for your comment! I was hoping to get feedback from my configuration because it is based on my own findings (naturally). Anyway, I did a fast Google search and found two interesting discussions about this matter:

      I didn't have time to read them through now but I will take a closer look at them tomorrow.

      Again, thanks for pointing this out!

      • Lukas Eder Jan 27, 2014 @ 10:34

        It is correct that one has to watch out to get thread-safety right with jOOQ's Configuration. Contrary to the OP's claim, merely injecting it is not going to guarantee thread-safety. The simplest way to get it right is to always construct new DSLContexts, Configurations, ConnectionProviders etc.

        But you can also share DSLContexts / Configurations (the first being a simple wrapper for the latter). You'll have to keep in mind these things:

        • Configuration contains mutable state, e.g. Settings. Don't change Settings in a shared Configuration, create new Configurations
        • Configuration provides jOOQ with JDBC Connections through ConnectionProvider. The official jOOQ & Spring tutorial shows how you can configure a single injectable Configuration that will behave threadsafely.
        • Petri Jan 27, 2014 @ 20:15

          Lukas,

          Thank you for your comment. It was very helpful.

  • Vladimir Sep 15, 2015 @ 13:12

    Thanks a lot for your introduction!!!
    But I have an error on PersistenceContext compilation:

    org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type [javax.sql.DataSource] is defined: expected single matching bean but found 3: transactionAwareDataSource,lazyConnectionDataSource,dataSource

    I know that I can use @Primary annotation to resolve this, but I dont know why you dont use this annotation in example and on what DataSource should I apply it?

    • Petri Sep 15, 2015 @ 13:43

      Which Spring version are you using? The reason why I ask this is that I want to run the example application by using the same Spring version as you (this helps me to reproduce your problem and solve it).

      • Anonymous Sep 15, 2015 @ 15:16

        Thanks for fast answer!
        :: Spring Boot :: (v1.2.5.RELEASE)
        Actually I can send you sources, if you want :)

        • Petri Sep 15, 2015 @ 19:33

          Great! Check your inbox (you should have an email from me).

          • nataraj Feb 18, 2016 @ 23:27

            I am also facing the same issue. Mine is a spring boot app. how should i resolve that 'No Qualifying bean' exception...

            Caused by: org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type [javax.sql.DataSource] is defined: expected single matching bean but found 2: transactionAwareDataSource,dataSource
            at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBean(DefaultListableBeanFactory.java:366) ~[spring-beans-4.2.4.RELEASE.jar:4.2.4.RELEASE]

          • Petri Feb 19, 2016 @ 0:04

            Spring Boot has built-in support for jOOQ. In other words, if you use Spring Boot, you shouldn't try to configure it manually. Spring Boot will configure it for you as long as you have the relevant jar files in your classpath.

            Unfortunately I haven't used jOOQ with Spring Boot, but you can take a look at the "official" Spring Boot and jOOQ example application. Also, Spring Boot reference documentation has a small section about jOOQ as well.

  • lukasz Dec 20, 2015 @ 1:45

    Hi
    Very interesting article.
    Now i have an idea to play with CQRS 'variance', and i thing about mixing JPA with JOOQ. So basicly for my domain (read) i use JPA, and for read JOOQ. I also consider to split that two solution, and provide for each one different version of data source. So for JPA i have got 'standard' datasource, but for JOOQ i provide datasource optimized only for reads, but very important that i use JOOQ only for query and newer use that result for domain operation, only for views.
    Is this idea is ok ? I need transaction when i use JOOQ only for query ?

    • Petri Dec 21, 2015 @ 21:33

      Hi Lukasz,

      Is this idea is ok ?

      Have you measured the performance improvements of using a datasource that is optimized for read operations? I think that using two different datasources make things a bit more complicated, and it is justified only if this setup provides you significant performance improvements (You need to decide what is significant you).

      I need transaction when i use JOOQ only for query ?

      Well, if you use this configuration and you want to avoid race condition bugs, I think you need to use them. However, getting jOOQ and Spring to work correctly was quite challenging and it is possible that I have misunderstood something. You should probably take a look at the jOOQ User Manual since it has a jOOQ and Spring example as well.

      That being said, Spring Boot has a built-in support for jOOQ nowadays, and I assume it takes care of this problem. Can you use Spring Boot? If not, you could take look at it and figure out how Spring Boot solves this problem.

  • Maksim Oct 14, 2016 @ 8:24

    Are there any guarantees on correctness of interleaving jOOQ with JDBC-templates? Or even plain jdbc? I wasn't able to find any mention of that anywhere.

    • Maksim Oct 14, 2016 @ 12:02

      By now I found how one can run jOOQ queries in a Spring transaction, so in that sense integration is possible. But can we also use jdbc-template in that same transaction?

      • Petri Oct 14, 2016 @ 19:42

        Hi,

        It should be possible in theory. Also, I haven't noticed any problems in my own applications (the stuff that I do at work), BUT I admit that the documentation is not clear about this, and there is a chance that I have just been lucky.

        By the way, Spring Boot has now a build-in support for jOOQ. Maybe you could take a look at it and use similar configuration in your application?

Leave a Reply