Hibernate Tip: How to integrate an external query builder

By Thorben Janssen

Criteria API, Query

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:

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:

 

Hibernate Tips Book







Get more recipes like this one in my new book Hibernate Tips: More than 70 solutions to common Hibernate problems.

It gives you more than 70 ready-to-use recipes for topics like basic and advanced mappings, logging, Java 8 support, caching, and statically and dynamically defined queries.

Get it now!



Tags

Criteria API, Query


About the author

Thorben is an independent consultant, international speaker, and trainer specialized in solving Java persistence problems with JPA and Hibernate.
He is also the author of Amazon’s bestselling book Hibernate Tips - More than 70 solutions to common Hibernate problems.

Books and Courses

Coaching and Consulting

Leave a Reply

Your email address will not be published. Required fields are marked

This site uses Akismet to reduce spam. Learn how your comment data is processed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}