Hibernate Tip: How to integrate an external query builder

---

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!

---

Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question for a future Hibernate Tip, please post a comment below.

Question:

“I don’t like JPQL. It abstracts from the underlying table model, and it’s too limited for my use case.
Is there any way to integrate an external query builder, like jOOQ, with Hibernate?
Does it remove the limitations from JPQL?”

Solution:

All query builders are a little bit different. The same is true for their integration with Hibernate. Some, like the QueryDSL, provide their own integration, others, like jOOQ, rely on JPA’s support of native queries.

Because this reader explicitly asked about jOOQ, and I’m not sure if QueryDSL gets still supported, I will focus on integrations via JPA’s native query capabilities.

Executing native queries

JPA and Hibernate allow you to execute any native SQL query as a native query. This enables you to use all features supported by your database and removes the limitations of JPQL.

A native query gets executed within the current Hibernate context. That means that Hibernate will flush the persistence context before executing the query. It also runs the query using the database connection and transaction that’s associated with the current Hibernate Session.

Creating a query using jOOQ

I explained jOOQ and its integration with Hibernate in great detail in:

• Getting Started with jOOQ – Building SQL Queries in Java and
• Hibernate & jOOQ – A Match Made in Heaven.

So, in this article, I keep the general explanations short.

Creating a query using jOOQ is pretty simple. You first need to instantiate a Context and you can then use it to implement your query. The great thing about it is that your code almost reads like SQL.

DSLContext ctx = DSL.using(SQLDialect.POSTGRES);
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%"));

Integrating jOOQ and Hibernate

After you’ve done that, you can create a native query using your EntityManager, set the bind parameter values, and execute the query. In this example, I use a SqlResultMapping with the name BookAuthor. It tells Hibernate to instantiate a new BookAuthorValue DTO object for each record in the result set.

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

The only part that requires your attention is the handling of bind parameters. Hibernate and jOOQ handle them differently. Hibernate requires you to set all bind parameter values explicitly while jOOQ automatically creates and manages a bind parameter for each inlined parameter.

You can bridge this small gap by using the following method. It takes all bind parameters of the jooqQuery and sets it on the 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));
	}
}

And that’s all you need to do. Hibernate executes the query that you defined with jOOQ as a native query and handles the result accordingly. You can use Hibernate’s default mappings to map each record to a managed entity, instantiate DTO objects or retrieve an Object[].

Learn more:

If you want to learn more about queries and the integration of jOOQ and Hibernate, please read the following articles:

• Ultimate Guide to JPQL Queries with JPA and Hibernate
• Native Queries – How to call native SQL queries with JPA
• Getting Started with jOOQ – Building SQL Queries in Java
• Using the Optimal Query Approach and Projection for JPA and Hibernate
• Why, When and How to Use DTO Projections with JPA and Hibernate