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.

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

 1<dependency>
 2	<groupId>org.springframework</groupId>
 3	<artifactId>spring-aop</artifactId>
 4	<version>4.1.2.RELEASE</version>
 5</dependency>
 6<dependency>
 7	<groupId>org.springframework</groupId>
 8	<artifactId>spring-beans</artifactId>
 9	<version>4.1.2.RELEASE</version>
10</dependency>
11<dependency>
12	<groupId>org.springframework</groupId>
13	<artifactId>spring-core</artifactId>
14	<version>4.1.2.RELEASE</version>
15</Dependency>
16<dependency>
17	<groupId>org.springframework</groupId>
18	<artifactId>spring-context</artifactId>
19	<version>4.1.2.RELEASE</version>
20</dependency>
21<dependency>
22	<groupId>org.springframework</groupId>
23	<artifactId>spring-context-support</artifactId>
24	<version>4.1.2.RELEASE</version>
25</dependency>
26<dependency>
27	<groupId>org.springframework</groupId>
28	<artifactId>spring-expression</artifactId>
29	<version>4.1.2.RELEASE</version>
30</dependency>
31<dependency>
32	<groupId>org.springframework</groupId>
33	<artifactId>spring-jdbc</artifactId>
34	<version>4.1.2.RELEASE</version>
35</dependency>
36<dependency>
37	<groupId>org.springframework</groupId>
38	<artifactId>spring-tx</artifactId>
39	<version>4.1.2.RELEASE</version>
40</dependency>
41
42<dependency>
43	<groupId>cglib</groupId>
44	<artifactId>cglib</artifactId>
45	<version>3.1</version>
46</dependency>
47
48<dependency>
49	<groupId>com.jolbox</groupId>
50	<artifactId>bonecp</artifactId>
51	<version>0.8.0.RELEASE</version>
52</dependency>
53
54<dependency>
55	<groupId>org.jooq</groupId>
56	<artifactId>jooq</artifactId>
57	<version>3.4.4</version>
58</dependency>
59
60<dependency>
61	<groupId>com.h2database</groupId>
62	<artifactId>h2</artifactId>
63	<version>1.3.174</version>
64</dependency>

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:
3. Get a SQLDialect object from the jOOQ configuration.
4. 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.
5. Create the DataAccessException object by using the created SQLExceptionTranslator object.
6. Pass the thrown DataAccessException forward to the ExecuteContext object given as a method argument.

The source code of the JOOQToSpringExceptionTransformer class looks as follows:

 1import org.jooq.ExecuteContext;
 2import org.jooq.SQLDialect;
 3import org.jooq.impl.DefaultExecuteListener;
 4import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
 5import org.springframework.jdbc.support.SQLExceptionTranslator;
 6import org.springframework.jdbc.support.SQLStateSQLExceptionTranslator;
 7
 8public class JOOQToSpringExceptionTransformer extends DefaultExecuteListener {
 9
10    @Override
11    public void exception(ExecuteContext ctx) {
12        SQLDialect dialect = ctx.configuration().dialect();
13        SQLExceptionTranslator translator = (dialect != null)
14                ? new SQLErrorCodeSQLExceptionTranslator(dialect.name())
15                : new SQLStateSQLExceptionTranslator();
16
17        ctx.exception(translator.translate("jOOQ", ctx.sql(), ctx.sqlException()));
18    }
19}

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.

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:

 1#Database Configuration
 2db.driver=org.h2.Driver
 3db.url=jdbc:h2:target/jooq-example
 4db.username=sa
 5db.password=
 6
 7#jOOQ Configuration
 8jooq.sql.dialect=H2
 9
10#DB Schema
11db.schema.script=schema.sql

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:
14. We have to set the ConnectionProvider which is used to obtain and release database connections.
15. 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.
16. We have to configure the used SQL dialect.
17. Configure the DefaultDSLContext bean. We use this bean when we are creating database queries with jOOQ.
18. 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:

  1import com.jolbox.bonecp.BoneCPDataSource;
  2import org.jooq.SQLDialect;
  3import org.jooq.impl.DataSourceConnectionProvider;
  4import org.jooq.impl.DefaultConfiguration;
  5import org.jooq.impl.DefaultDSLContext;
  6import org.jooq.impl.DefaultExecuteListenerProvider;
  7import org.springframework.beans.factory.annotation.Autowired;
  8import org.springframework.context.annotation.Bean;
  9import org.springframework.context.annotation.ComponentScan;
 10import org.springframework.context.annotation.Configuration;
 11import org.springframework.context.annotation.PropertySource;
 12import org.springframework.core.env.Environment;
 13import org.springframework.core.io.ClassPathResource;
 14import org.springframework.jdbc.datasource.DataSourceTransactionManager;
 15import org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy;
 16import org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy;
 17import org.springframework.jdbc.datasource.init.DataSourceInitializer;
 18import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
 19import org.springframework.transaction.annotation.EnableTransactionManagement;
 20
 21import javax.sql.DataSource;
 22
 23@Configuration
 24@ComponentScan({"net.petrikainulainen.spring.jooq.todo"})
 25@EnableTransactionManagement
 26@PropertySource("classpath:application.properties")
 27public class PersistenceContext {
 28
 29    @Autowired
 30    private Environment env;
 31
 32    @Bean(destroyMethod = "close")
 33    public DataSource dataSource() {
 34        BoneCPDataSource dataSource = new BoneCPDataSource();
 35
 36        dataSource.setDriverClass(env.getRequiredProperty("db.driver"));
 37        dataSource.setJdbcUrl(env.getRequiredProperty("db.url"));
 38        dataSource.setUsername(env.getRequiredProperty("db.username"));
 39        dataSource.setPassword(env.getRequiredProperty("db.password"));
 40
 41        return dataSource;
 42    }
 43
 44    @Bean
 45    public LazyConnectionDataSourceProxy lazyConnectionDataSource() {
 46        return new LazyConnectionDataSourceProxy(dataSource());
 47    }
 48
 49    @Bean
 50    public TransactionAwareDataSourceProxy transactionAwareDataSource() {
 51        return new TransactionAwareDataSourceProxy(lazyConnectionDataSource());
 52    }
 53
 54    @Bean
 55    public DataSourceTransactionManager transactionManager() {
 56        return new DataSourceTransactionManager(lazyConnectionDataSource());
 57    }
 58
 59    @Bean
 60    public DataSourceConnectionProvider connectionProvider() {
 61        return new DataSourceConnectionProvider(transactionAwareDataSource());
 62    }
 63
 64    @Bean
 65    public JOOQToSpringExceptionTransformer jooqToSpringExceptionTransformer() {
 66        return new JOOQToSpringExceptionTransformer();
 67    }
 68
 69    @Bean
 70    public DefaultConfiguration configuration() {
 71        DefaultConfiguration jooqConfiguration = new DefaultConfiguration();
 72
 73        jooqConfiguration.set(connectionProvider());
 74        jooqConfiguration.set(new DefaultExecuteListenerProvider(
 75            jooqToSpringExceptionTransformer()
 76        ));
 77
 78        String sqlDialectName = env.getRequiredProperty("jooq.sql.dialect");
 79        SQLDialect dialect = SQLDialect.valueOf(sqlDialectName);
 80        jooqConfiguration.set(dialect);
 81
 82        return jooqConfiguration;
 83    }
 84
 85    @Bean
 86    public DefaultDSLContext dsl() {
 87        return new DefaultDSLContext(configuration());
 88    }
 89
 90    @Bean
 91    public DataSourceInitializer dataSourceInitializer() {
 92        DataSourceInitializer initializer = new DataSourceInitializer();
 93        initializer.setDataSource(dataSource());
 94
 95        ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
 96        populator.addScript(
 97                new ClassPathResource(env.getRequiredProperty("db.schema.script"))
 98        );
 99
100        initializer.setDatabasePopulator(populator);
101        return initializer;
102    }
103}

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.

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.

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.