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

Answer 2

The solution is fairly simple use JPA 2.1 where you need add entiry grapgh at JPA repository only

@Repository
public interface BranchRepo extends JpaRepository< Branch, Long> {

@EntityGraph(attributePaths = {"subjectList" , 
"subjectList.topicList"})
List<Product> findAll();
}

This will create Join between the bi-directional mapping of Branch-> SubjectList-> TopicList

Blockquote you need to override equals method in each of these entity classes with ID else this will not work. You can get rid of complex multilevel NamedEntityGraph at each entity class which is not needed

Answer 3

I guess I had some similar issue. All my Device entities have a Transaction object which in my case stores the datetime and user and computer information for that particular object. Also have a DeviceType entity that has transaction object and has a Many to one relation with my Device entity. So Actually I had both 1 level and 2 levels of nested relations with this Transaction Entity. I got some nested exception because of that. Using Subgraph fixed that issue for me. Here is my NamedEntityGraphs definition code. Hope it helps:

@NamedEntityGraphs(value = {
    @NamedEntityGraph(name = "Device.AllRelations",
            attributeNodes = {
                    @NamedAttributeNode("transaction"),
                    @NamedAttributeNode(value = "deviceType", subgraph = "DeviceType.Transaction")
            },
            subgraphs = {
                    @NamedSubgraph(name = "DeviceType.Transaction", attributeNodes = {
                            @NamedAttributeNode("transaction")
                    })
            }
    ),

    @NamedEntityGraph(name = "Device.TransactionOnly", attributeNodes = {
            @NamedAttributeNode("transaction")
    }),
})