Hibernate & jOOQ – A Match Made in Heaven

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

JPA and Hibernate make the implementation of simple CRUD operations extremely easy and efficient. But they lack support for complex queries. You can use JPQL and the Criteria API to define queries of mediocre complexity. If you need more, you need to write a native SQL query as a plain, un-typed String and handle the finer details of different SQL dialects yourself. jOOQ, on the other hand, provides a powerful DSL to create SQL queries in a comfortable and type-safe way. So, why not use the 2 of them together to get the best of both options?

As I will show you in this post, Hibernate, and jOOQ work very well together. With a little bit of preparation, you can use Hibernate’s entities to implement your write operations and to generate jOOQ’s metamodel classes. You can then use jOOQ’s DSL to build fully-featured SQL queries in a type-safe way and execute them as native queries within the context of your current persistence context.

For the remainder of this post, I expect that you are familiar with JPA and Hibernate, know how to define your domain model and how to implement standard CRUD operations. If that’s not the case, I recommend you take a look at my Getting Started with Hibernate guide before you continue reading this post.

Project Structure

jOOQ provides a set of code generators which you can use to generate your jOOQ metamodel automatically. The JPADatabase generator is one of them. It generates jOOQ’s classes based on your annotated entities.

To make this work, your project needs to follow a certain structure. You need to reference a jar-file with your entities as a dependency of the code generator. That’s why I prefer a maven project with the following 3 modules:

1. Entities
2. jOOQ
3. Business or Persistence

The Entities module is independent of all other modules. The code generator used in the jOOQ module depends on the entities defined in the Entities module. And the Business module depends on the two other modules. It uses entities and jOOQ’s DSL to implement and execute your persistence code.

Let’s take a look at an example.

Entities

The Entities module contains all entities used in the application. For this example, I prepared 2 simple entities.

The Author entity models a simple author with an id, a first name, a last name and a list of books as a many-to-many association.

@Entity
public class Author {

	@Id
	@GeneratedValue(strategy = GenerationType.SEQUENCE)
	private Long id;

	@Version
	private int version;

	private String firstName;

	private String lastName;

	...
}

The Book entity models a book with an id, a title, a publishing date and a list of authors as a many-to-many association.

@Entity
public class Book {

	@Id
	@GeneratedValue(strategy = GenerationType.SEQUENCE)
	private Long id;

	@Version
	private int version;

	private String title;

	private LocalDate publishingDate;

	@ManyToMany
	private List<Author> authors = new ArrayList<Author>();

	...
}

jOOQ

You don’t need to write any code in the jOOQ module. The only thing you need is a build configuration that triggers jOOQ’s JPADatabase code generator. It uses Spring to find all annotated entities in your classpath, creates database tables for these entities in a local h2 database and then generates the jOOQ metamodel classes for these tables.

Here you can see a snippet of a maven build configuration that triggers the JPADatabase code generator for all entity classes in the org.thoughts.on.java package and generates classes to the org.thoughts.on.java.jooq package in the src/main/java folder.

<?xml version="1.0" encoding="UTF-8"?>
<project>
    
	...
	
	<build>
        <plugins>
            <plugin>
                <groupId>org.jooq</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <version>${version.jooq}</version>

                <executions>
                    <execution>
                        <id>generate-h2-jpa</id>
                        <phase>generate-sources</phase>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                        <configuration>
                            <generator>
                                <database>
                                    <name>org.jooq.util.jpa.JPADatabase</name>
                                    <properties>
                                        <property>
                                            <key>packages</key>
                                            <value>
                                                org.thoughts.on.java
                                            </value>
                                        </property>
                                    </properties>
                                    <includes>.*</includes>
                                </database>
                                <generate>
                                </generate>
                                <target>
                                    <packageName>org.thoughts.on.java.jooq</packageName>
                                    <directory>src/main/java</directory>
                                </target>
                            </generator>
                        </configuration>
                    </execution>
                </executions>
                <dependencies>

                    <!--  This is needed for the JPADatabase -->
                    <dependency>
                        <groupId>org.jooq</groupId>
                        <artifactId>jooq-meta-extensions</artifactId>
                        <version>${version.jooq}</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>
</project>

As I explained in more details in my Getting Started with jOOQ article, the code generator creates a set of classes that provide type-safe access to all database tables and columns. I will show you in the Business module how you can use them with jOOQ’s DSL to create fully-featured SQL queries in a type-safe way.

Business

After you’ve defined your entities and generated your jOOQ classes, you can finally use them to implement your persistence code. The easiest way to do that is to use your entities and EntityManager in the same way as you use it if you don’t integrate jOOQ.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

Author a = new Author();
a.setFirstName("Thorben");
a.setLastName("Janssen");
em.persist(a);

Book b = new Book();
b.setTitle("Hibernate Tips - More than 70 solutions to common Hibernate problems");
b.getAuthors().add(a);
a.getBooks().add(b);
em.persist(b);

em.getTransaction().commit();
em.close();

The creation and execution of a query with jOOQ require a few additional steps compared to the relatively simple approach described in my previous post. In the first step, you need to instantiate a DSLContext for your database dialect. In this example, I use a PostgreSQL 10 database which uses the 9.5 dialect.

DSLContext ctx = DSL.using(SQLDialect.POSTGRES_9_5);

The next step might be optional for your database. But in my case, I had to set the RenderNameStyle to AS_IS to avoid any issues with capitalized table and column names.

ctx.configuration().settings().setRenderNameStyle(RenderNameStyle.AS_IS);

And after that, you can build your query. Here I’m using a relatively simple example which selects the first name, last name and book title of all authors whose first name starts with “Thor”.

SelectConditionStep<Record3<String, String, String>> jooqQuery = 
		ctx.select(AUTHOR.FIRSTNAME, AUTHOR.LASTNAME, BOOK.TITLE)
			.from(AUTHOR)
				.leftJoin(BOOK_AUTHOR).on(AUTHOR.ID.eq(BOOK_AUTHOR.AUTHORS_ID))
				.leftJoin(BOOK).on(BOOK_AUTHOR.BOOKS_ID.eq(BOOK.ID))
			.where(AUTHOR.FIRSTNAME.like("Thor%"));
Query q = em.createNativeQuery(jooqQuery.getSQL());
setBindParameterValues(q, jooqQuery);

The only interesting part of this code block is the call of the setBindParameterValues method. As I explained in the previous post of this series, jOOQ automatically creates bind parameters and sets their values. But if you use Hibernate to execute the SQL statement as a native query, you need to set the bind parameter values explicitly. That’s what my setBindParameterValues method does. It extracts all bind parameters and their values from the jooqQuery and sets them on my hibernateQuery.

private static void setBindParameterValues(Query hibernateQuery, org.jooq.Query jooqQuery) {
	List<Object> values = jooqQuery.getBindValues();
	for (int i = 0; i < values.size(); i++) {
		hibernateQuery.setParameter(i + 1, values.get(i));
	}
}

That’s all you need to do to execute the SQL statement as a native query within the current persistence context. Here you can see all of the previous steps in one code sample.

DSLContext ctx = DSL.using(SQLDialect.POSTGRES_9_5);
ctx.configuration().settings().setRenderNameStyle(RenderNameStyle.AS_IS);
SelectConditionStep<Record3<String, String, String>> jooqQuery = 
		ctx.select(AUTHOR.FIRSTNAME, AUTHOR.LASTNAME, BOOK.TITLE)
			.from(AUTHOR)
				.leftJoin(BOOK_AUTHOR).on(AUTHOR.ID.eq(BOOK_AUTHOR.AUTHORS_ID))
				.leftJoin(BOOK).on(BOOK_AUTHOR.BOOKS_ID.eq(BOOK.ID))
			.where(AUTHOR.FIRSTNAME.like("Thor%"));
Query q = em.createNativeQuery(jooqQuery.getSQL());
setBindParameterValues(q, jooqQuery);

The good thing about executing the SQL statement as a native query is, that you can use all result set mapping features provided by JPA and Hibernate. So, you can map your query result to managed entities based on your mapping annotations or you can use an @SqlResultSetMapping to map it to entities, POJOs or scalar values.

For this example, I use the following @SqlResultSetMapping to map each record of the query result to a BookAuthor object with the attributes firstName, lastName and title.

@SqlResultSetMapping(name = "BookAuthor", 
		classes = @ConstructorResult(
					targetClass = BookAuthor.class,
					columns = {
						@ColumnResult(name = "firstName"),
						@ColumnResult(name = "lastName"),
						@ColumnResult(name = "title")
					}))

In the next step, you need to provide the name of the @SqlResultSetMapping as the second parameter to the createNativeQuery method.

Query q = em.createNativeQuery(jooqQuery.getSQL(), "BookAuthor");
setBindParameterValues(q, jooqQuery);
List<BookAuthor> bookAuthors = q.getResultList();

Summary

As you’ve seen, you can easily use Hibernate and jOOQ within the same application. This enables you to use the best parts of both worlds:

• JPA and Hibernate provide a very efficient and comfortable way to implement write operations.
• jOOQ’s DSL enables you to build fully-featured SQL queries in a type-safe way.

If you structure your application so that you can define a classpath dependency to your entity classes, jOOQ’s code generator can create metamodel classes based on your entity mappings. That makes sure that your entity model and your jOOQ model stays in sync and enables you to use the DSL in a type-safe way. After you’ve defined your query with jOOQ, you can execute it as a native query with Hibernate and use Hibernate’s mapping functionality to map the records of the result set.