Summary

The provided content outlines the process of integrating jOOQ with Spring Boot and MySQL to facilitate the creation of type-safe SQL queries and Java classes.

Abstract

The article titled "Configure jOOQ with Spring Boot and MySQL" is a guide that introduces jOOQ as a library for generating Java classes and writing type-safe SQL queries. It provides a step-by-step tutorial on setting up jOOQ in a Spring Boot application, including the addition of necessary dependencies and plugins, configuration of the database, and the generation of POJOs. The tutorial assumes basic knowledge of Spring Boot and MySQL and demonstrates how to create a sample table, configure data sources, and implement services and controllers to interact with the database using jOOQ. It concludes with instructions on running the project and verifying the functionality of the POST and GET APIs, with a reference to a complete source code repository for further guidance.

Opinions

The author presumes that the reader has a foundational understanding of Spring Boot and MySQL, indicating that the tutorial is designed for developers with some experience in these technologies.
The use of jOOQ is presented as beneficial for creating type-safe SQL queries, suggesting that the library enhances code reliability and maintainability.
The tutorial encourages the use of Flyway for database migration, implying a preference or recommendation for this tool in conjunction with jOOQ and Spring Boot.
By providing a link to a complete source code example, the author shows a commitment to practical, hands-on learning and support for the readers.
The inclusion of a sample Book table and corresponding service and controller classes in the tutorial demonstrates the practical application of jOOQ in a real-world scenario, emphasizing the importance of demonstrating theory with concrete examples.

Configure jOOQ with Spring Boot and MySQL

jOOQ is a library that will let you create Java Classes such as POJOs by reading your database and lets you write type-safe SQL queries.

In this tutorial, we won’t go deep into its working instead help you to configure and implement jOOQ into your spring boot application.

If you are using PostgreSQL refer to this story: https://readmedium.com/configure-jooq-with-spring-boot-and-postgresql-c362e41722b9

Prerequisite:

Knowledge of basic spring boot
knowledge of MySQL, make sure you have done installation

Step 1: Add jOOQ & MySQL dependency

<!--    JOOQ   -->
<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-jooq</artifactId>
</dependency>

<!--   MySQL  -->
<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <scope>runtime</scope>
</dependency>

Step 2: Add jOOQ plugin to pom.xml

<plugin>
   <groupId>org.jooq</groupId>
   <artifactId>jooq-codegen-maven</artifactId>
   <executions>
      <execution>
         <id>generate-mysql</id>
         <phase>generate-sources</phase>
         <goals>
            <goal>generate</goal>
         </goals>
         <configuration>
            <!--Insert your DB configuration-->
            <jdbc>
               <driver>com.mysql.cj.jdbc.Driver</driver>
               <url>jdbc:mysql://localhost:3306/jooq</url>
               <user>root</user>
               <password>root</password>
            </jdbc>
            <generator>
               <database>
                  <name>org.jooq.meta.mysql.MySQLDatabase</name>
                  <includes>.*</includes>
                  <excludes></excludes>
                  <inputSchema>jooq</inputSchema>
               </database>
               <generate>
                  <pojos>true</pojos>
                  <pojosEqualsAndHashCode>
                     true
                  </pojosEqualsAndHashCode>
                  <javaTimeTypes>true</javaTimeTypes>
                  <fluentSetters>true</fluentSetters>
               </generate>
               <target>
                  <packageName>
                     com.tej.JooQDemo.jooq.sample.model
                  </packageName>
                  <directory>
                     target/generated-sources/jooq
                  </directory>
               </target>
            </generator>
         </configuration>
      </execution>
   </executions>
</plugin>

Step 3: Configure your database

Make sure you have your database & schema created, I won’t go into database and schema creation.

For testing jOOQ please make sure you have at least one table created.

You can run following script in your MySQL workbench

CREATE TABLE book (
   id int not null auto_increment primary key, 
   title varchar(255),
   author varchar(255)
);

you can use flyway to migrate tables.

If you choose to use flyway follow this story

https://medium.com/@tejozarkar/configure-flyway-with-spring-boot-9493aebf336b

otherwise continue

Now add the following configuration in your application.properties file

spring.datasource.url=jdbc:mysql://localhost:3306/jooq
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

Now just run

mvn clean install

If it runs successfully, you have configured jOOQ and database correctly.

POJOs and other jOOQ classes will be generated in directory /target/generated-sources/jooq

Step 4: Using jOOQ

In your spring boot project, create a new class called BookService

import com.tej.JooQDemo.jooq.sample.model.tables.pojos.Book;

@Service
public class BookService {

    @Autowired
    DSLContext context;

    public List<Book> getBooks(){
       return context
                .selectFrom(Tables.BOOK)
                .fetchInto(Book.class);
    }

    public void insertBook(Book book){
        context
                .insertInto(Tables.BOOK, Tables.BOOK.AUTHOR,
                            Tables.BOOK.AUTHOR)
                .values(book.getTitle(), book.getAuthor())
                .execute();
    }
}

And create a new controller named BookController

import com.tej.JooQDemo.jooq.sample.model.tables.pojos.Book;

@RestController
public class BookController {

    @Autowired
    BookService bookService;

    @GetMapping
    public List<Book> getBooks(){
        return this.bookService.getBooks();
    }

    @PostMapping
    public void postBook(@RequestBody Book book){
        this.bookService.insertBook(book);
    }
}

That’s it, run your project, and check your POST & GET API.

Find the full source code on https://github.com/Tejas-Ozarkar/JooqSpringBootMySQLDemo