4

I have the following mapping
1 User can have 0 or more roles.

Query
from User u JOIN Fetch u.roles

If User1 has two roles RoleA and RoleB.
Then User1 is returned twice.

What i expect is User1 should be returned Once with list of roles containing RoleA and RoleB
How can I fix this.

Also please explain behavior for Many To Many relationship.

Piyush
  • 599
  • 1
  • 7
  • 16

2 Answers2

5

See the JPA spec 4.4.5.3

SELECT d FROM Department d LEFT JOIN FETCH d.employees WHERE d.deptno = 1

A fetch join has the same join semantics as the corresponding inner or outer join, except that the related objects specified on the right-hand side of the join operation are not returned in the query result or otherwise referenced in the query. Hence, for example, if department 1 has five employees, the above query returns five references to the department 1 entity.

Options

  1. You can add a DISTINCT to the SELECT clause to filter out the duplicate rows.
  2. Define an EntityGraph for the query and add the roles field to it, and it will be fetched, meaning you omit the "FETCH JOIN" from the query.
  3. Mark the roles field as EAGER, but this would then apply to all fetching of that field, so likely not desirable.
Neil Stockton
  • 11,383
  • 3
  • 34
  • 29
  • Thanks for Entity Graph tip. Please answer http://stackoverflow.com/questions/39507887/behavior-of-distinct-root-entity – Piyush Sep 15 '16 at 09:44
  • A coworker showed me that you might want to add .setHint("hibernate.query.passDistinctThrough", false) to the named query with 'DISTINCT' for performance reasons, as described here (https://vladmihalcea.com/jpql-distinct-jpa-hibernate/) – Benedikt Gansinger Jul 30 '19 at 11:50
2

user below code :

criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);

For more details check this link: Hibernate Criteria returns children multiple times with FetchType.EAGER

Community
  • 1
  • 1
Gokul
  • 931
  • 7
  • 16
  • What happens if I have User -> Role -> Permission all many to many. Will criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY); return the result as expected ? – Piyush Sep 15 '16 at 09:25