Liquibase and Jooq

For a recent project, we had to develop a web application with the following requirements: the server had to work “online” with connections to the main (Oracle) database, but it had also to work on a laptop with a local database in an “offline” mode having a subset of the functionality of the online mode.

For the offline case, we settled on HSQLDB [1] in file-based embedded mode. To manage our schemata, we choosed Liquibase [2] and to program our queries, we used Jooq [3].

The constraint of the web application to run on both HSQLDB and Oracle restricted us in the use of SQL because we could not use the fancy operators that Oracle provides. But it had also positive effects like allowing file-based HSQLDB databases for Jooq classes generation during the build process and in-memory embedded HSLQDB for a large part of our integration tests.

In this post, we will illustrate our build process — we will cover the integration tests in another post. The examples used in this post can be found in a sample project in a github repository [4]. The free version of Jooq cannot work with Oracle databases, but the same principles apply with another “large” DBMS like Postgresql. In any case, I suggest acquiring a license if only to support that great library.

The sample project uses Maven for the build. The generation of the Jooq classes will be done in two steps:

  1. Generate a file-based HSQLDB database from Liquibase;
  2. Generate the Jooq classes from the database created in the first step.

Liquibase setup

We first define the JDBC url that will be used by both the liquibase and the Jooq plugins as Maven properties.

<properties>
    <dbpath>${project.build.directory}/liquibase/db;shutdown=true</dbpath>
    <jdbcUrl>jdbc:hsqldb:${dbpath}</jdbcUrl>
</properties>

The ;shutdown=true directive is very important to ensure that the file-based database is properly shutdown as soon as its last connection closes: neither the liquibase plugin nor the jooq plugin expects to clean up the database after they have finished working.

The liquibase schema definition files are found in the resources folder and are organised as follows

  1. a schema.xml file that contains only DDL directives.
  2. an hsqldb.xml file that contains HSQLDB specific directives.
  3. an oracle.xml file that contains Oracle specific directives.

Notice that, in the schema.xml file, we create two tables in different schemata that are referenced with the liquibase variables ${schema1} and ${schema2}; the type of the column FLAG of the table A is of boolean type and this boolean type is also reference via a liquibase variable, ${boolean.type} in this case, in order to control precisely how the booleans are mapped in HSQLDB and Oracle.

The hsqldb.xml file sets this three variables for the HSQLDB case, ensure that liquibase is run with the SA user (super-administrator) and also add a liquibase change set to bootstrap the schemata. It makes sense to require the password-less SA user because we generate the HSQLDB database on the fly.

For Oracle, in the oracle.xml file, we set only the boolean type as varchar(1) (that will be translated to varchar2(1) by liquibase). The schema names for ${schema1} and ${schema2} have to be provided externally when running liquibase against an Oracle database: you may wish to create the 2 schemata many times on the same Oracle instance.

Now that the liquibase schema files are in place, we can use the hsqldb.xml file to generate a file-based HSLQDB from our pom.xml file using the liquibase plugin. The liquibase plugin section is reproduced below.

<plugin>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-maven-plugin</artifactId>
    <version>3.2.2</version>
    <configuration>
        <changeLogFile>src/main/resources/db_schema/hsqldb.xml</changeLogFile>
        <driver>org.hsqldb.jdbc.JDBCDriver</driver>
        <url>${jdbcUrl}</url>
        <username>sa</username>
        <password></password>
        <promptOnNonLocalDatabase>false</promptOnNonLocalDatabase>
    </configuration>
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>update</goal>
            </goals>
        </execution>
    </executions>
</plugin>

Notice that we execute the liquibase plugin during during source generation so that the file-based HSQLDB database is ready for the Jooq classes generation. Also, we configure the option promptOnNonLocalDatabase to be false to avoid being prompted by liquibase during the build process.

Jooq Setup

We can now proceed to the Jooq generation which simply follows the instructions given in the Jooq manual. The relevant plugin session of the pom.xml file is reproduced below.

<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>3.4.2</version>
    <executions>
        <execution>
            <goals>
                <goal>generate</goal>
            </goals>
        </execution>
    </executions>
    <dependencies>
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>2.3.2</version>
        </dependency>
    </dependencies>
    <configuration>
        <jdbc>
            <driver>org.hsqldb.jdbc.JDBCDriver</driver>
            <url>${jdbcUrl}</url>
            <user>sa</user>
            <password></password>
        </jdbc>
        <generator>
            <name>org.jooq.util.DefaultGenerator</name>
            <database>
                <name>org.jooq.util.hsqldb.HSQLDBDatabase</name>
                <includes>.*</includes>
                <excludes></excludes>
                <schemata>
                    <schema><inputSchema>SCHEMA1</inputSchema></schema>
                    <schema><inputSchema>SCHEMA2</inputSchema></schema>
                </schemata>
            </database>
            <target>
                <packageName>ch.hood.jooq</packageName>
                <directory>target/generated-sources/jooq</directory>
            </target>
        </generator>
    </configuration>
</plugin>

Notice the schemata section to generate sources from more that one schema.

Jooq will generate classes from both schemata under their HSQLDB name. As we hinted above, when installing the project on an Oracle instance, it is possible to use different schema names. To reflect those names in the code, it is then necessary to use the Schema Mapping API of Jooq that is registred on a Setting object.

String schema1Name = <...>
String schema2Name = <...>
Settings settings = new Settings().
    .withRenderMapping(new RenderMapping()
        .withSchemata(
            new MappedSchema().withInput(SCHEMA1.getName()).withOutput(schema1Name),
            new MappedSchema().withInput(SCHEMA2.getName()).withOutput(schema2Name)));

[1]: HSQLDB

[2]: Liquibase

[3]: Jooq

[4]: github.com/stanislas/jooq-with-liquibase