Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

JOOQ Facts: From JPA Annotations to JOOQ Table Mappings

DZone's Guide to

JOOQ Facts: From JPA Annotations to JOOQ Table Mappings

· Java Zone
Free Resource

What every Java engineer should know about microservices: Reactive Microservices Architecture.  Brought to you in partnership with Lightbend.

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 functionsuser-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 DSLfashion 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:

If you enjoyed this article, I bet you are going to love my book as well.






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); } } 

Code available on GitHub.

Microservices for Java, explained. Revitalize your legacy systems (and your career) with Reactive Microservices Architecture, a free O'Reilly book. Brought to you in partnership with Lightbend.

Topics:
java ,sql ,frameworks

Published at DZone with permission of Vlad Mihalcea. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}