CODEWITHSUNDEEP
hibernatejpafetchlazy-evaluation
abkvandrd
abkvandrd

Reputation: 203

Left join fetching duplication

I have some troubles with JPA(Hibernate) and LazyFetching

I have some entities: Quest, Reward, Contract

Relationship(all are Lazy):

DB contains this data: Quest1 has Reward1, Reward2 and Contract1, Contract2;

In come cases I need Quest with its Rewards & Contracts, so i do in JPArepository:

@Query("select Q from Quest Q left join fetch Q.rewards left join fetch Q.contracts")
List<Quest> getAllQuestsWithRewardsAndContracts();

the problem is that I recive
One quest with 4(but it has only 2) rewards(Reward1,Reward2,Reward1,Reward2) and two Contracts.
I get rewards duplicated!
I dont understand why !
If i do so:

@Query("select distinct Q from Quest Q left join fetch Q.rewards")
List<Quest> getAllQuestsWithRewardsRew();

I recive one quest with two rewards(Reward1, Reward2)

Why ? Why i have duplication ?

Upvotes: 0

Views: 5361

Answers (2)

frictionlesspulley
frictionlesspulley

Reputation: 12368

That is the nature how Hibernate works. It does not remove duplicate generated out of LEFT OUTER JOINS. You can get explicitly specify that you need distinct results

For example if you were using criteria to fetch data you could specify 

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

Check out the SQL being generated when you fetch the data. Here is a FAQ which explains more about why Hibernate behaves this way here

Also in the first query you are loading multiple collections (Rewards and Contracts) in the same SQL which might result in a Cartesian product. Hibernate has a Futures API which helps in loading multiple children collections using simpler SQLs than those which generate cartesian products. (I am not sure if Futures would work with JPA)

Upvotes: 6

Kanagaraj M
Kanagaraj M

Reputation: 966

One table may have more rows then another. So give some condition to avoid duplication...

Upvotes: 0

Related Questions