Home | Send Feedback

Getting started with jOOQ, Flyway and Spring Boot

Published: January 31, 2019  •  java, spring

If you are new to jOOQ and want to learn the basics, check out Marco Behler's jOOQ course.
In 14 video lessons Marco shows you how to set up jOOQ and explains the basic concepts of the framework. You learn how to use jOOQ with and without the Spring Framework. The course is not free but definitely worth the price.


Flyway

Flyway is a library to manage and execute schema migration scripts against a relational database.

Another similar tool is Liquibase. The main difference between Flyway and Liquibase is how database changes are defined. With Liquibase you write them in a XML document and the library translates them into the SQL dialect of the target database. This is convenient if your project targets multiple relational databases.

With Flyway, you write the changes in native SQL. The library also supports database migrations in Java classes if the changes cannot be expressed in SQL alone.

Both tools are great and do their job. I highly recommend adding a database migration library to every project. There is an initial effort to make but over time this pays off with easier and organised database migrations. And you are going to see in this project that setting up Flyway is not that complicated, because Spring Boot already takes care of the configuration.

jOOQ

jOOQ is a database access library that provides a fluent API for SQL query construction and execution. jOOQ includes generators that generate Java code from your database. This lets you build type safe SQL queries and provides code completion in your IDE.

The library also includes code generators for Data Access Objects (DAO) and POJOs to easily perform CRUD operation in your application. jOOQ does not hide the underlying database like a JPA library. Instead, it embraces it and gives an application full access to all provided SQL features of your database.

Notice that jOOQ is dual-licensed. If you are using an open source database, you can use the Open Source edition of jOOQ, which is released under the Apache 2.0 license, for free. The Open source edition supports the following databases:

If your project uses a commercial database like Oracle and MS SQL Server, you have to buy a license. Check out the pricing page for more information: https://www.jooq.org/download/

In this blog post I'm going to use MariaDB and the Open Source edition of jOOQ. This version is also available from the Maven Central repository.

Getting started

When I create a new Spring Boot application I usually generate the skeleton application with Spring Initializr. For this tutorial I use the HTTP API of Spring Initializr and generate the application with curl:

 curl https://start.spring.io/starter.zip -d dependencies=web,jooq,flyway -d name=sbjooqflyway -d groupId=ch.rasc -d artifactId=sbjooqflyway -d javaVersion=11 -d applicationName=Application --output starter.zip

This command downloads a file called starter.zip. Unzip it into any directory and import it into your favorite IDE.

Most of the heavy lifting for setting up jOOQ and Flyway is done by Spring Boot's auto configurers. Flyway can be used without any further configuration and for jOOQ we only have to configure the code generator, which is optional but highly recommended.


One thing we have to configure is the data source. Open src/main/resources/application.properties and insert the database URL and if required username and password. My setup for MariaDB looks like this.

spring.datasource.url=jdbc:mariadb://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=

application.properties

Add the JDBC driver as dependencies to your project. For a Maven project with MariaDB you add this dependency.

    <dependency>
        <groupId>org.mariadb.jdbc</groupId>
        <artifactId>mariadb-java-client</artifactId>
    </dependency>

pom.xml

For this library you don't have to specify the version because the Spring Boot POM manages the version.


We are ready for creating our first Flyway migration file. This is going to be the file that describes the initial database structure of our application.

In the folder src/main/resources/db/migration create the file V0001__initial.sql. You can choose a different name, you just have to make sure that you follow the Flyway naming pattern

To keep this demo simple and concise I create only one table.

CREATE TABLE employee (
    id          INTEGER NOT NULL AUTO_INCREMENT,
    user_name   VARCHAR(100)    NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(100)    NOT NULL,
    last_name   VARCHAR(100)    NOT NULL,
    gender      CHAR(1)         NOT NULL,
    hire_date   DATE            NOT NULL,
    UNIQUE KEY (user_name),
    PRIMARY KEY(id)
);

V0001__initial.sql

This is just a normal SQL script, there is nothing special going on here. You can use any SQL command that your database supports. When you start the application, Spring Boot automatically looks for files in the src/main/resources/db/migration folder and triggers the Flyway migration. If you don't like the default location, you can change the path in application.properties

#override default
spring.flyway.locations=classpath:migration/flyway

#multiple locations
spring.flyway.locations=classpath:migration/db1,classpath:migration/db2

Sometimes you can't do a migration with SQL only. Maybe there are some complex calculations involved or you need to access an external service to migrate a table. To support this case Flyway allows Java based migration files. Flyway automatically instantiates these special classes and supplies a database connection. Your code then can do whatever is necessary for migrating the database.

To demonstrate this I created a Java migration for the initial data load. The default location for Java based migration files is src/main/java/db/migration. I called my migration file V0002__initial_import.java. The version V0002 ensures that this code runs after the create table in the V0001 file. The name of the Java migration files also has to follow the Flyway naming pattern.

The initial import reads a CSV file with the univocity-parsers and inserts the data into the employee table.

All the database statements inside the migrate() method run inside a transaction and Flyway automatically commits the transaction when the method finishes without any error.

public class V0002__initial_import extends BaseJavaMigration {

  @Override
  public void migrate(Context context) throws Exception {
    ClassPathResource cpr = new ClassPathResource("employees.csv");
    try (InputStream is = cpr.getInputStream()) {

      CsvParserSettings settings = new CsvParserSettings();
      settings.getFormat().setDelimiter(',');
      settings.getFormat().setQuote('"');
      CsvParser parser = new CsvParser(settings);

      List<String[]> rows = parser.parseAll(is);

      try (DSLContext dsl = using(context.getConnection())) {
        for (String[] row : rows) {
          dsl.insertInto(table("employee"), field("user_name"), field("birth_date"),
              field("first_name"), field("last_name"), field("gender"),
              field("hire_date"))
              .values(row[0], DSL.cast(row[1], Date.class), row[2], row[3], row[4],
                  DSL.cast(row[5], Date.class))
              .execute();
        }
      }
    }
  }
}

V0002__initial_import.java

The code in this migration uses jOOQ as query builder and executor, but does not use generated jOOQ code, which we will generate in a second. The reason for this is that over time your database schema can and will change and so does the generated jOOQ code. If our migration code depends on generated classes we run into the situation that we reference columns or tables in an older migration class that are no longer available. For instance, if we decide in a future version to remove the gender column, our migration code would no longer compile because of the missing gender definition in the generated jOOQ classes.

Admittingly, writing queries this way has other drawbacks, it can lead to errors and problems when you misspell table or column names.

For the main application we definitely want to take advantage of jOOQ's code generator. It allows us to write type safe queries, gives us code completion in the IDE and with the generated POJOs and DAOs we can very easily implement CRUD functionality into our application.

The skeleton Spring Boot application did not add a code generator, because there are different ways how to generate jOOQ code.

For this demo application I choose to generate code with the jOOQ Maven plugin using the DDLDatabase extension, which generates code from the Flyway scripts.

I added the following code to my pom.xml. I use a profile here because I don't want run the generator each time I run a build. In my experience the database does not change every day but you might build an application multiple times a day.

The generator is configured this way that it reads the Flyway migration files from src/main/resources/db/migration/* and writes the code into target/generated-sources/jooq.
I also enabled POJO (<pojos>true</pojos>) and DAO (<daos>true</daos>) generation.

  <profiles>
    <profile>
      <id>jooq</id>
      <build>
        <plugins>
          <plugin>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-codegen-maven</artifactId>
            <version>3.11.11</version>
            <executions>
              <execution>
                <id>generate-jooq-sources</id>
                <phase>generate-sources</phase>
                <goals>
                  <goal>generate</goal>
                </goals>
                <configuration>
                  <generator>
                    <generate>
                      <pojos>true</pojos>
                      <daos>true</daos>
                      <pojosEqualsAndHashCode>true</pojosEqualsAndHashCode>
                      <javaTimeTypes>true</javaTimeTypes>
                    </generate>
                    <database>
                      <name>org.jooq.meta.extensions.ddl.DDLDatabase</name>
                      <inputCatalog></inputCatalog>
                      <inputSchema>PUBLIC</inputSchema>
                      <outputSchemaToDefault>true</outputSchemaToDefault>
                      <outputCatalogToDefault>true</outputCatalogToDefault>
                      <properties>                    
                        <property>
                          <key>sort</key>
                          <value>semantic</value>
                        </property>
                        <property>
                          <key>scripts</key>
                          <value>src/main/resources/db/migration/*</value>
                        </property>
                      </properties>
                    </database>
                    <target>
                      <clean>true</clean>
                      <packageName>ch.rasc.sbjooqflyway.db</packageName>
                      <directory>target/generated-sources/jooq</directory>
                    </target>
                  </generator>
                </configuration>
              </execution>
            </executions>
            <dependencies>
              <dependency>
                <groupId>org.jooq</groupId>
                <artifactId>jooq-meta-extensions</artifactId>
                <version>3.11.11</version>
              </dependency>
            </dependencies>
          </plugin>
        </plugins>
      </build>
    </profile>
  </profiles>

pom.xml

To start the jOOQ generator run this command:

mvnw generate-sources -P jooq

If no error occurs, you should find new classes in target/generated-sources/jooq. Add this path to the source path in your IDE, because the application code will reference these classes.

This is just one way how to generate jOOQ code. There are other ways to do that, like generating from a database or JPA entities. You could also write the generated code into the src folder and commit it, together with your other code, into the version control system.

Read more about code generation on the official documentation page:
https://www.jooq.org/doc/3.11/manual-single-page/#code-generation


With the generated jOOQ code we can start writing the business logic. For this demo I created the following simple rest controller. Two beans DSLContext dsl and Configuration jooqConfiguration are injected into this controller. Both beans are auto created by Spring Boot during start up. The configuration instance is needed for instantiating the generated DAO classes and the DSLContext instance is used for writing and executing SQL queries.

@RestController
public class EmployeeController {

  private final EmployeeDao employeeDao;

  private final DSLContext dsl;

  private final TransactionTemplate transactionTemplate;

  public EmployeeController(DSLContext dsl, Configuration jooqConfiguration,
      TransactionTemplate transactionTemplate) {
    this.employeeDao = new EmployeeDao(jooqConfiguration);
    this.dsl = dsl;
    this.transactionTemplate = transactionTemplate;
  }

  @GetMapping("/listEmployees")
  public List<Employee> employees() {
    return this.employeeDao.findAll();
  }

  @PostMapping("/deleteEmployee")
  public void delete(@RequestParam("id") int id) {
    this.employeeDao.deleteById(id);
  }

  @PostMapping("/newEmployee")
  public Employee newEmployee(@RequestBody Employee newEmployee) {
    this.employeeDao.insert(newEmployee);
    System.out.println(newEmployee.getId());
    return newEmployee;
  }

  @PostMapping("/updateEmployee")
  public Employee updateEmployee(@RequestBody Employee newEmployee) {
    this.employeeDao.update(newEmployee);
    return newEmployee;
  }

EmployeeController.java

All the methods above use the DAO, but you can also write and execute arbitrary SQL with the DSLContext.

  @GetMapping("/findEmployees/{name}")
  public Integer[] findEmployees(@PathVariable("name") String name) {
    return this.dsl
            .select(EMPLOYEE.ID)
            .from(EMPLOYEE)
            .where(EMPLOYEE.FIRST_NAME.contains(name).or(EMPLOYEE.FIRST_NAME.contains(name)))
            .fetchArray(EMPLOYEE.ID);
  }

  @GetMapping("/countEmployees")
  public int count() {
    return this.dsl.fetchCount(EMPLOYEE);
  }

EmployeeController.java

For this example I created the EmployeeDao instance in the constructor of the controller. Alternatively you can configure the EmployeeDao as Spring managed bean and then inject that bean into other services. This is a better approach if you need to access the DAO in multiple Spring managed controllers or services.

import org.springframework.context.annotation.Bean;
import org.jooq.Configuration;
import ch.rasc.sbjooqflyway.db.tables.daos.EmployeeDao;

@org.springframework.context.annotation.Configuration
public class SomeConfigClass {

  @Bean
  public EmployeeDao employeeDao(Configuration jooqConfiguration) {
    return new EmployeeDao(jooqConfiguration);
  }
}

Transactions

In the examples above we used the auto commit mode from JDBC for transaction handling. In this mode every update sent the database is immediately made permanent.

As soon as you send multiple updates to the database and expect them to run in a all-or-nothing fashion you have to start a transaction.

In a Spring application with jOOQ you can achieve that in multiple ways.

With the declarative way you annotate the method that has to run inside a database transaction with @Transactional and Spring takes care of starting a transaction before it calls the method and committing the transaction after the method returns without any error or rolling back the transaction when the method throws an exception.

In the following example nothing will be inserted because the method throws a null pointer exception after the first insert. If you remove @Transactional the first insert will succeed because in that case the JDBC auto commit mode is used.

  @GetMapping("/insertMultiple1")
  @Transactional
  public void insertMultiple1() {
    Employee e1 = new Employee();
    e1.setFirstName("1");
    e1.setLastName("1");
    e1.setUserName("1");
    e1.setBirthDate(LocalDate.now());
    e1.setGender("M");
    e1.setHireDate(LocalDate.now());
    this.employeeDao.insert(e1);

    if (true) {
      throw new NullPointerException();
    }

    Employee e2 = new Employee();
    e2.setFirstName("2");
    e2.setLastName("2");
    e2.setUserName("2");
    e2.setBirthDate(LocalDate.now());
    e2.setGender("M");
    e2.setHireDate(LocalDate.now());
    this.employeeDao.insert(e2);
  }

EmployeeController.java

Another approach is using the programmatic approach. Either with Spring's TransactionTemplate or with jOOQ's own transaction support. The programmatic approach allows for a more fine-grained control over the transactions.

For using Spring's programmatic transaction management you need to inject the TransactionTemplate and then supply your code as lambda block to the transactionTemplate.execute() method. The whole block will run within a transaction and Spring automatically commits the transaction or rolls it back in case of an error.

	private final TransactionTemplate transactionTemplate;

	public EmployeeController(..., TransactionTemplate transactionTemplate) {
    ...
	  this.transactionTemplate = transactionTemplate;
	}

	@GetMapping("/insertMultiple2")
	public void insertMultiple2() {
	  this.transactionTemplate.execute(txStatus -> {
		Employee e1 = new Employee();
		e1.setFirstName("1");
		...
		this.employeeDao.insert(e1);


		Employee e2 = new Employee();
		e2.setFirstName("2");
		...
		this.employeeDao.insert(e2);


		return null;
	  });
	}

The other option is using jOOQ's programmatic transaction support. Similar to the Spring template approach you run your code as lambda block inside the dsl.transaction() method.

Be aware that you can't use dsl and employeeDao from the outer scope. You have to create a new DAO with the configuration object (txConf) the method passed to the lambda and use DSL.using(...) if you need to create and execute a SQL query.

Like the other two approaches above, transaction() automatically commits or rolls back the transaction.

	@GetMapping("/insertMultiple3")
	public void insertMultiple3() {

	  this.dsl.transaction(txConf -> {
		  EmployeeDao txEd = new EmployeeDao(txConf);
			
		  Employee e1 = new Employee();
		  e1.setFirstName("1");
		  ...
		  txEd.insert(e1);


		  int count = DSL.using(txConf).fetchCount(EMPLOYEE);
		  System.out.println(count);

			
		  Employee e2 = new Employee();
		  e2.setFirstName("2");
		  ...
		  txEd.insert(e2);
	  });
	}

Migration

In the last section we follow a typical migration workflow.

Our client sends us a new requirement that the application should not only manage employees but also departments. Additionally, each employee needs to be assigned to a department.

We start by creating a new SQL migration file: src/main/resources/db/migration/V0003__add_department.sql

In this file we create the new department table, insert all available departments, and create a foreign key department_id in the employee table.

We want to make the department_id column not nullable because it is a mandatory field in our application. We can't just specify NOT NULL when we call ADD COLUMN because there are already rows stored in this table. Therefore, we assign the artificial Unset department to all employees and then change the column definition to NOT NULL.

CREATE TABLE department (
    id    INTEGER NOT NULL AUTO_INCREMENT,
    no    CHAR(4)     NOT NULL,
    name  VARCHAR(40) NOT NULL,
    PRIMARY KEY(id)
);

INSERT INTO department(no, name) VALUES 
  ('d001','Marketing'),
  ('d002','Finance'),
  ('d003','Human Resources'),
  ('d004','Production'),
  ('d005','Development'),
  ('d006','Quality Management'),
  ('d007','Sales'),
  ('d008','Research'),
  ('d009','Customer Service'),
  ('dddd','Unset');
  
ALTER TABLE employee ADD COLUMN department_id INTEGER; 
UPDATE employee SET department_id = (SELECT id FROM department where no = 'dddd');
ALTER TABLE employee MODIFY department_id INTEGER NOT NULL;

V0003__add_department.sql

Our client also sent us an Excel that contains the current department assignments for each employee. To import this Excel file we create a Java Flyway migration class src/main/java/db/migration/V0004__assign_department.java

The first part of the migrate method reads the contents of the Excel into a Map

    Map<String, String> userToDepartment = new HashMap<>();

    ClassPathResource cpr = new ClassPathResource("employee_department.xlsx");
    try (InputStream is = cpr.getInputStream();
        Workbook wb = WorkbookFactory.create(is)) {

      Sheet sheet = wb.getSheetAt(0);
      if (sheet != null) {
        Iterator<Row> rowIt = sheet.rowIterator();
        while (rowIt.hasNext()) {
          Row row = rowIt.next();
          userToDepartment.put(row.getCell(0).getStringCellValue(),
              row.getCell(1).getStringCellValue());
        }
      }
    }

V0004__assign_department.java

and the second part links the department_id column in the employee table to the department. Afterwards it checks if every employee has a valid department assigned to it and deletes the special Unset department we created earlier.
If there are still employees assigned to the Unset department, somebody has to manually fix them.

    try (DSLContext dsl = using(context.getConnection())) {
      for (Map.Entry<String, String> entry : userToDepartment.entrySet()) {
        dsl.update(table("employee"))
            .set(field("department_id", Integer.class), 
                 select(field("id", Integer.class))
                 .from(table("department"))
                 .where(field("no", String.class).eq(entry.getValue())).limit(1).asField())
            .where(field("user_name", String.class).eq(entry.getKey())).execute();
      }

      int employeesWithUnsetDep = dsl.selectCount()
          .from(table("employee"))
          .innerJoin(table("department"))
          .on(field("department.id", Integer.class)
              .equal(field("employee.department_id", Integer.class)))
          .where(field("department.no", String.class).eq("dddd"))
          .fetchOne(0, Integer.class);

      if (employeesWithUnsetDep == 0) {
        dsl.delete(table("department")).where(field("no").eq("dddd")).execute();
      }

    }

V0004__assign_department.java

Finally, run the jOOQ code generator again so we can start writing code for the new department table.

mvnw generate-sources -P jooq
@RestController
public class DepartmentController {
  private final DepartmentDao departmentDao;

  private final DSLContext dsl;

  public DepartmentController(DSLContext dsl, Configuration jooqConfiguration) {
    this.departmentDao = new DepartmentDao(jooqConfiguration);
    this.dsl = dsl;
  }

  @GetMapping("/departments")
  public List<Department> departments() {
    return this.departmentDao.findAll();
  }

  @GetMapping("/department/{no}")
  public Department findDepartment(@PathVariable("no") String no) {
    return this.dsl
            .selectFrom(DEPARTMENT)
            .where(DEPARTMENT.NO.eq(no))
            .fetchOne()
            .into(Department.class);
  }
}

DepartmentController.java


You find the complete source for this application on GitHub:
https://github.com/ralscha/blog2019/tree/master/sbjooqflyway


This concludes this blog post about Flyway and jOOQ. A powerful couple for Java database application development.

For further information visit the homepages of the projects:

Visit the Spring Boot documentation for more information about the integration: