Duplicated results in Hibernate OneToMany List

Question

I have mapped an 1:N relation with a @OneToMany List, but when I access the list, the results are duplicated due to an OUTER JOIN. This is how the mapping looks like:

@Entity
public class Programmer

    @ElementCollection(fetch=FetchType.EAGER)
    @CollectionTable(name="emails", joinColumns=@JoinColumn(name="id", nullable=false))
    @Column(name="email", nullable=false)
    protected Set<String> emails = new HashSet<String>();

    @OneToMany(mappedBy="programmer", fetch=FetchType.EAGER)
    private List <Game> games = new ArrayList<Game>();

When I get the attribute with prog.getGames(), the results comes duplicated because the Hibernate SQL makes an OUTER JOIN:

from programmer 
left outer join emails on programmer.id=emails.id 
left outer join game on programmer.id=game.id
where programmer.id=?

Is there any solution without transforming the List into a Set? I need to get the games with prog.getGames(), can not use a custom HQL or Criteria.

Possible duplicate... http://stackoverflow.com/questions/6277287/hibernate-onetomany-criteria-returns-duplicates?rq=1 — SS_DBA, May 12 '17 at 15:28
Possible duplicate of [hibernate OneToMany criteria returns duplicates](http://stackoverflow.com/questions/6277287/hibernate-onetomany-criteria-returns-duplicates) — cнŝdk, May 12 '17 at 15:34
I need to pass a JUnit test that gets the games with Programmer.getGames(), how can I do it without Criteria? — ssoomm07, May 12 '17 at 15:58
@WEI_DBA don't think it's duplicate - based on his question, OP is looking for answer without using criteria. — Daniel Bickler, May 12 '17 at 16:56
I am not real familiar with Hibernate/JPA when it comes to matters like this, but why can't you use a Set? That would be my naive solution not knowing the "ideal" way to do it within Hibernate. — Daniel Bickler, May 12 '17 at 16:57
Possible duplicate of [One-To-Many relationship gets duplicate objects whithout using "distinct".Why?](http://stackoverflow.com/questions/18753245/one-to-many-relationship-gets-duplicate-objects-whithout-using-distinct-why) — Daniel Bickler, May 12 '17 at 17:04
That duplicate is better for this question. While the question seems different, the answer in that question will supply the answer for this question, that a Set (or LinkedHashSet as given by the Hibernate FAQ to preserve order) is the correct solution without using Criteria — Daniel Bickler, May 12 '17 at 17:06

score 6 · Accepted Answer · answered May 13 '17 at 02:22

While the use of Set<> fundamentally resolves your issue, I'd argue that is simply a bandaid to get the expected results you're after but it doesn't technically address the underlying problem.

You should ultimately be using the default lazy fetch strategy because I'm of the opinion that eagerly loading any associations, particularly collection-based ones, are specific to a query and therefore should be toggled when you construct specific queries and not influenced as a part of your entity mapping model as you're doing.

Consider the future where you add a new query but you're only interestesd in attributes from the aggregate root entity. Your mapping model will still impose eagerly fetching those associations, you'll consume additional resources to by having a larger persistence context which means more memory consumption and impose unnecessary database joins for something which you aren't going to use.

If there are multiple collections that you need to hydrate, I would instead recommend you consider using FetchMode.SUBSELECT instead.

If we assume your query has 10 entities being returned, the default lazy strategy with 2 collections would issue 21 queries (1 for the base result set and 2 for each loaded entity).

The benefit of SUBSELECT is that Hibernate will actually only issue 3 queries (1 for the base result set and 1 for each collection to load all collection elements for all entities). And obviously, depending on certain queries, breaking one query with left-joins into 3 queries could actually perform better at the database level too.

lazy select then require opening transaction all the time – Enerccio Aug 23 '20 at 10:21 — Enerccio, Aug 23 '20 at 10:21

score 4 · Answer 2 · answered May 14 '19 at 06:01

4

Ive resolved this problem with @Fetch(FetchMode.SUBSELECT)

answered May 14 '19 at 06:01

Dmitriy Goy

65
1
6

Saeed Farahi · Answer 3 · 2023-01-24T01:07:36.520

0

@OneToMany(mappedBy = "user", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@Fetch(FetchMode.SUBSELECT)
private List<CompanyUserEntity> companyUserRelations;

I had the same problem. companyUserRelations had duplicate objects (I mean the same pointers to the same object, not duplicated data)

So after reading @dimitry response, I added @Fetch(FetchMode.SUBSELECT) and it worked

edited Jan 24 '23 at 01:07

answered Jan 06 '23 at 15:52

Saeed Farahi

53
1
5

Please add codes as text in the questions/answers please read [this](https://stackoverflow.com/help/formatting) – MD Zand Jan 09 '23 at 18:07

Duplicated results in Hibernate OneToMany List

3 Answers3

Linked