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.
In a previous article, I showed how to join to unassociated entities using JPQL and Hibernate’s proprietary query features. Since I published that post, I get asked how to do the same in a CriteriaQuery.
So, let’s take a closer look at it in this Hibernate Tip.
JPQL and the Criteria API require a mapped association between two entities to define an inner, left, or right join. And as I showed in a previous article, Hibernate offers a proprietary extension to JPQL that enables you to create SQL-like join clauses.
Unfortunately, there isn’t something similar that you could use with the Criteria API. But you can define a cross join. It leads to the same result, but it might perform worse than an inner join. So, please double-check your query and its performance before you deploy it to production.
Defining a Cross Join
Creating a cross join in a CriteriaQuery is simple but a little counter-intuitive.
You need to call the from method on your CriteriaQuery for each entity you want to include in the cross join. In contrast to all other methods on the CriteriaQuery, calling the from method multiple times doesn’t override the previous calls. After you’ve done that, you need to include your join criteria in the WHERE clause.
Let’s take a look at an example.
I didn’t model an association between the Book and Review entities. But the bookId attribute of the Review entity maps a foreign key to the Book entity. So, we could easily join the underlying database tables in an SQL query.
To do the same in a CriteriaQuery, you first need to get an instance of the CriteriaBuilder and create the CriteriaQuery.
In the next step, you need to call the from method twice. Once for the Review and once for the Book entity.
After you’ve done that and defined the SELECT clause, you need to specify the WHERE clause. Because you can’t provide any join conditions to your cross join, you need to include it in the WHERE clause. In this case, I use the JPA metamodel to limit the cross join to the records in which the bookId value of the Review matches the id of the Book.
CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Review> cq = cb.createQuery(Review.class); Root<Review> review = cq.from(Review.class); Root<Book> book = cq.from(Book.class); cq.select(review); ParameterExpression<String> paramTitle = cb.parameter(String.class); cq.where(cb.and( cb.equal(review.get(Review_.bookId), book.get(Book_.id)), cb.equal(book.get(Book_.title), paramTitle))); TypedQuery<Review> query = em.createQuery(cq); query.setParameter(paramTitle, "Hibernate Tips - More than 70 solutions to common Hibernate problems"); List<Review> reviews = query.getResultList();
When you activate the logging of SQL statements and execute the query, you can see that Hibernate generates the expected SQL query.
14:59:34,084 DEBUG [org.hibernate.SQL] - select review0_.id as id1_1_, review0_.bookId as bookId2_1_, review0_.message as message3_1_, review0_.version as version4_1_ from Review review0_ cross join Book book1_ where review0_.bookId=book1_.id and book1_.title=?
Get this Hibernate Tip as a printable PDF!Join the free Thoughts on Java Library to get access to lots of member-only content, like a printable PDF for this post, lots of cheat sheets and 2 ebooks about Hibernate.
If you want to learn more about JPA’s Criteria API, you should also read the following articles:
- How to join unrelated entities with JPA and Hibernate
- Hibernate Tips: How to select multiple scalar values in a Criteria Query
- Hibernate Tips: How to select a POJO with a Criteria Query
- Hibernate Tip: How to Use a SubQuery in a CriteriaQuery
- Hibernate Tip: Using LEFT JOIN and FETCH JOIN in a CriteriaQuery
Hibernate Tips Book
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 as a paperback, ebook or PDF.