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.

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.

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 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:

We have to set the ConnectionProvider which is used to obtain and release database connections.

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.

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).

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.

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.

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 inAdam 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.

Newsletter

Join them now to gain exclusive access to the latest news in the Java world, as well as insights about Android, Scala, Groovy and other related technologies.

Email address:

Join Us

With 1,240,600 monthly unique visitors and over 500 authors we are placed among the top Java related sites around. Constantly being on the lookout for partners; we encourage you to join us. So If you have a blog with unique and interesting content then you should check out our JCG partners program. You can also be a guest writer for Java Code Geeks and hone your writing skills!

Disclaimer

All trademarks and registered trademarks appearing on Examples Java Code Geeks are the property of their respective owners. Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries. Examples Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.