CODEWITHSUNDEEP
javaoracle-databasejpajpa-2.0jpql
RNJ
RNJ

Reputation: 15552

Fetch joins returning more rows than they should

I have hit a strange problem with some joins I am doing. In a nutshell if I do a left join I get a correct number of rows. If I do a left join fetch I get more rows than expected.

My entity classes are (using pseudocode for brevity)

public class One {
   ...
   private Set<Two> twos;
}

public class Two {
   ...
   private Set<Three> threes;
}

public class Three {
   ...
}

In my relationship I have four twos in class one and nine threes in each class two

I was expecting to have returned one class one with four twos and in each of these twos nine threes.

I get this if I use

List<One> res = em.createQuery("select o from One o left join o.twos t left join t.threes where o.id = 322", One.class).getResultList();
List<Two> twos = resultList.get(0).getTwos(); //results in four twos - CORRECT

List<One> res = em.createQuery("select o from One o left join fetch o.twos t left join t.threes where o.id = 322", One.class).getResultList();
List<Two> twos = resultList.get(0).getTwos(); //results in four twos - CORRECT

List<One> res = em.createQuery("select o from One o left join fetch o.twos t left join fetch.threes where o.id = 322", One.class).getResultList();
List<Two> twos = resultList.get(0).getTwos(); //results in thirty size twos - *INCORRECT* (is four * nine)

Why does the second fetch cause it to multiply. I am having performance problems with my DB so want to do fetch joins to try and improve them. By doing the fetches though I seem to have lots more results than expected.

I am using Oracle XE as my DB and JPA2/hibernate as my ORM.

Thanks

Upvotes: 0

Views: 1250

Answers (1)

RNJ
RNJ

Reputation: 15552

As per comment from @JB Nizet I found a spurious List in my hierachy. To solve this you need to make sure that everything is using a set. If you have more than one list you will get JPA errors about cannot fetch multiple bags. If you have one list you wont but you end up with duplicates

Upvotes: 1

Related Questions