linkedin tracking
icon-sprite Created with Sketch.
Skip to main content

Tech Design,  Web


Getting started with Spring Boot, JOOQ, Flyway, and ModelMapper

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:

  1. Load the properties
  2. Run flyway
  3. 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: 8080

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

Sign up for our newsletter


Delivered monthly, featuring great content from our blog!