How to use jOOQ with Spring Boot, Gradle, Flyway and Testcontainers
Benefit from type-safe SQL queries at compile time.
👋 Introduction
So, recently I was bothered with JPA and Hibernate and how certain queries were executed when I checked the logs. If you are not careful, you can run into N+1 query issues, and the abstraction might translate queries differently than expected, so fine-tuning gets difficult. You can still use native queries or JPQL, but then you can lose compile-time type safety for your queries. So what could we use instead? I wanted to give jOOQ a try as an alternative to an Object-Relational Mapper (ORM) framework. During setup, I faced some difficulties, so here I am gonna present the setup that I have chosen.
💻 Technologies
PostgreSQL will be used as the database.
Flyway will be used for the creation of tables and managing database migrations.
"org.jooq.jooq-codegen-gradle"will be used for generating jOOQ code.Testcontainers will be used for tests and also as the database we use to connect against to check for schema and generate the jOOQ code accordingly.
To follow along, you should have some basic knowledge of Java, Spring Boot, Flyway, and Testcontainers.
You can find the source code on GitHub.
⚙️ Setup
We start by creating a simple Spring application. We will only have a repository and an entity in our Spring Boot application. We keep it simple with our entity:
Now, to create a repository for data access, we have to do a few steps first. We need Flyway to create the table. We add the following dependencies to have the Postgres driver and the necessary jOOQ dependencies to create type-safe queries later on:
For that, we create a folder under resources with the name db.migration and this script:
So now we can use the DSLContext of jOOQ to write queries, but we have no code generated yet, so how can we access it type-safe? Well, here come Testcontainers and an additional jOOQ Gradle config into play. We make the Gradle task that compiles our project dependent on the jOOQ code generation. Therefore, we need to supply some dependencies to Gradle as well in the build script. The config looks like this:
The jOOQ config lets you configure which generator you want to use, which database is used, and for which resources you want to create the code. I got the basic idea on how to integrate it from this Gist and modified it a bit. I have only chosen books as we only have one table, but you can also generate code for stored procedures and so on. Files will be placed in the directory and with the package name that you specify under target; I used shared/db.
Afterwards, the container is shut down. You could also configure it to reuse that container for your tests, but I wanted to separate that. So now, if you run ./gradlew clean build, you will see that Testcontainers is started and jooqCodegen executed.
🧠 Generated Code
If your setup runs through, you should find the generated classes in the specified folder.
The Public class has the tables we have created:
For our books table definition, it looks something like this:
And for our primary key:
✍ Creating a Repository
To enable and streamline our data access, let us create a repository that can save and fetch a book. DSLContext is required to use the generated classes and to implement repository methods. It is pretty much fluent SQL, and if you know your way around that, it should work out for you easily to create the methods you want.
🕵🏻 Testing
Of course, we also want to test those repositories. For that, we will use Testcontainers. Dependencies needed:
Our PostgresConfig for testing purposes:
And finally, our test. We use @JooqTest to only create the Spring context needed for this test, and we import the previously created Postgres config so that the container with Postgres is running.
🏁 Conclusion
After giving jOOQ a try, I really appreciated the benefit of having type-safe SQL queries at compile-time. The DSL it offers lets you write SQL queries in a way that feels natural, and catching errors during development rather than at runtime is a big plus. This level of control over SQL generation ensures that the queries are optimized and executed exactly as intended, which is especially useful for complex database interactions.
However, I also recognize the strengths of Hibernate and JPA. They make CRUD operations incredibly straightforward and provide robust object-relational mapping, caching, and transactional support. For many standard applications, these features can streamline development and boost productivity.
Ultimately, it boils down to the specific needs of your project. If you require precise control over your SQL and value the safety of compile-time checks, jOOQ is a solid choice. But if you're working on a project where rapid development and ease of managing persistent entities are priorities, Hibernate and JPA might serve you better.