In this story, we’ll implement a sample REST API that uses Spring Boot with jOOQ and PostgreSQL.
This is the list of all the prerequisites for following this story:
- Spring Boot 2.7.5
- Maven 3.6.3
- Java 17
- Postman or Insomnia
What is jOOQ?
jOOQ (Java Object Oriented Querying) is a popular Java database library that builds type-safe SQL queries through its fluent API. jOOQ includes generators that generate Java code from the database. It also has code generators for Data Access Objects (DAO) and POJOs to effectively complete CRUD operations.
Why jOOQ ?
- Code Generation: jOOQ generates Java classes, JPA-annotated entity classes, interfaces, or even XML from the database metadata.
- Type Safety SQL: jOOQ treats SQL like what it is in the first place: A language. Through its unique and modern fluent API design techniques, jOOQ embeds SQL as an internal domain-specific language directly in Java, making it easy for developers to write and read code that almost feels like actual SQL.
- Database First: With jOOQ, database and data comes first.
- Domain-Specific Language: jOOQ provides a DSL that replicates the particular SQL dialect used by the database vendors.
We’ll start with a “database first” approach which consists in creating and initializing the objects of our database which will be used by jOOQ.
We have two tables author and book with a one-to-many mapping between the tables.
Configure jOOQ’s code generator
Let’s start by creating a simple Spring Boot project from start.spring.io, with the following dependencies: Spring Web, JOOQ Access Layer, Lombok, PostgreSQL Driver, and Validation.
Open the properties file
src/main/resources/application.propertiesand add the PostgreSQL database configuration.
Now we need to use
jooq-codegen-mavenplugin to integrate source code generation in your Maven build process.
Start the jOOQ generator with the command:
mvn clean generate-sources
We should find new classes in
The fluent API offered by jOOQ is initiated via the
org.jooq.DSLContext interface. Spring Boot will auto-configure
DSLContext as a Spring Bean.
JOOQRepository is our custom interface which uses the CRUD methods.
Let’s try some calls through our BookController.java.
Done. It works fine. 🙂
All the methods above use the DSLContext bean but we can also use DAOs implementation. jOOQ generates one DAO per UpdatableRecord, i.e. per table with a single-column primary key. Generated DAOs implement a common jOOQ type called
org.jooq.DAO. With this, we don’t need our custom repository interface JOOQRepository.java because generated DAO classes implement various useful CRUD methods.
Let’s modify the existing jooq-codegen-maven plugin configuration to add daos generation.
After rebuilding, jOOQ added classes corresponding to the Dao.
To use the DAO, we can inject it, as shown in the following example in the BookServiceImpl.java class:
Congratulations! It’s done.
In this story, we implemented a REST API application that uses Spring Boot with jOOQ and PostgreSQL.
The complete source code is available on GitHub.
If you enjoyed this story, please give it a few claps for support.