Spring Boot & JOOQ | Getting started
Tags: springboot, jooq, liquibase, springdoc
Published: 2024-01-03 11:37:31

---
title: Spring Boot & JOOQ | Getting started
published: true
description: This article will show how to use jOOQ in your spring boot projects.
tags: SpringBoot, jOOQ, Liquibase, SpringDoc
# cover_image: https://direct_url_to_image.jpg
# Use a ratio of 100:42 for best results.
# published_at: 2024-01-03 11:35 +0000
---

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.

![ERD of database](https://cdn-images-1.medium.com/max/2000/1*Fbe-EU7MAd0qdzG7hE5AVw.png)

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.

![Creation of the project in IDE](https://cdn-images-1.medium.com/max/2000/1*P86R1jQcrkKoZ5urOComNA.png)

![List of all dependencies](https://cdn-images-1.medium.com/max/2000/1*2MJv5n9q2N2AO5VJ7fxB-A.png)

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.

![Project structure](https://cdn-images-1.medium.com/max/2000/1*Lli52Z-3oBKJVP9YhZ0gUQ.png)

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.

![Tables created by Liquibase](https://cdn-images-1.medium.com/max/2076/1*t8Zve4cELkC1Tnc2RQtP3A.png)

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.

![IDEA Maven Plugins](https://cdn-images-1.medium.com/max/2000/1*nz-ewVLRKPyuXT1w0bCiNQ.png)

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

![Target folder with generated classes](https://cdn-images-1.medium.com/max/2000/1*Z70sSwXqoSl9iQYZPzXVRw.png)

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.

![](https://cdn-images-1.medium.com/max/4784/1*NorFZN9dwfBwDlGjUxOOBQ.png)

![Swagger UI and creation of goods](https://cdn-images-1.medium.com/max/4728/1*E2ncN4mLzpbKhA8tFj92eQ.png)

## 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.

## References

 1. [Spring Boot jOOQ Documentation](https://docs.spring.io/spring-boot/docs/1.3.5.RELEASE/reference/html/boot-features-jooq.html)

 2. [jOOQ Documentation](https://www.jooq.org/doc/latest/manual/)

 3. [Liquibase Documentation](https://docs.liquibase.com/)

 4. [SpringDoc Documentation](https://springdoc.org/)