In
this article, I will show you how to write EXISTS subqueries with JPA and Hibernate
.
EXISTS subqueries are very useful because they allow you to implement SemiJoins. Unfortunately, many app developers don’t know about SemiJoins, and end up emulating it using EquiJoin (e.g. INNER JOIN) at the expense of query performance.
Domain Model
Suppose we are using the following
Post and PostComment entities:
<img src
=”https://vladmihalcea.com/wp-content/uploads/2022/01/PostCommentScore.png” alt=”Post and PostComment entities” /> The Post entity is the parent and PostComment
is the child because PostComment references the parent through its post property.
Get parent entities while filtering by entities
Suppose we want to get all Post entities that have a PostComent with a score greater than 10. Most developers would mistakenly use the following query
: List<Post> posts = entityManager.createQuery(“”” select distinct p from PostComment pc join pc.post p where pc.score > :minScore order by p.id “””, Post.class) .setParameter(“minScore”, 10) .setHint(QueryHints.HINT_PASS_DISTINCT_THROUGH, false) .getResultList();
This query executes a union between the publication and the post_comment just by filtering the publishing records. Because the projection contains only the Post entity, JOIN is not required in this case. Instead, a SemiJoin should be used to filter Post entity records.
The HINT_PASS_DISTINCT_THROUGH is used to prevent the DISTINCT keyword from being passed to the underlying SQL query, because deduplication is performed for Java object references, not SQL table records. See this article for more details on this topic.
EXISTS
subqueries with JPQL
As I explained in this article, an EXISTS subquery is a much better alternative. Therefore, we can achieve our goal using the following JPQL query
: List<Post> posts = entityManager.createQuery(“”” select p from Post p where exists ( select 1 from PostComment pc where pc.post = p and pc.score > :minScore ) order by p.id “””, Post.class) .setParameter(“minScore”, 10) .getResultList();
When you run the JPQL query above, Hibernate generates the following SQL query:
SELECT p.id AS id1_0_, P.TITLE AS title2_0_ FROM POST P WHERE IT EXISTS ( SELECT 1 FROM post_comment COMPUTER WHERE pc.post_id=P.ID AND PC.SCORE > ? ) SORT BY p.id
The advantage of this query is that SemiJoin does not need to join all post and post_comment records, because as soon as a post_comment matching the filtering criteria is found (for example, pc.score > ?), the EXISTS clause returns true and the query continues to the next publish record. EXISTS
subqueries with criteria API
If you want to generate the entity query dynamically, then you can use a criteria API since, like JPQL, it supports subquery filtering.
The JPQL query above can be rewritten into a criteria API query, like this:
CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Post> query = builder.createQuery(Post.class); Root<Post> p = query.from(Post.class); ParameterExpression<Integer> minScore = builder.parameter(Integer.class); Subquery<Integer> subQuery = query.subquery(Integer.class); root<PostComment> pc = subQuery.from(PostComment.class); subQuery .select(builder.literal(1)) .where( builder.equal(pc.get(PostComment_. POST), p), builder.gt(pc.get(PostComment_. SCORE), minScore) ); query.where(builder.exists(subQuery)); List<Post> posts = entityManager.createQuery(query) .setParameter(minScore, 10) .getResultList();
The above criteria API query generates the same SQL query that generated the previous JPQL query.
Subqueries EXIST with Blaze Persistence
If you’re not a big fan of the Criteria API, then there’s a much better alternative for creating dynamic entity queries. Blaze Persistence allows you to write dynamic queries that are not only more readable, but also more powerful, as you can use LATERAL UNION, Derived Tables, Common Table Expressions, or Window Functions.
The above criteria API query
can be rewritten with the criteria API, like this:
Final string POST_ALIAS = “p”; final string POST_COMMENT_ALIAS = “pc”; List<Post> posts = cbf.create(entityManager, Post.class) .from(Post.class, POST_ALIAS) .whereExists() .from(PostComment.class, POST_COMMENT_ALIAS) .select(“1”) .where(PostComment_. POST).eqExpression(POST_ALIAS) .where(PostComment_. SCORE).gtExpression(“:minScore”) .end() .select(POST_ALIAS) .setParameter(“minScore”, 10) .getResultList();
When you run the Blaze Persistence query above, Hibernate will generate the same SQL statement that generated the aforementioned JPQL query or Criteria API.
Impressive, right?
If you liked this article, I bet you’ll love my book and video courses too.
<img src="https://vladmihalcea.com/wp-content/uploads/2022/01/PostCommentScore-300×106.png" alt="" /
>
And there’s
more! You
can get a significant passive income stream to promote all these amazing products that I’ve been creating.
If you’re interested in supplementing your income, then join my affiliate program.
Conclusion
Half-joins are very useful for filtering, and you should prefer them to EquiJoin when the query projection does not contain any of the joined columns
. In SQL,
semijoins are expressed using EXISTS subqueries, and this feature is not limited to native SQL queries, because you can use EXISTS in your JPA entity queries and Hibernate with JPQL and Criteria API. and Blaze persistence queries.
