How to use HINT_PASS_DISTINCT_THROUGH with Criteria API?

Hibernate ORM
1

When performing a join fetch to load lazy associations with a Hibernate 5 Criteria API query, the returned result list may contain duplicate entities. Hibernate supports hints such as HINT_PASS_DISTINCT_THROUGH in order to avoid the use of DISTINCT in the resulting SQL query and filter out duplicate entities in the result list in memory.

What is the best practice way of using that hint with JPA Criteria API?

Note the following:

  • CriteriaQuery#distinct(boolean) is supported however, it adds the DISTINCT keyword to the SQL query.
  • I am aware that Hibernate 6 makes the use of distinct on join fetch obsolete.
2

What’s the problem? Just set the hint on the javax.persistence.Query or javax.persistence.TypedQuery and it should work. If it doesn’t, please create an issue in the issue tracker(https://hibernate.atlassian.net) with a test case(hibernate-test-case-templates/JPAUnitTestCase.java at main · hibernate/hibernate-test-case-templates · GitHub) that reproduces the issue.

3

Thank you for the answer. I was just trying to figure out the common way of applying that hint with Criteria API.

Just to clarify, in my particular use case I am using Panache and Mutiny (reactive implementations) and as far as I can see they have a parallel implementation of the Query structure. Mutiny.Query does not provide any such operation, and PanacheQuery seems to throw an exception for hints right now.

4

There is no way to apply the hint to the criteria query, you have to apply it to the built Query/TypedQuery. I don’t know Panache well enough, but I guess this is just a bug or missing feature on that end.