JOOQ is a neat framework, and it addresses a long 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 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 hibernatetool 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-resources</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 prefered 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>process-test-resources</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>process-test-classes</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>target/generated-sources/jooq</directory>
</target>
</generator>
</configuration>
</plugin>Running through maven we get the table mapping generated, so let’s compare a JPA meta model for the Image class to the associated JOOQ table mapping:
The JPA meta model 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.
