Using jOOQ with Spring: Configuration

Petri KainulainenJanuary 28th, 2014Last Updated: January 27th, 2014

0 96 9 minutes read

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 and 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 and 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 3.2.6. 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.2.2.
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 1.2 of the Spring Framework Reference Documentation.

The reason why this example application uses Spring Framework 3.2.6 instead of 4.0 is that at the moment spring-test-dbunit isn’t compatible with Spring Framework 4.0.

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

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-aop</artifactId>
    <version>3.2.6.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-beans</artifactId>
    <version>3.2.6.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-core</artifactId>
    <version>3.2.6.RELEASE</version>
</Dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>3.2.6.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context-support</artifactId>
    <version>3.2.6.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-expression</artifactId>
    <version>3.2.6.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>3.2.6.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>3.2.6.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.2.2</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 to Spring DataAccessExceptions

Why should we transform the exceptions thrown by jOOQ to 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 to Spring DataAccessExceptions.

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

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.
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 the 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()));
    }
}

This is not my idea. I got this idea from Adam Zell’s Gist.

Additional reading:

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:

Configure the data base connection. We need to configure the JDBC driver class, JDBC url, username of the database user, and password of the database user.
Configure the name of the used SQL dialect.
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:

Create a PersistenceContext class.
Ensure that the created class is recognized as a configuration class by annotating the class with the @Configuration annotation.
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.
Enable the annotation-driven transaction management by annotating the configuration class with the @EnableTransactionManagement annotation.
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.
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.
Configure the DataSource bean. Because our application uses BoneCP, we have create a BoneCPDataSource object as our data source.
Configure the LazyConnectionDataSourceProxy bean. This bean ensures that the database connection are fetched lazily (i.e. when first statement is created).
Configure the TransactionAwareDataSourceProxy bean. This bean ensures that all JDBC connection are aware of Spring-managed transactions. In other words, JDBC connections participates in thread-bound transactions.
Configure the DataSourceTransactionManager bean. We must pass the LazyConnectionDataSourceProxy bean as as constructor argument when we create a new DataSourceTransactionManager object.
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 jOOQ participate in Spring-managed transactions.
Configure the JOOQToSpringExceptionTransformer bean.
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 to Spring DataAccessExceptions.
3. We have to configure the used SQL dialect.
Configure the DefaultDSLContext bean. We use this bean when we are creating database queries with jOOQ.
Configure the DataSourceInitializer bean. We use this bean to create the database schema of 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 configurations files, the example application has a working XML configuration file as well.

Credits:

This configuration is based on section 3.4.3 of the jOOQ user manual.
The message posted by Sergey Epik helped me to figure out how I can add custom ExecuteListeners to the jOOQ configuration.
Peter Ertl gave me an idea to use LazyConnectionDataSourceProxy.

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 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 to Spring DataAccessExceptions.
We learned how we can configure the application context of an application which uses jOOQ and Spring.
We took a quick 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.

The example application of this blog post is available at Github.

Additional reading:

Vlad Mihalcea has written about jOOQ as well. Check out his blog!

Reference: Using jOOQ with Spring: Configuration from our JCG partner Petri Kainulainen at the Petri Kainulainen blog.