Spring Boot with jOOQ and PostgreSQL

In this story, we’ll implement a sample REST API that uses Spring Boot with jOOQ and PostgreSQL.

Prerequisites

This is the list of all the prerequisites for following this story:

• Spring Boot 2.7.5
• Maven 3.6.3
• Java 17
• PostgreSQL
• Postman or Insomnia

Overview

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.

Getting Started

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.

src/main/resources/schema.sql

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 filesrc/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 target/generated-sources/jooq

Using DSLContext

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.

POST http://localhost:8080/api/book

GET http://localhost:8080/api/book

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.

https://www.jooq.org/doc/3.17/manual/code-generation/codegen-daos/#flags-controlling-dao-generation

After rebuilding, jOOQ added classes corresponding to the Dao.

target/generated-sources/jooq

To use the DAO, we can inject it, as shown in the following example in the BookServiceImpl.java class:

Congratulations! It’s done.

Conclusion

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.

References

• https://github.com/jOOQ/jOOQ
• https://www.jooq.org/
• https://docs.spring.io/spring-boot/docs/2.7.5/reference/html/data.html#data.sql.jooq