Yet another blog

Database with Flyway and jOOQ

Sam's photo
Sam
·

8 min read

Introduction

Here we discuss how to quickly setup a Java project - that connects to a relational database system (RDBMS) - with Flyway and jOOQ.
The goal is to have a simple project setup - that can be adapted to many database systems - and that can take advantage of what both jOOQ and Flyway have to offer.

Scenario and setup

We are using a H2 database, however the same setup can be used with PostgreSQL, MySQL or other RDBMS.

The idea is to have a project template (or a couple of templates) which is simple and can be used repeatedly to work efficiently with relational database systems.

Throughout this series we'll discuss alternatives.

Flyway is a database migration tool, we'll use it to create and populate a database table.

Wikipedia describes jOOQ as follow:

"jOOQ, is a light database-mapping software library in Java".

We'll use Gradle to build the project. That will allow us to use the flyway gradle plugin and jOOQ gradle plugin.
Again, we might discuss a different setup in subsequent articles.

Dependencies

We have Java 17 installed and, on this occasion, are using the Gradle Groovy DSL (as opposed to the Kotlin DSL).
Below is the project directory structure.

If you ignore the 'db' directory, that is a typical Gradle project structure.

In the build.gradle file, we need to add the 'flyway gradle plugin' and the H2 database dependency.

plugins {
    id("org.flywaydb.flyway") version("10.4.0")
}
dependencies {
    implementation("com.h2database:h2:2.2.220")
}

The flyway 'gradle quickstart guide' advises to add the h2/database dependency to the buildscript block, however we are not doing that here.
If any issue(s) when running the migrations, you can follow the guide's advice.

We then configure Flyway

flyway {
    url = 'jdbc:h2:file:~/.h2Databases/testDB'
    user = 'sa'
    locations = ['filesystem:db/migration']
}

The database will be saved in a file (testDB), the migrations are located in the 'db/migration' directory.
I don't put the migrations on the classpath, for example in the resources directory, because I don't want them to be packaged in the final build artefact (jar file).

Creating the first migrations

We use the same migrations as the 'flyway gradle quickstart guide'.
The database schema here is simple however it can easily be changed to suit more complicated scenarios.
The migrations are db/migration/V1__Create_person_table.sql:

create table PERSON (
    ID int not null,
    NAME varchar(100) not null
);

And db/migration/V2__Insert_into_person_table.sql:

insert into PERSON (ID, NAME) values (1, 'Axel');
insert into PERSON (ID, NAME) values (2, 'Mr. Foo');
insert into PERSON (ID, NAME) values (3, 'Ms. Bar');

We are now ready to run the migrations, by running the following:

./gradlew flywayMigrate -i

The output should be similar to:

> Task :flywayMigrate
.
.
.
Database: jdbc:h2:file:~/.h2Databases/testDB (H2 2.2)
Schema history table "PUBLIC"."flyway_schema_history" does not exist yet
Successfully validated 2 migrations (execution time 00:00.003s)
Creating Schema History table "PUBLIC"."flyway_schema_history" ...
Current version of schema "PUBLIC": << Empty Schema >>
Migrating schema "PUBLIC" to version "1 - Create person table"
Migrating schema "PUBLIC" to version "2 - Insert into person table"
Successfully applied 2 migrations to schema "PUBLIC", now at version v2 (execution time 00:00.003s)

Now that the migrations ran and the database table has been created, we are going to setup jOOQ to generate classes based on what is in the database.
We add the jOOQ gradle plugin (the OSS version, as opposed to the Enterprise one):

plugins {
    id ("nu.studer.jooq") version("9.0")
}

The jOOQ gradle plugin page advises do add the database driver to the jooqGenerator configuration.

dependencies {
    jooqGenerator("com.h2database:h2:2.2.220")
}

We then configure jOOQ.
Below is what I think is a minimal configuration.
I wasn't sure whether the generate block could be omitted and I've added it regardless (you can try without it).

jooq {
    configurations {
        main {
            generationTool {
                jdbc {
                    driver = 'org.h2.Driver'
                    url = 'jdbc:h2:file:~/.h2Databases/testDB'
                    user = 'sa'
                    password = ''
                }
                generator {
                    name = 'org.jooq.codegen.DefaultGenerator'
                    database {
                        name = 'org.jooq.meta.h2.H2Database'
                        inputSchema = 'PUBLIC'
                    }
                    generate {
                        deprecated = false
                        records = true
                        immutablePojos = true
                        fluentSetters = true
                    }
                    target {
                        packageName = 'net.sammy'
                        directory = 'build/generated-src/jooq/main'
                    }
                }
            }
        }
    }
}

We can now run the generateJooq task:

./gradlew generateJooq

You should see an output similar to:

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@  @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@        @@@@@@@@@@
@@@@@@@@@@@@@@@@  @@  @@    @@@@@@@@@@
@@@@@@@@@@  @@@@  @@  @@    @@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@    @@  @@  @@@@  @@@@@@@@@@
@@@@@@@@@@    @@  @@  @@@@  @@@@@@@@@@
@@@@@@@@@@        @@  @  @  @@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@  @@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  Thank you for using jOOQ 3.19.1 (Build date: 2023-12-20T14:13:25Z)
.
.
.      
19:07:46 INFO Generation finished: PUBLIC: Total: 891.922ms, +0.768ms
.
.
.
BUILD SUCCESSFUL in 3s
1 actionable task: 1 executed

All is now ready to start coding and querying the database.
We'll start with the Java JDBC API.

One more thing; we are going to run the code in test methods, just so that we can use the IDE to launch them quickly.

Therefore we are going to add JUnit to our build file:

dependencies {
    testImplementation 'org.junit.jupiter:junit-jupiter-api:5.10.0'
    testRuntimeOnly 'org.junit.jupiter:junit-jupiter-engine:5.10.0'
}

test {
    useJUnitPlatform()
}

We create a first test using 'raw sql', which means using strings to create the SQL statement.
There could be typos if we don't first test the SQL in a DB editor.
With the ResultSet, column's name or index can be used; note that - with JDBC - the index starts at 1.

    final static String DB_URL = "jdbc:h2:file:~/.h2Databases/testDB";

    @Test
    void testUsingRawSql() {
        try (Connection conn = DriverManager.getConnection(DB_URL, "sa", "");
             Statement statement = conn.createStatement()) {

            ResultSet resultSet = statement.executeQuery("select * from PERSON");

            while (resultSet.next()) {
                System.out.print(resultSet.getInt(1) + " ");
                System.out.println(resultSet.getString(2));
                System.out.print(resultSet.getInt("ID") + " ");
                System.out.println(resultSet.getString("NAME"));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

We then take advantage of jOOQ, which is our main purpose :-).
We don't have to use strings here, jOOQ has a rich API, and - with the classes generated with ./gradlew generateJooq - we can create type safe SQL statements.
The IDE offers code completion and we have access to tables' name and columns.
Note that, when using column indices, getValue(1) refers to what would be column 2 with the JDBC API.

    @Test
    void testFetchUsingJooq() {
        try (Connection conn = DriverManager.getConnection(DB_URL, "sa", "")) {
            DSLContext context = DSL.using(conn, SQLDialect.H2);
            Result<Record> result = context.select().from(Person.PERSON).fetch();
            List<String> listOfNames = result.map(record -> record.getValue(Person.PERSON.NAME));
            System.out.println(listOfNames);
            result.forEach(record -> System.out.println(record.getValue(1)));
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

org.jooq.Result can do a lot for us; use the 'auto completion' feature of your IDE for suggested methods.
It is a 'collection like' object and it has a lot of convenient methods.
From org.jooq.Result, you can easily hydrate domain objects.
Think of org.jooq.Record as tuples, they are not Java record.

To sum up, Result can be thought of as an iterable or list, and Record can be thought of as a tuple.
However it is just an analogy, Result and Record offer methods not found on iterable and tuple respectively.

Discussion

Our main goal was to take advantage of jOOQ; and see how that could help us, simply and rapidly, to interact with a database system.
Among other things, jOOQ helps us with type safe SQL queries and iterating over the results.

We'll try various setups and other tools as well as libraries throughout this series.
We'll compare and discuss some of the pros and cons.

Some of the pros here

Using jOOQ is a great plus, I think their website does a good job at explaining some of the benefits.
It would probably be my 'go to' choice when not using an ORM or JPA.
And even when using an ORM, I might use jOOQ in conjunction.

Some of the things I would do differently

In a 'real world' project, I would probably separate the migrations and application code.
That would mean having one project with Flyway, to run the migrations, and another project with jOOQ to generate jOOQ classes and run the application code.

One thing also to bear in mind is that Flyway uses migrations written in SQL.
Each RDBMS uses their own dialect of SQL, meaning that the migrations are tailored to the database system you are using.
You might have to edit or rewrite them if you change RDBMS.

Conclusion

In this article we have setup and configured a simple project to use Flyway, to handle database migrations, and jOOQ, to interact with the database.
jOOQ is an invaluable tool and has a rich Java API to query or manipulate data in database systems.
The API is type safe and 'feels like' using SQL.
jOOQ can be used by itself or with JPA and could become one of the preferred choices of Java/JVM developers.

Full Listing

Code available on Github

Below are the directory structure, the full build.gradle and ApplicationTest.java files.

// build.gradle
plugins {
    id("application")
    id("org.flywaydb.flyway") version("10.4.0")
    id ("nu.studer.jooq") version("9.0")
}

version = "0.1"
group = "net.sammy"

repositories {
    mavenCentral()
}

dependencies {
    implementation("com.h2database:h2:2.2.220")
    jooqGenerator("com.h2database:h2:2.2.220")
    testImplementation 'org.junit.jupiter:junit-jupiter-api:5.10.0'
    testRuntimeOnly 'org.junit.jupiter:junit-jupiter-engine:5.10.0'
}

application {
    mainClass.set("net.sammy.Application")
}

java {
    sourceCompatibility = JavaVersion.toVersion("17")
    targetCompatibility = JavaVersion.toVersion("17")
}

test {
    useJUnitPlatform()
}

flyway {
    url = 'jdbc:h2:file:~/.h2Databases/testDB'
    user = 'sa'
    locations = ['filesystem:db/migration']
}

jooq {
    configurations {
        main {
            generationTool {
                jdbc {
                    driver = 'org.h2.Driver'
                    url = 'jdbc:h2:file:~/.h2Databases/testDB'
                    user = 'sa'
                    password = ''
                }
                generator {
                    name = 'org.jooq.codegen.DefaultGenerator'
                    database {
                        name = 'org.jooq.meta.h2.H2Database'
                        inputSchema = 'PUBLIC'
                    }
                    generate {
                        deprecated = false
                        records = true
                        immutablePojos = true
                        fluentSetters = true
                    }
                    target {
                        packageName = 'net.sammy'
                        directory = 'build/generated-src/jooq/main'
                    }
                }
            }
        }
    }
}

// ApplicationTest.java
package net.sammy;

import net.sammy.tables.Person;
import org.jooq.*;
import org.jooq.Record;
import org.jooq.impl.DSL;
import org.junit.jupiter.api.Test;

import java.sql.*;
import java.sql.Statement;
import java.util.List;

class ApplicationTest {
    final static String DB_URL = "jdbc:h2:file:~/.h2Databases/testDB";

    @Test
    void testUsingRawSql() {
        try (Connection conn = DriverManager.getConnection(DB_URL, "sa", "");
             Statement statement = conn.createStatement()) {

            ResultSet resultSet = statement.executeQuery("select * from PERSON");

            while (resultSet.next()) {
                System.out.print(resultSet.getInt(1) + " ");
                System.out.println(resultSet.getString(2));
                System.out.print(resultSet.getInt("ID") + " ");
                System.out.println(resultSet.getString("NAME"));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    @Test
    void testFetchUsingJooq() {
        try (Connection conn = DriverManager.getConnection(DB_URL, "sa", "")) {
            DSLContext context = DSL.using(conn, SQLDialect.H2);
            Result<Record> result = context.select().from(Person.PERSON).fetch();
            List<String> listOfNames = result.map(record -> record.getValue(Person.PERSON.NAME));
            System.out.println(listOfNames);
            result.forEach(record -> System.out.println(record.getValue(1)));
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

Did you find this article valuable?

Support Yet another blog by becoming a sponsor. Any amount is appreciated!

JavajvmDatabasesmigrationJDBCjooqflywaygradleh2