JPA : How to define @NamedEntityGraph for 3 levels?

Question

I have 3 entities. Branch,Subject,Topic. Branch has list of subjects and Subject has list of topics. Also subjectList and topicList both are lazy. I want to fetch all branch including its subjects and topics in single query.

1.

@Entity
public class Branch implements Serializable 
{
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    private String name;

    @OneToMany(mappedBy = "branch")
    private List<Subject> subjectList;
    //Getters and Setters
}

2.

@Entity
public class Subject implements Serializable 
{
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    private String name;

    @ManyToOne()
    private Branch branch;

    @OneToMany(mappedBy = "subject")
    private List<Topic> topicList;
    //Getters and Setters       
}

3.

@Entity
public class Topic implements Serializable 
{
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    private String name;

    @ManyToOne()
    private Subject subject;
    //Getters and Setters
}

I tried the method below but it didn't work.

@NamedEntityGraph(name="branch_subject", 
attributeNodes = {
    @NamedAttributeNode(value="name"),
    @NamedAttributeNode(value="subjectList", subgraph = "subjectListGraph")
},
subgraphs = {
    @NamedSubgraph(name="subjectListGraph",
            attributeNodes = {
                @NamedAttributeNode(value="name"),
                @NamedAttributeNode(value = "topicList", subgraph = "topicListGraph")
            }
    ),
    @NamedSubgraph(name="topicListGraph",
            attributeNodes = {
                    @NamedAttributeNode("name")
            }
    )
}
)

Also following code is used to fetch data from database, I am using JPQL as follows

    EntityGraph branchEntityGraph = entityManager
                .getEntityGraph("branch_subject");

        Branch branch = entityManager
                .createQuery("SELECT b from Branch b WHERE b.id=:ID",
                        Branch.class)
                .setHint("javax.persistence.loadgraph", branchEntityGraph)
                .setParameter("ID", branch1.getId()).getResultList().get(0);

This gives below exception

org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags

Answer 1

Hibernate doesn't allow you to fetch multiple Bags because it would end up fetching a Cartesian Product.

M → N → P one-to-many or many-to-many relations

For a multi-nested hierarchy, you can use JOIN FETCH on multiple collections as long as your lists are mapped as Set.

M → N and M → P one-to-many or many-to-many relations

For sibling collections, like M → N and M → P, don't switch to using Set instead of List.

Using a Set instead of a List to avoid the MultipleBagFetchException is a very bad idea since you will still end up with a Cartesian Product, and that's going to cause performance issues because you are going to fetch M x N x P records.

In this case, a much better approach is to fetch one collection with the first query and use additional queries for the remaining collections:

List<Post> _posts = entityManager.createQuery("""
    select distinct p
    from Post p
    left join fetch p.comments
    where p.id between :minId and :maxId
    """, Post.class)
.setParameter("minId", 1L)
.setParameter("maxId", 50L)
.setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
.getResultList();

_posts = entityManager.createQuery("""
    select distinct p
    from Post p
    left join fetch p.tags t
    where p in :posts
    """, Post.class)
.setParameter("posts", _posts)
.setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
.getResultList();

This strategy allows you to avoid the M x N x P result set by fetching M x (N + P) records instead.

Fetching from the child-side to the parent

If you have to use INNER JOIN when fetching the child collection, then you can simply [fetch from the inner-most Child up to the root][3] and reassemble the structure afterward. This is much more efficient since the query goes like this:

select t 
from Topic t
join t.subject s
join s.branch b