Spring Boot & JOOQ | Getting started

This article will show how to use jOOQ in your spring boot projects.

Why do we need jOOQ?
There is a lot of pain using JPA and Hibernate. That is why we have something jOOQ.

First, we need to create a database schema. In this article, we will develop a simple CRUD (create, read, update, and delete) application to manage goods.

The first step is to design our database. The application will contain only one table — goods. The table looks like below.

This code of ERD above, I have created within Mermaid.

erDiagram
    GOODS {
        id uuid pk "ID"
        name string "The name of good"
        price numeric "The price of good"
        total_count int "The total count of good"
        sold_count int "The sold count of good"
        deleted bool "The flag indicated that good was deleted"
    }

The next step is to create a Spring Boot Maven Project, but you can choose Gradle; it is up to you.

Let’s create a migration to the init schema of our database. I will use YAML syntax, but with Liquibase, you can choose between plain SQL, JSON, and YAML.

databaseChangeLog:
  - changeSet:
      id: init-schema
      author: Egor Voronianskii
      changes:
        - createTable:
            tableName: GOODS
            columns:
              - column:
                  name: id
                  type: UUID
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: name
                  type: VARCHAR(255)
                  constraints:
                    nullable: false
              - column:
                  name: price
                  type: numeric
              - column:
                  name: total_count
                  type: integer
              - column:
                  name: sold_count
                  type: integer
              - column:
                  name: deleted
                  type: boolean

I have the following structure in the project.

I have the following structure in the project. There are two folders — one for data definition language and the second for data management language.

Inside the changelog.yml, I specified where the migrations folder is located.

databaseChangeLog:
  - includeAll:
      path: db/migrations/ddl

Let’s configure our application to use the right changelog.

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/goods_db
    username: postgres
    password: 55555
  liquibase:
    change-log: classpath:db/changelog.yml

Before starting the app, ensure you have created a database.

The next step is to add a jOOQ plugin to generate code from the database. Here is my updated build section of pom.xml.

    <build>
        <plugins>
            <plugin>
                <groupId>org.jooq</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <dependencies>
                    <dependency>
                        <groupId>org.postgresql</groupId>
                        <artifactId>postgresql</artifactId>
                        <version>42.6.0</version>
                    </dependency>
                </dependencies>
                <configuration>
                    <jdbc>
                        <driver>org.postgresql.Driver</driver>
                        <url>jdbc:postgresql://localhost:5432/goods_db</url>
                        <user>postgres</user>
                        <password>55555</password>
                    </jdbc>
                    <generator>
                        <name>org.jooq.codegen.JavaGenerator</name>
                        <database>
                            <excludes>databasechangelog|databasechangeloglock</excludes>
                        </database>
                    </generator>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

As you may see, I must configure the database connection again and exclude tables Liquibase creates.

Now, run the plugin; you can do it from the terminal or the IDE.

After successfully running the plugin, you should see the generated classes in the target folder.

At the start of the article, I forgot to add the Spring Boot Web starter; let’s add it to pom.xml.

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

We must create a data transfer object before moving to the write service and controller layer. You may agree or disagree with me. However, having a different object unrelated to the database entity is better.

package io.vrnsky.jooqdemo.dto;

import java.math.BigDecimal;
import java.util.UUID;

public record Goods(
        UUID id,
        String name,
        BigDecimal price,
        Integer totalCount,
        Integer soldCount,
        boolean deleted
) {
}

The next step is to implement the service layer of our app.

package io.vrnsky.jooqdemo.service;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.jooq.DSLContext;
import org.jooq.generated.public_.tables.Goods;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import java.util.UUID;

@Slf4j
@Service
@RequiredArgsConstructor
public class GoodsService {

    private final DSLContext dslContext;

    public io.vrnsky.jooqdemo.dto.Goods create(io.vrnsky.jooqdemo.dto.Goods goods) {
        var id = UUID.randomUUID();
        var result = dslContext.insertInto(Goods.GOODS)
                .values(id, goods.name(), goods.price(), goods.totalCount(), goods.soldCount(), goods.deleted())
                .execute();
        log.info("Inserted with result: {}", result);
        return getById(id);
    }

    public io.vrnsky.jooqdemo.dto.Goods update(io.vrnsky.jooqdemo.dto.Goods goods) {
        var updated = dslContext.update(Goods.GOODS)
                .set(Goods.GOODS.ID, goods.id())
                .set(Goods.GOODS.NAME, goods.name())
                .set(Goods.GOODS.PRICE, goods.price())
                .set(Goods.GOODS.SOLD_COUNT, goods.soldCount())
                .set(Goods.GOODS.TOTAL_COUNT, goods.totalCount())
                .set(Goods.GOODS.DELETED, goods.deleted())
                .execute();
        log.info("Successfully updated {} rows", updated);
        return this.getById(goods.id());
    }

    public io.vrnsky.jooqdemo.dto.Goods getById(UUID id) {
        final var fetchedRecord = dslContext.select(
                        Goods.GOODS.ID, Goods.GOODS.NAME, Goods.GOODS.PRICE,
                        Goods.GOODS.SOLD_COUNT, Goods.GOODS.TOTAL_COUNT, Goods.GOODS.DELETED
                )
                .from(Goods.GOODS)
                .where(Goods.GOODS.ID.eq(id))
                .fetchOne();
        Assert.notNull(fetchedRecord, "Record with id = " + id + " is not exists");
        return new io.vrnsky.jooqdemo.dto.Goods(
                fetchedRecord.get(Goods.GOODS.ID),
                fetchedRecord.get(Goods.GOODS.NAME),
                fetchedRecord.get(Goods.GOODS.PRICE),
                fetchedRecord.get(Goods.GOODS.SOLD_COUNT),
                fetchedRecord.get(Goods.GOODS.TOTAL_COUNT),
                fetchedRecord.get(Goods.GOODS.DELETED)
        );
    }

    public void delete(UUID id) {
        dslContext.update(Goods.GOODS).
                set(Goods.GOODS.DELETED, true)
                .where(Goods.GOODS.ID.eq(id))
                .execute();

        log.info("Successfully deleted the good with id = [" + id + "]");
    }
}

It is time to create a controller layer to test our application.

package io.vrnsky.jooqdemo;

import io.vrnsky.jooqdemo.dto.Goods;
import io.vrnsky.jooqdemo.service.GoodsService;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import java.util.UUID;

@RestController
@RequiredArgsConstructor
public class GoodsController {

    private final GoodsService goodsService;

    @PostMapping("/create")
    public Goods create(@RequestBody Goods goods) {
        goodsService.create(goods);
    }

    @GetMapping("/{id}")
    public Goods getById(@PathVariable UUID id) {
        goodsService.getById(id);
    }

    @PutMapping("/update")
    public Goods update(@RequestBody Goods goods) {
        goodsService.update(goods);
    }

    @DeleteMapping("/delete/{id}")
    public void delete(@PathVariable UUID id) {
        goodsService.delete(id);
    }

}

I will add Spring Doc dependencies and document controller methods to make the testing process more accessible. This type of testing is manual and the better approach to writing unit tests.

   <dependency>
      <groupId>org.springdoc</groupId>
      <artifactId>springdoc-openapi-starter-webmvc-ui</artifactId>
      <version>2.3.0</version>
   </dependency>

Here is the updated version of the controller class.

package io.vrnsky.jooqdemo;

import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import io.vrnsky.jooqdemo.dto.Goods;
import io.vrnsky.jooqdemo.service.GoodsService;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import java.util.UUID;

@RestController
@RequiredArgsConstructor
@Tag(name = "GoodsController", description = "Operations with goods")
public class GoodsController {

    private final GoodsService goodsService;

    @Operation(summary = "Creation of goods")
    @PostMapping("/create")
    public Goods create(@RequestBody Goods goods) {
        return goodsService.create(goods);
    }

    @Operation(summary = "Get goods by id")
    @GetMapping("/{id}")
    public Goods getById(@PathVariable UUID id) {
        return goodsService.getById(id);
    }

    @Operation(summary = "Update goods")
    @PutMapping("/update")
    public Goods update(@RequestBody Goods goods) {
        return goodsService.update(goods);
    }

    @Operation(summary = "Delete by id")
    @DeleteMapping("/delete/{id}")
    public void delete(@PathVariable UUID id) {
        goodsService.delete(id);
    }

}

Now, we can run the application and try to perform CRUD operations.

Conclusion

It is my first time using a jOOQ; I cannot say that I’m going to use it at this moment at my job. My experience with jOOQ is relatively tiny. In my honest opinion, jOOQ would be a better approach if the application business logic requires very complex SQL queries. Meanwhile, the Spring JPA and Hibernate simplify the work with the database. So, choose carefully the tool that you would like to use. Compare the pros and cons of each tool before starting to use it.

One more thing: You may raise an opinion that Goods is terrible naming, and I agree with you, but at the moment, I have been struggling to choose a domain field for this article.

Please feel free to give any comments if you have any. Share your experience with jOOQ in the comments.

Blog

Spring Boot & JOOQ | Getting started

Yegor Voronianskii

Conclusion

References

Join Our Newsletter. No Spam, Only the good stuff.

Related