In this post, 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

drop table if exists public.author;
drop table if exists public.book;

create table public.author
(
    id        serial primary key,
    firstname character varying(255) COLLATE pg_catalog."default",
    lastname  character varying(255) COLLATE pg_catalog."default"
);

create table public.book
(
    id          serial primary key,
    description text,
    isbn        character varying(255) COLLATE pg_catalog."default",
    page        integer          NOT NULL,
    price       double precision NOT NULL,
    title       character varying(100) COLLATE pg_catalog."default",
    author_id   integer          not null references public.author (id)
);

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.

#PostgreSQL Database Configuration
datasource.driver= org.postgresql.Driver
datasource.jdbcUrl= jdbc:postgresql://localhost:5432/librarydb
datasource.username= dev
datasource.password= qwerty

# configure spring data source
spring.application.name=jooq-api
spring.datasource.driver-class-name=${datasource.driver}
spring.datasource.url = ${datasource.jdbcUrl}
spring.datasource.username = ${datasource.username}
spring.datasource.password = ${datasource.password}
spring.jooq.sql-dialect= postgres

Now we need to use jooq-codegen-mavenplugin to integrate source code generation in your Maven build process.

<plugin>
                <groupId>org.jooq</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <executions>
                    <execution>
                        <id>generate-postgres</id>
                        <phase>generate-sources</phase>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                        <configuration>
                            <!-- JDBC connection parameters -->
                            <jdbc>
                                <driver>${datasource.driver}</driver>
                                <url>${datasource.jdbcUrl}</url>
                                <user>${datasource.username}</user>
                                <password>${datasource.password}</password>
                            </jdbc>
                            <!-- Generator parameters -->
                            <generator>
                                <database>
                                    <name>
                                        org.jooq.meta.postgres.PostgresDatabase
                                    </name>
                                    <includes>.*</includes>
                                    <excludes/>
                                    <inputSchema>public</inputSchema>
                                </database>
                                <generate>
                                    <pojos>true</pojos>
                                    <pojosEqualsAndHashCode>true</pojosEqualsAndHashCode>
                                    <javaTimeTypes>true</javaTimeTypes>
                                    <fluentSetters>true</fluentSetters>
                                </generate>
                                <target>
                                    <packageName>com.example.springbootjooq.model</packageName>
                                    <directory>target/generated-sources/jooq</directory>
                                </target>
                            </generator>
                        </configuration>
                    </execution>
                </executions>
            </plugin>

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.

@RequiredArgsConstructor
@Transactional
@Repository
public class BookRepository implements JOOQRepository<Book> {

    private final DSLContext context;

    @Override
    public Book save(Book book){
        BookRecord bookRecord = context.insertInto(BOOK)
                .set(BOOK.DESCRIPTION, book.getDescription())
                .set(BOOK.ISBN, book.getIsbn())
                .set(BOOK.PAGE, book.getPage())
                .set(BOOK.PRICE, book.getPrice())
                .set(BOOK.TITLE, book.getTitle())
                .set(BOOK.AUTHOR_ID, book.getAuthorId())
                .returning(BOOK.ID).fetchOne();

        if (bookRecord != null) {
            book.setId(bookRecord.getId());
            return book;
        }
        return null;
    }

    @Override
    public Book update(Book book, int id) {
        BookRecord bookRecord = context.update(BOOK)
                .set(BOOK.DESCRIPTION, book.getDescription())
                .set(BOOK.ISBN, book.getIsbn())
                .set(BOOK.PAGE, book.getPage())
                .set(BOOK.PRICE, book.getPrice())
                .set(BOOK.TITLE, book.getTitle())
                .set(BOOK.AUTHOR_ID, book.getAuthorId())
                .where(BOOK.ID.eq(5))
                .returning(BOOK.ID).fetchOne();

        return (bookRecord != null) ? book : null;
    }

    @Override
    public List<Book> findAll(){
        return context
                .selectFrom(BOOK)
                .fetchInto(Book.class);
    }
    @Override
    public Optional<Book> findById(int id){
        Book book = context.selectFrom(BOOK).where(BOOK.ID.eq(id)).fetchOneInto(Book.class);
        return (ObjectUtils.isEmpty(book)) ? Optional.empty() : Optional.of(book);
    }

    @Override
    public boolean deleteById(int id) {
        return context.delete(BOOK)
                .where(BOOK.ID.eq(id))
                .execute() == 1;
    }

}

JOOQRepository is our custom interface which uses the CRUD methods.

Let’s try some calls through our BookController.java.

@RequiredArgsConstructor
@RestController
@RequestMapping("/api/book")
public class BookController {

    private final BookService bookService;

     @PostMapping()
     public ResponseEntity<SuccessResponse> create(@RequestBody @Valid Book book) {
         if (!ObjectUtils.isEmpty(book.getId())) {
             throw new BadRequestException("A new data cannot already have an ID");
         }

         return new ResponseEntity<>(
                 new SuccessResponse(bookService.create(book), "Successful registration"),
                 HttpStatus.CREATED);
     }

    @GetMapping
    public ResponseEntity<SuccessResponse> getAll() {
        List<Book> books = bookService.getAll();

        return new ResponseEntity<>(new SuccessResponse(books, MessageFormat.format("{0} Results found", books.size())), HttpStatus.OK);
    }

    @GetMapping("/{id}")
    public ResponseEntity<SuccessResponse> getOne(@PathVariable("id") int id) {
        Book book = bookService.getOne(id);
        return new ResponseEntity<>(
                new SuccessResponse(book, "Result found"), HttpStatus.OK);
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<SuccessResponse> delete(@PathVariable("id") int id) {
        bookService.deleteById(id);
        return new ResponseEntity<>(
                new SuccessResponse(null, "Deletion completed successfully"), HttpStatus.OK);
    }

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

@RequiredArgsConstructor
@Service(value = "BookServiceDAO")
public class BookServiceImpl implements BookService {

    private final BookDao repository;

    @Override
    public Book create(Book book) {
         repository.insert(book);
        return book;
    }

    @Override
    public Book update(Book book) {
        repository.update(book);
        return book;
    }

    @Override
    public List<Book> getAll(){
        return repository.findAll();
    }

    @Override
    public Book getOne(int id) {
        Book book = repository.findById(id);
        if(book == null){
           throw new DataNotFoundException(MessageFormat.format("Book id {0} not found", String.valueOf(id)));
        }
        return book;
    }

    @Override
    public void deleteById(int id) {
        repository.deleteById(id);
    }

}

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.

References

👉 Link to Medium blog

, , , , ,

Related Posts

Java Spring Boot

Building Robust API Clients with RestClient in Spring Boot 3.X

Editorial Team
Spring Boot

Simplifying Complex JPA Queries with Hibernate @Formula Annotation

Editorial Team