How to fetch multiple JPA collections with Blaze Persistence MULTISET
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, we are going to see how we can fetch multiple JPA entity collections without generating an implicit Cartesian Product with the MULTISET strategy offered by the Blaze Persistence open-source project.
The MULTISET fetch strategy is inspired by the MULTISET operator offered by jOOQ.
If you are not familiar with the MULTISET operator, then check out this article first, in which I explain why this jOOQ feature is truly revolutionary.
Domain Model
Let’s consider we have the following entities in our application:
The Post
entity is the root aggregate, and it has the following associations:
- a many-to-many association with the
Tag
entity - a one-to-many association with the
PostComment
child entity
The PostComment
entity also has a one-to-many association with the UserView
entity, which has a many-to-one association with the User
entity.
So, our entities form a multi-level hierarchical structure.
Fetching multiple JPA collections using a single entity query with multiple JOIN FETCH
Let’s assume we want to fetch a list of Post
entities, along with all their associated tags, comments, and user votes.
At first, we might attempt to do that using an entity query that looks as follows:
List<Post> posts = entityManager.createQuery(""" select p from Post p left join fetch p.tags t left join fetch p.comments pc left join fetch pc.votes v left join fetch v.user u where p.id between :minId and :maxId """, Post.class) .setParameter("minId", 1L) .setParameter("maxId", 50L) .getResultList();
However, when running the above query, Hibernate will throw the following exception:
Caused by: org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags: [ com.vladmihalcea.hpjp.spring.blaze.domain.PostComment.votes, com.vladmihalcea.hpjp.spring.blaze.domain.Post.comments ]
The
MultipleBagFetchException
is thrown if the fetched collections are mapped asList
.If you change the collection type to
Set
, Hibernate will no longer throw theMultipleBagFetchException
, and instead will issue an implicit Cartesian Product that can affect the performance of your data access layer.For more details about the Hibernate
MultipleBagFetchException
, check out this article.
Fetching multiple JPA collections using a single query using MULTISET
To fetch multiple JPA collections with a single SQL query that avoids a Cartesian Product, we can use the MULTISET fetch strategy offered by Blaze Persistence.
This feature works with Entity Views, and for our use case, we will define the following views:
The entity views are mapped as follows:
@EntityView(Post.class) public interface PostView { @IdMapping Long getId(); String getTitle(); } @EntityView(Tag.class) public interface TagView { @IdMapping Long getId(); String getName(); } @EntityView(PostComment.class) public interface PostCommentView { @IdMapping Long getId(); String getReview(); @Mapping(fetch = MULTISET) List<UserVoteView> getVotes(); } @EntityView(UserVote.class) public interface UserVoteView { @IdMapping Long getId(); UserView getUser(); int getScore(); } @EntityView(User.class) public interface UserView { @IdMapping Long getId(); String getFirstName(); String getLastName(); } @EntityView(Post.class) public interface PostWithCommentsAndTagsView extends PostView { @Mapping(fetch = MULTISET) List<PostCommentView> getComments(); @Mapping(fetch = MULTISET) List<TagView> getTags(); }
The
@Mapping(fetch = MULTISET)
allows us to instruct Blaze Persistence to use the MULTISET strategy when fetching a given association.
Now, we will define the findPostWithCommentsAndTagsViewByIds
service method in our ForumService
component:
@Service @Transactional(readOnly = true) public class ForumService { private final PostRepository postRepository; public ForumService(PostRepository postRepository) { this.postRepository = postRepository; } public List<PostWithCommentsAndTagsView> findPostWithCommentsAndTagsViewByIds( Long minId, Long maxId) { return postRepository.findPostWithCommentsAndTagsViewByIds( minId, maxId ); } }
The PostRepository
is defined like this:
@Repository public interface PostRepository extends BaseJpaRepository<Post, Long>, CustomPostRepository { }
First, to avoid all the issues associated with the default Spring Data JpaRepository
, I’m using the BaseJpaRepository
from the Hypersistence Utils project. For more details on why I prefer using the BaseJpaRepository
, check out this article.
Second, the PostRepository
extends the CustomPostRepository
so that we can provide a custom implementation for our MULTISET query. If you’re unfamiliar with the custom Spring Data Repository feature, check out this article first.
The CustomPostRepository
interface looks as follows:
public interface CustomPostRepository { List<PostWithCommentsAndTagsView> findPostWithCommentsAndTagsViewByIds( Long minId, Long maxId ); }
The CustomPostRepository
interface is implemented by CustomPostRepositoryImpl
, which looks as follows:
public class CustomPostRepositoryImpl implements CustomPostRepository { private final EntityManager entityManager; private final CriteriaBuilderFactory criteriaBuilderFactory; private final EntityViewManager entityViewManager; public CustomPostRepositoryImpl( EntityManager entityManager, CriteriaBuilderFactory criteriaBuilderFactory, EntityViewManager entityViewManager) { this.entityManager = entityManager; this.criteriaBuilderFactory = criteriaBuilderFactory; this.entityViewManager = entityViewManager; } @Override public List<PostWithCommentsAndTagsView> findPostWithCommentsAndTagsViewByIds( Long minId, Long maxId) { return entityViewManager.applySetting( EntityViewSetting.create(PostWithCommentsAndTagsView.class), criteriaBuilderFactory.create(entityManager, Post.class) ) .where(Post_.ID) .betweenExpression(":minId") .andExpression(":maxId") .setParameter("minId", minId) .setParameter("maxId", maxId) .getResultList(); } }
The CriteriaBuilderFactory
and EntityViewManager
are specific to Blaze Persistence and can be configured in a Spring application like this:
@Bean public CriteriaBuilderFactory criteriaBuilderFactory( EntityManagerFactory entityManagerFactory) { CriteriaBuilderConfiguration config = Criteria.getDefault(); return config.createCriteriaBuilderFactory(entityManagerFactory); } @Bean public EntityViewConfiguration entityViewConfiguration( CriteriaBuilderFactory criteriaBuilderFactory) { EntityViewConfiguration entityViewConfiguration = EntityViews .createDefaultConfiguration(); for(Class entityViewClass : ReflectionUtils .getClassesByPackage(PostView.class.getPackageName())) { entityViewConfiguration.addEntityView(entityViewClass); } return entityViewConfiguration; } @Bean public EntityViewManager entityViewManager( CriteriaBuilderFactory cbf, EntityViewConfiguration entityViewConfiguration) { return entityViewConfiguration.createEntityViewManager(cbf); }
Now, when fetching the list of PostWithCommentsAndTagsView
objects, we can see that all collections have been properly fetched:
List<PostWithCommentsAndTagsView> posts = forumService .findPostWithCommentsAndTagsViewByIds( 1L, 50L ); assertEquals(POST_COUNT, posts.size()); for (PostWithCommentsAndTagsView post : posts) { assertEquals(POST_COMMENT_COUNT, post.getComments().size()); for(PostCommentView comment : post.getComments()) { assertEquals(VOTE_COUNT, comment.getVotes().size()); } assertEquals(TAG_COUNT, post.getTags().size()); }
When checking the application log, we can see that Blaze Persistence executed the following SQL query:
SELECT p.id, ( SELECT json_agg( json_build_object( 'f0', '' || pc.id, 'f1', '' || pc.review, 'f2', ( SELECT json_agg( json_build_object( 'f0', '' || uv.id, 'f1', '' || uv.score, 'f2', '' || uv.user_id, 'f3', '' || u.first_name, 'f4', '' || u.last_name ) ) FROM user_vote uv LEFT JOIN blog_user u ON u.id = uv.user_id WHERE pc.id = uv.comment_id ) ) ) FROM post_comment pc WHERE p.id = pc.post_id ), ( SELECT json_agg( json_build_object( 'f0', '' || pt.tag_id, 'f1', '' || t1_1.name ) ) FROM post_tag pt JOIN tag t1_1 ON t1_1.id = pt.tag_id WHERE p.id = pt.post_id ), p.title FROM post p WHERE p.id BETWEEN 1 AND 50
Cool, right?
I'm running an online workshop on the 11th of October about High-Performance SQL.If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
When you need to fetch multiple JPA collections, a very efficient solution is to use the MULTISET fetching strategy offered by Blaze Persistence.
Another alternative is to use multiple JPQL queries, as explained by this article.
Or, you can use the jOOQ MULTISET operator and build the SQL query programmatically using the type-safe query builder offered by jOOQ.
