Hibernate left join fetch on many returns duplicities

Question

I have a problem, I am using Hibernate via Spring data and I have this data model (model is much bigger but this is the part that causes problems).

Lets assume we have Entity A and Entity B. Relationship between two entities are Many-to-Many.

I am trying to fetch A records with B recordes fetched (to prevent lazy loading).

The model is connected like this

On entity A:

@ManyToMany(mappedBy = "items") private List<BEntity> bs = new ArrayList<>();

On entity B

@ManyToMany @JoinTable( name = "a_b", joinColumns = { @JoinColumn(name = "b_id", referencedColumnName = "id") }, inverseJoinColumns = { @JoinColumn(name = "a_id", referencedColumnName = "id") } ) private Collection<AEntity> as = new ArrayList<>();

So its typical M:N relationship. And I am trying to retrieve A data like following

@Query("SELECT a FROM AEntity a " + " LEFT JOIN FETCH a.bs" + "WHERE a.id IN (:aIds)") List<AEntity> findX(@NonNull @Param("aIds") Collection<Long> aIds);

The resulted SQL is something like

select X -- select fields omitted for simplicity from item itembo0_ left outer join a_b ab on a.id = ab.a_id left outer join b b on ab.b_id = b.id where a.id in (...)

Which is a thing I would expect. The SQL will result duplicites (which I would expect as well cause there might be many B records which each has one result row). But at the end, hibernate does not merge all these rows into a A entity with fetched B fields.

For example, when I pass a 5 IDS into "in" condition, I get a 10 A records. Each one has a 2 B records linked! Thats the weird part.

Is there anyone who can tell me why hibernate does not merge these SQL results by A.id identifier and makes duplicites? Is it because I am asking for a List instead of Set?

Did you implement a proper equals and hashCode? See vladmihalcea.com/…. Also you might want to do a SELECT DISTINCT a ... in your query. — M. Deinum
– M. Deinum, Commented Aug 17, 2021 at 14:12

Kirill Misnik · Accepted Answer · 2021-08-17 14:45:49Z

4

Using the "DISTINCT" keyword in your query should be enough to avoid duplicates:

@Query("SELECT DISTINCT a FROM AEntity a " + " LEFT JOIN FETCH a.bs" + "WHERE a.id IN (:aIds)") List<AEntity> findX(@NonNull @Param("aIds") Collection<Long> aIds);

I do not know what kind of data you store, but perhaps a better solution, as you have mentioned, would be to use a Set instead of a List.

answered Aug 17, 2021 at 14:45

Kirill Misnik

982 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Johnczek Over a year ago

Hi, DISTINCT works, but I would like to understand why Hibernate can not process these children records as in case of 1:M relation, the result of SQL with 1:M relation has duplicates as well but Hibernate correctly merge all rows with primary key of FROM entity together and creates Entity object.

Kirill Misnik Over a year ago

Unfortunately, this problem is on the Hibernate side. The official documentation says: "Queries that make use of eager fetching of collections usually return duplicates of the root objects, but with their collections initialized. You can filter these duplicates through a Set." link

Johnczek Over a year ago

Well the default ManyToMany Fetch is LAZY. Returning SET (or using distinct) works fine buts its still kind of a workaround. I would like to Hibernate to be able to JOIN FETCH many childrens without duplicates (as it does in case of 1:M)

mjs Over a year ago

This is wierd. Using a subselect will return the correct values but the subselect is not going to be as fast.

Collectives™ on Stack Overflow

Hibernate left join fetch on many returns duplicities

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related