fetch join results in too many sql queries

Hibernate ORM
1

I have a parent-child-grandchild relationship and I’m trying to fetch it in a single sql query.

The Parent has two OneToOne Mappings to the Child, each Child has a OneToMany Association to the Grandchildren:

public class Parent {
    @OneToOne
    @JoinColumn(name = "child_id")
    @Fetch(FetchMode.JOIN)
    private Child child;

    @OneToOne
    @JoinColumn(name = "otherchild_id")
    @Fetch(FetchMode.JOIN)
    private Child otherchild;
}

public class Child {
    @OneToMany(mappedBy = "child")
    @Fetch(FetchMode.JOIN)
    private Set<Grandchild> grandchildren = new HashSet<>();
}

public class Grandchild {
    @ManyToOne
    private Child child;
}

With this Mapping I got two sql queries:


    select
        parent0_.parent_id as parent_i1_121_0_,
        parent0_.child_id as child_id2_121_0_,
        parent0_.otherchild_id as otherchi3_121_0_,
        child1_.child_id as child_id1_21_1_,
        grandchild2_.child_id as child_id2_65_2_,
        grandchild2_.grandchild_id as grandchi1_65_2_,
        grandchild2_.grandchild_id as grandchi1_65_3_,
        grandchild2_.child_id as child_id2_65_3_,
        child3_.child_id as child_id1_21_4_ 
    from
        Parent parent0_ 
    left outer join
        Child child1_ 
            on parent0_.child_id=child1_.child_id 
    left outer join
        Grandchild grandchild2_ 
            on child1_.child_id=grandchild2_.child_id 
    left outer join
        Child child3_ 
            on parent0_.otherchild_id=child3_.child_id 
    where
        parent0_.parent_id=?

    select
        grandchild0_.child_id as child_id2_65_0_,
        grandchild0_.grandchild_id as grandchi1_65_0_,
        grandchild0_.grandchild_id as grandchi1_65_1_,
        grandchild0_.child_id as child_id2_65_1_ 
    from
        Grandchild grandchild0_ 
    where

There’s only one left join to the grandchild table. The second Grandchild Association ist loaded by the second query.

Is it possible two avoid the second sql query?

With debugging I found the class MetamodelDrivenLoadPlanBuilder (org.hibernate.loader.plan.build.spi) doing buildRootEntityLoadPlan with the Object strategy (FetchStyleLoadPlanBuildingAssociationVisitationStrategy) and persister (SingleTableEntityPersister).

The persister object contains all associations, but the strategy object contains only one fetch for the child-grandchild association. Seems that the other fetch is dropped, maybe because Hibernate can’t fnd a difference to the first child- grandchild fetch.

2

This is something that fixed in Hibernate 6.0 though I don’t remember the JIRA issue key under which this was done. I don’t think though that we can fix this in 5.x as that would be too big of a change for existing applications. A possible alternative is to write an HQL query yourself with proper fetch joins.

3

Thanks for your reply! We are using Hibernate 5.6.3 currently.