On a recent Spring project I decided to give JOOQ a try. Once I figured out how to configure my pom.xml file to run both Flyway and JOOQ, and got used to the JOOQ syntax and object model, I soon decided that I would never go back to using an ORM. Where an ORM strives to hide SQL from you, JOOQ embraces SQL giving you a DSL that wraps your database in easy-to-use and type safe Java objects. If you want to give JOOQ a try, this blog is meant to get you up and running quickly with Spring Boot, JOOQ, and Flyway. Full source code for this project can be found here.
1. Configuring the pom.xml file.
Let’s start with the configuration of the pom.xml file. We need to make sure that Flyway runs before JOOQ so that JOOQ has the latest database from which to generate code. And both need the properties used to connect to the database. I prefer yml, so I use a library to load the application.yml properties. So the build order is:
- Load the properties
- Run flyway
- Run JOOQ Code gen
First we import the required libraries
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>it.ozimov</groupId>
<artifactId>yaml-properties-maven-plugin</artifactId>
<version>${yaml-properties-maven-plugin.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jooq</artifactId>
</dependency>
<dependency>
<groupId>org.modelmapper</groupId>
<artifactId>modelmapper</artifactId>
<version>1.1.3</version>
</dependency>
<dependency>
<groupId>org.modelmapper.extensions</groupId>
<artifactId>modelmapper-jooq</artifactId>
<version>1.1.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
</dependencies>Then we configure the build order
<build>
<pluginManagement>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<groupId>it.ozimov</groupId>
<artifactId>yaml-properties-maven-plugin</artifactId>
<version>${yaml-properties-maven-plugin.version}</version>
<executions>
<execution>
<phase>initialize</phase>
<goals>
<goal>read-project-properties</goal>
</goals>
<configuration>
<files>
<file>src/main/resources/application.yml</file>
</files>
</configuration>
</execution>
</executions>
</plugin>
<plugin>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-maven-plugin</artifactId>
<!-- Note that we're executing the Flyway plugin in the "generate-sources"
phase -->
<executions>
<execution>
<phase>generate-sources</phase>
<goals>
<goal>migrate</goal>
</goals>
</execution>
</executions>
<!-- Note that we need to prefix the db/migration path with filesystem:
to prevent Flyway from looking for our migration scripts only on the classpath -->
<configuration>
<driver>${spring.datasource.driverClassName}</driver>
<url>${spring.datasource.url}</url>
<user>${spring.datasource.username}</user>
<password>${spring.datasource.password}</password>
<locations>
<location>filesystem:src/main/resources/db/migration</location>
</locations>
</configuration>
</plugin>
<!-- The jOOQ code generator plugin -->
<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>
<jdbc>
<driver>${spring.datasource.driverClassName}</driver>
<url>${spring.datasource.url}</url>
<user>${spring.datasource.username}</user>
<password>${spring.datasource.password}</password>
</jdbc>
<generator>
<database>
<name>org.jooq.meta.mysql.MySQLDatabase</name>
<includes>.*</includes>
<excludes></excludes>
<inputSchema>springjooq</inputSchema>
</database>
<generate>
<deprecated>false</deprecated>
<instanceFields>true</instanceFields>
<pojos>false</pojos>
</generate>
<target>
<packageName>co.cantina.spring.jooq.sample.model</packageName>
<directory>target/generated-sources/jooq</directory>
</target>
</generator>
</configuration>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.6</version>
</dependency>
</dependencies>
</plugin>
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>build-helper-maven-plugin</artifactId>
<executions>
<execution>
<id>add-source</id>
<phase>generate-sources</phase>
<goals>
<goal>add-source</goal>
</goals>
<configuration>
<sources>
<source>${project.build.directory}/generated-sources/jooq/</source>
</sources>
</configuration>
</execution>
</executions>
</plugin>
</plugins>
</pluginManagement>
</build>The properties are configured at src/main/resources/db/application.yml
spring:
datasource:
url: jdbc:mysql://localhost:3306/springjooq?autoReconnect=true&serverTimezone=UTC
username: springjooquser
password: password
driver-class-name: com.mysql.cj.jdbc.Driver
server:
port: 80802. Configuring Flyway
Next we configure flyway. In this script we’ll create a single table for illustrative purposes.
src/main/resources/db/migration/V1.001__create_books_table.sql
create table books (
id int not null auto_increment,
title varchar(255),
primary key (id)
);3. Create a domain object
Next we’ll make a POJO that we will convert to JSON to pass to the front end. Note JOOQ can automatically generate a set of POJOs for you. If the JSON model that is returned is the same as the database, this can save time. In my case there were differences between the JSON the front end was expecting and the database tables, so I created my own POJOs. With lombok the writing of these POJOs is fairly painless.
src/main/java/co/cantina/springjooq/domain/Book.java
package co.cantina.springjooq.domain;
import lombok.Data;
@Data
public class Book {
private String title;
}4. Setup Model Mapper
In order to allow services to autowire the ModelMapper, we add the Bean to the Application.
src/main/java/co/cantina/springjooq/SpringJooqApplication.java
package co.cantina.springjooq;
import org.modelmapper.ModelMapper;
import org.modelmapper.convention.NameTokenizers;
import org.modelmapper.jooq.RecordValueReader;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
@SpringBootApplication
public class SpringJooqApplication {
public static void main(String[] args) {
SpringApplication.run(SpringJooqApplication.class, args);
}
@Bean
public ModelMapper modelMapper() {
ModelMapper mapper = new ModelMapper();
mapper.getConfiguration().setSourceNameTokenizer(NameTokenizers.UNDERSCORE)
.addValueReader(new RecordValueReader());
return mapper;
}
}5. Create the service.
The Book Service will connect through JOOQ to access the data in the database. The JOOQ objects returned from a query will be mapped into our domain object using Model Mapper.
src/main/java/co/cantina/springjooq/services/BookService.java
package co.cantina.springjooq.services;
import java.util.List;
import java.util.stream.Collectors;
import org.jooq.DSLContext;
import org.modelmapper.ModelMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import co.cantina.spring.jooq.sample.model.Tables;
import co.cantina.springjooq.domain.Book;
@Service
public class BookService {
@Autowired
private ModelMapper mapper;
@Autowired
DSLContext dsl;
public List<Book> getBooks() {
return dsl
.selectFrom(Tables.BOOKS)
.fetch()
.stream()
.map(e -> mapper.map(e, Book.class))
.collect(Collectors.toList());
}
}6. Create a controller
Finally we create a controller that uses our service to get data from the database and returns JSON.
src/main/java/co/cantina/springjooq/controllers/ApiController.java
package co.cantina.springjooq.controllers;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import co.cantina.springjooq.services.BookService;
import co.cantina.springjooq.domain.Book;
@RestController
@RequestMapping("/api/v1")
public class ApiController {
@Autowired
BookService bookService;
@GetMapping(value ="/books", produces = "application/json")
@ResponseBody
public List<Book> getBooks() {
return this.bookService.getBooks();
}
}And that’s it. You should now have a basic project stood up using Spring Boot, Flyway, JOOQ, and ModelMapper. Again full source code can be found here.
