JOOQ Facts: From JPA Annotations to JOOQ Table Mappings

(Last Updated On: January 4, 2018)

JOOQ is a neat framework, and it addresses a long-time issue I’ve had with advanced dynamic filtered queries. While Hibernate and JPA come with a useful Criteria API, which I’ve been using for quite some time, there are understandable limits to what you can do with those. For instance, you cannot go beyond simple SQL operations (e.g JOINS, NESTED SLECTS, AGGREGATION) and do something like: window functions, user-defined functions or easy sequencing to name a few.

JOOQ doesn’t feel like competing with Hibernate, but instead, I feel like it completes it. I’ve been using Hibernate mostly for the WRITE part of my Data Layer, hence its name or the “Persisting” part in JPA. For simple to medium complex queries, Hibernate does its best, but I don’t have to solely rely on it for all my queries, am I? There is also a drawback to querying properties and that’s because you sometimes have to add an association to your Domain Model just for the sake of querying it for a small number of use cases.

So, since I’m not afraid of writing native queries, I could, therefore, do it in a DSL fashion and vendor-independent way.

While you can go with string-based column naming, JOOQ offers a better approach through the use of a type-safe Metadata, so the first thing we need to do is to generate our table mapping for our database schema.

Since I already have a JPA Model, I can generate a database schema DDL from it, and for that, we can use the hibernate tool ant task.

<plugin>
	<groupId>org.apache.maven.plugins</groupId>
	<artifactId>maven-antrun-plugin</artifactId>
	<executions>
		<execution>
			<id>generate-test-sql-scripts</id>
			<phase>generate-test-sources</phase>
			<goals>
				<goal>run</goal>
			</goals>
			<configuration>
				<tasks>
					<property name="maven_test_classpath" refid="maven.test.classpath"/>
					<path id="hibernate_tools_path">
						<pathelement path="${maven_test_classpath}"/>
					</path>
					<property name="hibernate_tools_classpath" refid="hibernate_tools_path"/>
					<taskdef name="hibernatetool"
							 classname="org.hibernate.tool.ant.HibernateToolTask"/>
					<mkdir dir="${project.build.directory}/test-classes/hsqldb"/>
					<hibernatetool destdir="${project.build.directory}/test-classes/hsqldb">
						<classpath refid="hibernate_tools_path"/>
						<jpaconfiguration persistenceunit="testPersistenceUnit"
										  propertyfile="src/test/resources/META-INF/spring/jdbc.properties"/>
						<hbm2ddl drop="false" create="true" export="false"
								 outputfilename="create_db.sql"
								 delimiter=";" format="true"/>
						<hbm2ddl drop="true" create="false" export="false"
								 outputfilename="drop_db.sql"
								 delimiter=";" format="true"/>
					</hibernatetool>
				</tasks>
			</configuration>
		</execution>
	</executions>
	<dependencies>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-entitymanager</artifactId>
			<version>${hibernate.version}</version>
			<exclusions>
				<exclusion>
					<groupId>org.slf4j</groupId>
					<artifactId>slf4j-api</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-tools</artifactId>
			<version>${hibernate.tools.version}</version>
			<exclusions>
				<exclusion>
					<groupId>org.hibernate</groupId>
					<artifactId>hibernate-commons-annotations</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>${slf4j.version}</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-simple</artifactId>
			<version>${slf4j.version}</version>
		</dependency>
	</dependencies>
</plugin>

This will generate a “create_db.sql” database DDL script, which we’ll use to populate a temporary file-based HSQLDB, using “maven.sql.plugin”. I would have preferred the in-memory HSQLDB but unfortunately, it didn’t save the state between plugin executions.

<plugin>
    <groupId>org.codehaus.mojo</groupId>
    <artifactId>sql-maven-plugin</artifactId>
    <dependencies>
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>${hsqldb.version}</version>
        </dependency>
    </dependencies>
    <configuration>
        <driver>org.hsqldb.jdbc.JDBCDriver</driver>
        <url>jdbc:hsqldb:file:${project.build.directory}/hsqldb/db;shutdown=true</url>
        <username>sa</username>
        <password></password>
        <autocommit>true</autocommit>
        <settingsKey>hsql-db-test</settingsKey>
    </configuration>
    <executions>
        <execution>
            <id>create-test-compile-data</id>
            <phase>generate-test-sources</phase>
            <inherited>true</inherited>
            <goals>
                <goal>execute</goal>
            </goals>
            <configuration>
                <orderFile>ascending</orderFile>
                <fileset>
                    <basedir>${project.build.directory}/test-classes/hsqldb/</basedir>
                    <includes>
                        <include>create_db.sql</include>
                    </includes>
                </fileset>
                <autocommit>true</autocommit>
            </configuration>
        </execution>
    </executions>
</plugin>

So the HSQLDB is now populated with our JPA generated schema, and we can finally call the JOOQ code generation to build the table mapping.

<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <executions>
        <execution>
            <phase>generate-test-sources</phase>
            <goals>
                <goal>generate</goal>
            </goals>
        </execution>
    </executions>
    <dependencies>
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>${hsqldb.version}</version>
        </dependency>
    </dependencies>
    <configuration>
        <jdbc>
            <driver>org.hsqldb.jdbc.JDBCDriver</driver>
            <url>jdbc:hsqldb:file:${project.build.directory}/hsqldb/db</url>
            <user>sa</user>
            <password></password>
        </jdbc>
        <generator>
            <name>org.jooq.util.JavaGenerator</name>
            <database>
                <name>org.jooq.util.hsqldb.HSQLDBDatabase</name>
                <includes>.*</includes>
                <excludes></excludes>
                <inputSchema>PUBLIC</inputSchema>
            </database>
            <generate></generate>
            <target>
                <packageName>vladmihalcea.jooq.schema</packageName>
                <directory>${project.build.directory}/generated-sources/java</directory>
            </target>
        </generator>
    </configuration>
</plugin>

Running through maven we get the table mapping generated, so let’s compare a JPA metamodel for the Image class to the associated JOOQ table mapping:

The JPA metamodel looks like:

@StaticMetamodel(Image.class)
public abstract class Image_ {

    public static volatile SingularAttribute<Image, Product> product;
    public static volatile SingularAttribute<Image, Long> id;
    public static volatile SetAttribute<Image, Version> versions;
    public static volatile SingularAttribute<Image, Integer> index;
    public static volatile SingularAttribute<Image, String> name;

}

and the JOOQ table mapping

@javax.annotation.Generated(value    = { "http://www.jooq.org", "3.2.0" },
                            comments = "This class is generated by jOOQ")
@java.lang.SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class Image extends org.jooq.impl.TableImpl<vladmihalcea.jooq.schema.tables.records.ImageRecord> {

    private static final long serialVersionUID = 1596930978;

    /**
     * The singleton instance of <code>PUBLIC.IMAGE</code>
     */
    public static final vladmihalcea.jooq.schema.tables.Image IMAGE = new vladmihalcea.jooq.schema.tables.Image();

    /**
     * The class holding records for this type
     */
    @Override
    public java.lang.Class<vladmihalcea.jooq.schema.tables.records.ImageRecord> getRecordType() {
        return vladmihalcea.jooq.schema.tables.records.ImageRecord.class;
    }

    /**
     * The column <code>PUBLIC.IMAGE.ID</code>. 
     */
    public final org.jooq.TableField<vladmihalcea.jooq.schema.tables.records.ImageRecord, java.lang.Long> ID = createField("ID", org.jooq.impl.SQLDataType.BIGINT.nullable(false), this);

    /**
     * The column <code>PUBLIC.IMAGE.INDEX</code>. 
     */
    public final org.jooq.TableField<vladmihalcea.jooq.schema.tables.records.ImageRecord, java.lang.Integer> INDEX = createField("INDEX", org.jooq.impl.SQLDataType.INTEGER, this);

    /**
     * The column <code>PUBLIC.IMAGE.NAME</code>. 
     */
    public final org.jooq.TableField<vladmihalcea.jooq.schema.tables.records.ImageRecord, java.lang.String> NAME = createField("NAME", org.jooq.impl.SQLDataType.VARCHAR.length(255), this);

    /**
     * The column <code>PUBLIC.IMAGE.PRODUCT_ID</code>. 
     */
    public final org.jooq.TableField<vladmihalcea.jooq.schema.tables.records.ImageRecord, java.lang.Long> PRODUCT_ID = createField("PRODUCT_ID", org.jooq.impl.SQLDataType.BIGINT, this);

    /**
     * Create a <code>PUBLIC.IMAGE</code> table reference
     */
    public Image() {
        super("IMAGE", vladmihalcea.jooq.schema.Public.PUBLIC);
    }

    /**
     * Create an aliased <code>PUBLIC.IMAGE</code> table reference
     */
    public Image(java.lang.String alias) {
        super(alias, vladmihalcea.jooq.schema.Public.PUBLIC, vladmihalcea.jooq.schema.tables.Image.IMAGE);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public org.jooq.Identity<vladmihalcea.jooq.schema.tables.records.ImageRecord, java.lang.Long> getIdentity() {
        return vladmihalcea.jooq.schema.Keys.IDENTITY_IMAGE;
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public org.jooq.UniqueKey<vladmihalcea.jooq.schema.tables.records.ImageRecord> getPrimaryKey() {
        return vladmihalcea.jooq.schema.Keys.SYS_PK_10059;
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public java.util.List<org.jooq.UniqueKey<vladmihalcea.jooq.schema.tables.records.ImageRecord>> getKeys() {
        return java.util.Arrays.<org.jooq.UniqueKey<vladmihalcea.jooq.schema.tables.records.ImageRecord>>asList(vladmihalcea.jooq.schema.Keys.SYS_PK_10059, vladmihalcea.jooq.schema.Keys.UK_OQBG3YIU5I1E17SL0FEAWT8PE);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public java.util.List<org.jooq.ForeignKey<vladmihalcea.jooq.schema.tables.records.ImageRecord, ?>> getReferences() {
        return java.util.Arrays.<org.jooq.ForeignKey<vladmihalcea.jooq.schema.tables.records.ImageRecord, ?>>asList(vladmihalcea.jooq.schema.Keys.FK_9W522RC4D0KFDKQ390IHV92GB);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public vladmihalcea.jooq.schema.tables.Image as(java.lang.String alias) {
        return new vladmihalcea.jooq.schema.tables.Image(alias);
    }
}

Now we also need to make Maven aware of our newly generated JOOQ Metadata classes, so it can compile them in a next test-compile phase.

<plugin>
    <groupId>org.codehaus.mojo</groupId>
    <artifactId>build-helper-maven-plugin</artifactId>
    <executions>
        <execution>
            <id>add-source</id>
            <phase>process-test-sources</phase>
            <goals>
                <goal>add-test-source</goal>
            </goals>
            <configuration>
                <sources>
                    <source>${project.build.directory}/generated-sources/java</source>
                </sources>
            </configuration>
        </execution>
    </executions>
</plugin>

Now I can start playing with JOOQ. Let’s add the DSLContext to our Spring application context:

<bean id="jooqContext" class="org.jooq.impl.DSL" factory-method="using">
    <constructor-arg ref="dataSource"/>
    <constructor-arg value="#{T(org.jooq.SQLDialect).HSQLDB}"/>
</bean

And we will write a test to check if everything works properly:

private List<ImageProductDTO> getImageProductDTOs_JOOQ() {
    return transactionTemplate.execute(new TransactionCallback<List<ImageProductDTO>>() {
        @Override
        public List<ImageProductDTO> doInTransaction(TransactionStatus transactionStatus) {
            return jooqContext
                    .select(IMAGE.NAME, PRODUCT.NAME)
                    .from(IMAGE)
                    .join(PRODUCT).on(IMAGE.PRODUCT_ID.equal(PRODUCT.ID))
                    .where(PRODUCT.NAME.likeIgnoreCase("%tv%"))
                        .and(IMAGE.INDEX.greaterThan(0))
                    .orderBy(IMAGE.NAME.asc())
                    .fetch().into(ImageProductDTO.class);
        }
    });
}

Which generates the following SQL

SELECT "PUBLIC"."image"."name",
       "PUBLIC"."product"."name"
FROM   "PUBLIC"."image"
       JOIN "PUBLIC"."product"
         ON "PUBLIC"."image"."product_id" = "PUBLIC"."product"."id"
WHERE  ( Lower("PUBLIC"."product"."name") LIKE Lower('%tv%')
         AND "PUBLIC"."image"."index" > 0 )
ORDER  BY "PUBLIC"."image"."name" ASC  

This is the first time I used JOOQ, and it didn’t take me too much time to browse the documentation and to have everything set up in my Hibernate Facts coding example. The JOOQ query building feels natural, it’s like writing native SQL code, so I don’t have to actually learn the API to know how to use it. I will proudly add it to my Java Data Toolbox.

This coding example generates the JOOQ mappings into the test-classes folder and so you can’t use them from within the main/java source files. This can be solved, but it requires refactoring the existing solution by moving the model classes into a separate Maven module. You could generate the JOOQ schema in this separate module, where before packaging you would move the schema classes from test-classes to the classes folder. Then you would have to include this new module, where you would normally make use of the JOOQ schema.

Code available on GitHub.

Subscribe to our Newsletter

* indicates required
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence, 
  • a 10% discount coupon for my book. 
Get the most out of your persistence layer!

Advertisements

2 thoughts on “JOOQ Facts: From JPA Annotations to JOOQ Table Mappings

  1. Quite some extra boiling plate code just for having reads with jOOQL. I’ve personally chose to use QueryDSL instead of JPA Criteria.

Leave a Reply

Your email address will not be published. Required fields are marked *