How to use multiple JOIN FETCH in one JPQL query
I have below entities:
public class Category {
private Integer id;
@OneToMany(mappedBy = "parent")
private List<Topic> topics;
}
public class Topic {
private Integer id;
@OneToMany(mappedBy = "parent")
private List<Posts> posts;
@ManyToOne
@JoinColumn(name = "id")
private Category parent;
}
public class Post {
private Integer id;
@ManyToOne
@JoinColumn(name = "id")
private Topic parent;
/* Post fields */
}
and I want to fetch all categories with joined topics
and joined posts
using JPQL query. I wrote query like below:
SELECT c FROM Category c
JOIN FETCH c.topics t
JOIN FETCH t.posts p WHERE
But I got the error
org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags
I found articles about this error, but these articles only describe situation where in one entity are two collections to join. My problem is a little different and I don't know how to solve it.
It is possible to do it in one query?
Considering we have the following entities:
And, you want to fetch some parent Post
entities along with all the associated comments
and tags
collections.
If you are using more than one JOIN FETCH
directives:
List<Post> posts = entityManager.createQuery("""
select p
from Post p
left join fetch p.comments
left join fetch p.tags
where p.id between :minId and :maxId
""", Post.class)
.setParameter("minId", 1L)
.setParameter("maxId", 50L)
.getResultList();
Hibernate will throw the MultipleBagFetchException
:
org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags [
com.vladmihalcea.book.hpjp.hibernate.fetching.Post.comments,
com.vladmihalcea.book.hpjp.hibernate.fetching.Post.tags
]
The reason why Hibernate throws this exception is that it does not allow fetching more than one bag because that would generate a Cartesian product.
The worst "solution" others might try to sell you
Now, you will find lots of answers, blog posts, videos, or other resources telling you to use a Set
instead of a List
for your collections.
That's terrible advice. Don't do that!
Using Sets
instead of Lists
will make the MultipleBagFetchException
go away, but the Cartesian Product will still be there, which is actually even worse, as you'll find out the performance issue long after you applied this "fix".
The proper solution
You can do the following trick:
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();
In the first JPQL query,
distinct
DOES NOT go to the SQL statement. That's why we set thePASS_DISTINCT_THROUGH
JPA query hint tofalse
.DISTINCT has two meanings in JPQL, and here, we need it to deduplicate the Java object references returned by
getResultList
on the Java side, not the SQL side.
As long as you fetch at most one collection using JOIN FETCH
, you will be fine.
By using multiple queries, you will avoid the Cartesian Product since any other collection but the first one is fetched using a secondary query.
Always avoid the FetchType.EAGER
strategy
If you're using the FetchType.EAGER
strategy at mapping time for @OneToMany
or @ManyToMany
associations, then you could easily end up with a MultipleBagFetchException
.
You are better off switching from FetchType.EAGER
to Fetchype.LAZY
since eager fetching is a terrible idea that can lead to critical application performance issues.
Conclusion
Avoid FetchType.EAGER
and don't switch from List
to Set
just because doing so will make Hibernate hide the MultipleBagFetchException
under the carpet. Fetch just one collection at a time, and you'll be fine.
As long as you do it with the same number of queries as you have collections to initialize, you are fine. Just don't initialize the collections in a loop, as that will trigger N+1
query issues, which are also bad for performance.
Here is a working example of complex join and multiple consition:
String query_findByProductDepartmentHospital = "select location from ProductInstallLocation location "
+ " join location.product prod " + " join location.department dep "
+ " join location.department.hospital hos " + " where prod.name = :product "
+ " and dep.name.name = :department " + " and hos.name = :hospital ";
@Query(query_findByProductDepartmentHospital)
ProductInstallLocation findByProductDepartmentHospital(@Param("product") String productName,@Param("department") String departName, @Param("hospital") String hospitalName);