hibernate join fetch one to many multiple tables

Question

I have the following entity relationship in my data model.

Entity A: one-to-many :Entity B

Entity B: one-to-many :Entity C

Entity B: one-to-many :Entity D

Hibernate Entities:

public class EntityA {
    
    private Integer id;
    
    @OneToMany
    private List<EntityB> entityBList;
}

public class EntityB {
    
    private Integer id;

    @ManyToOne
    private EntityA entityA;
    
    @OneToMany(fetch=FetchType.LAZY)
    private List<EntityC> entityCList;

    @OneToMany(fetch=FetchType.LAZY)
    private List<EntityD> entityDList;
}

public class EntityC {
    
    private Integer id;
    
    @ManyToOne
    private EntityB entityB;
}

public class EntityD {

    private Integer id;

    @ManyToOne
    private EntityB entityB;
}

Requirement: I want to query on Entity C with join fetch of Entity B and also eagerly fetch Entity D at the same time. After the query is completed I expect that if I do entityC.getEntityB().getEntityDList(), it should not lead to N+1 query problem in hibernate.

I was trying the following JPQL query:

select ec from EntityC ec 
join fetch ec.entityB eb 
join fetch eb.entityD ed 
where ec.id = :id

This leads to duplicates in the result because of cross join with Entity D. Instead of one result, I get n results where n is the size of list of Entity D.

How can I avoid this? Is there any way to fetch the Entity D without cross join in JPQL?

score 1 · Answer 1 · answered Apr 01 '21 at 10:21

The first thing is to use the DISTINCT JPQL keyword on your query, e.g. as:

TypedQuery<EntityC> query = em.createQuery("SELECT DISTINCT ec FROM EntityC ec JOIN FETCH ec.entityB eb JOIN FETCH eb.entityDList ed WHERE ec.id = :id", EntityC.class);
//                                                 ^^^^^^^^

This will eliminate the duplicates but has the side-effect of passing the DISTINCT to the SQL query as well, which is not something that you want. Read details here, here and in this excellent answer. Long story short - quoting Vlad Mihalcea's answer:

By passing DISTINCT to the SQL query, the EXECUTION PLAN is going to execute an extra Sort phase which adds overhead without bringing any value[...]

The solution is in the linked articles and answers, but in short, if you are on Hibernate >= 5.2.2, use the hibernate.query.passDistinctThrough hint:

TypedQuery<EntityC> query = em.createQuery("SELECT DISTINCT ec FROM EntityC ec JOIN FETCH ec.entityB eb JOIN FETCH eb.entityDList ed WHERE ec.id = :id", EntityC.class);
query.setHint("hibernate.query.passDistinctThrough", false);
// -OR-
query.setHint(QueryHints.HINT_PASS_DISTINCT_THROUGH, false); // import org.hibernate.jpa.QueryHints

hibernate join fetch one to many multiple tables

1 Answers1