Spring-Batch with JOOQ

Spring-Batch is a battle tested batch processing framework with transaction support and the ability to retry/recover from a failure point with many other useful features needed in enterprise grade batch processing jobs.

Spring-Batch comes with built in support for JDBC and JPA, but not JOOQ.

In fact there is one issue in JOOQ code repository opened requesting for this example at:

• https://github.com/jOOQ/jOOQ/issues/3716

In this article I will summarize my findings integrating the JOOQ with Spring-Batch to consolidate this learning to hopefully arrive at a recommend approach for doing it.

Why JOOQ ?

JOOQ allows us to generate a type safe access layer from Java to the database with just the enough level of abstraction.

Using JOOQ one does not have learn another database query language like with Hibernate/JPA , nor learn the EntityManager very wide interface or the caching strategies being used by the ORM layer, or optimizing queries generated by ORM.

If you are comfortable with SQL the code you will write to do database access will be very much like SQL as you can see from the examples bellow:

Inserting and item into the database using JOOQ

Reading items from the database with pagination using JOOQ

With JOOQ we start with the database and SQL first and by doing code generation from the database via build script that generates elegant type-safe code that can talk to that database schema.

So when we are writing database access code it is all validated by the java compiler.

The image bellow is the example H2 database schema I used for this example:

Example H2 database schema

Example database schema DDL

I have used H2 database example to make it easy for you to replay this example in you environment without the need to go about installing a database locally because H2 is usable as an embedded database with Java.

But JOOQ supports equally well all of the main post popular database engines like: Oracle, MSSQL, PostgresSQL, MySQL and others.

A handy feature of H2 in the context of this example is that it supports running a SQL script up on opening of the database connection so that one can initialize the database schema on the fly.

The image bellow shows how one has to configure the database connection in order to have the schema initialized on connection.

Connection to H2 database with the schema initialized on the fly

The above image is from Intelij DataGrip plugin as a reference, but the setting will be exactly the same if you use another SQL client like SQuirrel SQL.

Code Generation

In the past I would always be cautious about a tool that uses code generation, but in the case of JOOQ the creator did an stellar job and the generated code is actually very idiomatic java DSL with fluent builders.

Then example bellow shows a typical usage to the JOOQ gradle plugin to generate the supporting java fluent builder classes:

Gradle JOOQ plugin usage example

It is relevant to note that since I am using embedded H2 database I am using the handy H2 init sql script to make sure the schema exists up front so that the plugin will be able to introspect the database and find existing tables and generate the necessary supporting classes.

This generated java source code becomes available for us to consume once we link these as an additional sourceset in gradle

Linking the generated source classes in Gradle build

JOOQ integration with Spring-Batch

I order for JOOQ to integrate well with Spring database access code one needs to :

1. Make JOOQ take part of Spring managed database transactions
2. Translate JOOQ database access exceptions into Spring Database exceptions to get nice meaningful exceptions up on errors

The class below is how shows how this was done in the example code:

Integration of JOOQ with Spring database code

Bellow is the exception translation code:

SpringExceptionTranslationExecuteListener

Example Spring-Batch job definition

In the example you will a very simple batch job definition that read a CSV file line by line processes/transforms each line and then loads this into the target database.

The batch job definition

Bellow is the definition necessary to read a CSV file using Spring-Batch.

Reading the input CSV file

Enabling Pagination with JOOQ and Spring-Batch

It is possible to implement pagination with retry and recovery in a batch job if we implement the AbstractPagingItemReader interface and you will find an example implementation of this in the code the classe PersonItemReader that uses JOOQ support of pagination in database access code.

Item reader with pagination and retry/recovery

The example source code

The complete source code for this example is available in my git repository, and includes unit testing code all available at:

luistrigueiros/jooq-spring-batch-test

If you found anything wrong with example please provide me feedback.

Thank you for reading, hope you found this useful.