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 as List.

If you change the collection type to Set, Hibernate will no longer throw the MultipleBagFetchException, 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.

Insert details about how the information is going to be processed

DOWNLOAD NOW