CODEWITHSUNDEEP
javahibernatejoin
Steve Marion
Steve Marion

Reputation: 289

hibernate generate query without all outer join fetch possible

I have 5 entities :

@Entity
public class A {
    @OneToMany(mappedBy="a", fetch=FetchType.EAGER)
    @Fetch(FetchMode.JOIN)
    private Set<B1> b1s;

    @OneToMany(mappedBy="a", fetch=FetchType.EAGER)
    @Fetch(FetchMode.JOIN)
    private Set<B2> b2s;
}

@Entity
public class B1 {
    @ManyToOne
    @JoinColumn(name="c")
    private C c;
}

@Entity
public class B2 {
    @ManyToOne
    @JoinColumn(name="c")
    private C c;
}

@Entity
public class C {
    @OneToMany(mappedBy="d", fetch=FetchType.EAGER)
    @Fetch(FetchMode.JOIN)
    private Set<D> ds;
}

@Entity
public classD {

}

So in short, A eager join two set of Entity B1 and B2, each one eager joining (implicitely) an Entity C which in turn eager join a set of Entity D.

when loading an A object, the generated query look like 

select (...) from A a0 
left outer join B1 b1 on a0.id=b1.aid
    left outer join C c1 on b1.cid=c1.id
        left outer join D d1 on c1.id=d1.cid
left outer join B2 b2 on a0.id=b2.aid
    left outer join C c2 on b2.cid=c2.id
where a0.id=?

The problem is that the Set of Entity D wich are linked to c2 (C Entity loaded via B2 Entity) are not loaded in the same query, resulting in N subsequent queries for each c2 object.

I would expect Hibernate to generate another left outer join for these objects in the first query, as it already does for the first occurence of D.

I am missing something ? I use hibernate 3.6 on an Oracle DB, is it an known issue ?

Thank you for your time.

Upvotes: 2

Views: 1029

Answers (1)

Dragan Bozanovic
Dragan Bozanovic

Reputation: 23562

Indeed, this seems to be a bug or an incomplete feature. I was able to reproduce it in a simpler scenario as well:

@Entity
public class A {
    @OneToMany(fetch=FetchType.EAGER)
    @Fetch(FetchMode.JOIN)
    @JoinColumn
    private Set<B> bs1;

    @OneToMany(fetch=FetchType.EAGER)
    @Fetch(FetchMode.JOIN)
    @JoinColumn
    private Set<B> bs2;
}

@Entity
public class B {
    @OneToMany(fetch=FetchType.EAGER)
    @Fetch(FetchMode.JOIN)
    @JoinColumn
    private Set<C> cs;
}

@Entity
public class C {

}

When loading A by id, the following joins are generated:

from
    A a0_ 
left outer join
    B bs1x1_ 
        on a0_.id=bs1x1_.bs1_id 
left outer join
    C cs2_ 
        on bs1x1_.id=cs2_.cs_id 
left outer join
    B bs2x3_ 
        on a0_.id=bs2x3_.bs2_id

So, if you really want to fetch everything in one query, you'll have to do it with HQL:

select a from A a 
left join fetch a.b1s b1 
left join fetch b1.c c1 
left join fetch c1.ds 
left join fetch a.b2s b2 
left join fetch b2.c c2 
left join fetch c2.ds 
where a.id = ?

However, I assume that the collections being joined are really small. Because these kinds of joins produce full Cartesian product and are considered a very bad anti-pattern.

Suppose that each of the collections contains only 100 rows (A has 100 b1s and 100 b2s, and each of b1s and b2s has a C which has 100 ds). Then you will get and read a result set containing 100 million rows!

But with lazy collections and batch fetching for example, you would read a total of about 400 rows in a few queries, which is much faster than reading 100 million rows in one query.

Upvotes: 2

Related Questions