About Vlad Mihalcea

Vlad Mihalcea is a software architect passionate about software integration, high scalability and concurrency challenges.

JOOQ Facts: From JPA Annotations to JOOQ Table Mappings

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.

Related Whitepaper:

Functional Programming in Java: Harnessing the Power of Java 8 Lambda Expressions

Get ready to program in a whole new way!

Functional Programming in Java will help you quickly get on top of the new, essential Java 8 language features and the functional style that will change and improve your code. This short, targeted book will help you make the paradigm shift from the old imperative way to a less error-prone, more elegant, and concise coding style that’s also a breeze to parallelize. You’ll explore the syntax and semantics of lambda expressions, method and constructor references, and functional interfaces. You’ll design and write applications better using the new standards in Java 8 and the JDK.

Get it Now!  

Leave a Reply


two + 2 =



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.

Sign up for our Newsletter

20,709 insiders are already enjoying weekly updates and complimentary whitepapers! Join them now to gain exclusive access to the latest news in the Java world, as well as insights about Android, Scala, Groovy and other related technologies.

As an extra bonus, by joining you will get our brand new e-books, published by Java Code Geeks and their JCG partners for your reading pleasure! Enter your info and stay on top of things,

  • Fresh trends
  • Cases and examples
  • Research and insights
  • Two complimentary e-books