如何用Hibernate Criteria替代查询字符串解决MultipleBagFetchException

问题描述 #

需要将Stack Overflow用户Vlad Mihalcea在2018年6月27日提供的、用于解决Hibernate MultipleBagFetchException的JPQL查询，转换为符合编码规范的Hibernate Criteria命令。

原JPQL解决方案 #

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();

相关实体类 #

Post类定义：

// 同时包含两个需要加载的集合的Post类
public class Post {
    
    ...

    @OneToMany(mappedBy = "post", fetch = FetchType.LAZY)
    private List<Comment> comments;

    @OneToMany(mappedBy = "post", fetch = FetchType.LAZY)
    private List<Tag> tags;
    
    ...
}

用户错误的Criteria实现及异常 #

错误代码 #

PostService类中的查询方法：

public class PostService {
    ...
    
    public List<Post> findingAllPosts() {
        CriteriaBuilder builder = entityManager.getCriteriaBuilder();
        
        CriteriaQuery<Post> criteria = builder.createQuery(Post.class);
        Root<Post> root = criteria.from(Post.class);
        
        root.fetch(Post_.comments, JoinType.LEFT);
        criteria.select(root);
        criteria.distinct(true);
        
        List<Post> posts = entityManager
                .createQuery(criteria)
                .setHint(QueryHints.HINT_PASS_DISTINCT_THROUGH, false)
                .getResultList();
        
        root.fetch(Post_.tags, JoinType.LEFT);
        // 尝试的不同写法之一：
        criteria.select(root)
            .where(root
                .in(":posts")
            );
        
        posts = entityManager
            .createQuery(criteria)
            .setParameter("posts", posts)
            .setHint(QueryHints.HINT_PASS_DISTINCT_THROUGH, false)
            .getResultList();
            
        return posts;
    }
}

抛出的异常 #

org.hibernate.QueryException:
query specified join fetching, but the owner of the
fetched association was not present in the select list
[FromElement{explicit,not a collection join,fetch join,
fetch non-lazy properties,
classAlias=generatedAlias1,
role=at.test.Post.comments,
tableName=TEST.COMMENT,
tableAlias=comment1_,
origin=TEST.POST post0_,columns={post0_.ID ,className=at.test.Comment}}]
[
select distinct generatedAlias0
from at.test.Post as generatedAlias0
left join fetch generatedAlias0.comments as generatedAlias1
left join fetch generatedAlias0.tags as generatedAlias0
where generatedAlias0 in (:param0)
]:
javax.ejb.EJBTransactionRolledbackException:
org.hibernate.QueryException:
query specified join fetching, but the owner of the
fetched association was not present in the select list
[FromElement{explicit,not a collection join,fetch join,
fetch non-lazy properties,
classAlias=generatedAlias1,
role=at.test.Post.comments,
tableName=TEST.COMMENT,
tableAlias=comment1_,
origin=TEST.POST post0_,
columns={post0_.ID ,className=at.test.Comment}}]
[
select distinct generatedAlias0
from at.test.Post as generatedAlias0
left join fetch generatedAlias0.comments as generatedAlias1
left join fetch generatedAlias0.tags as generatedAlias0
where generatedAlias0 in (:param0)]

错误原因分析 #

• 重复复用查询对象：第一次查询后，原CriteriaQuery和Root已绑定comments的fetch关联，第二次直接添加tags的fetch会导致查询同时加载两个Bag集合，触发异常，且Hibernate不支持修改已构建完成的查询。
• IN子句写法错误：root.in(":posts")是错误用法，不能直接传入字符串参数名，需通过CriteriaBuilder构建正确的IN条件。

正确的Criteria实现 #

public class PostService {
    ...
    
    public List<Post> findingAllPosts() {
        CriteriaBuilder builder = entityManager.getCriteriaBuilder();
        
        // 第一步：查询并加载comments集合
        CriteriaQuery<Post> firstCriteria = builder.createQuery(Post.class);
        Root<Post> firstRoot = firstCriteria.from(Post.class);
        
        // 左连接fetch comments
        firstRoot.fetch(Post_.comments, JoinType.LEFT);
        firstCriteria.select(firstRoot);
        firstCriteria.distinct(true);
        
        // 添加ID范围条件
        Predicate idRange = builder.between(firstRoot.get(Post_.id), 1L, 50L);
        firstCriteria.where(idRange);
        
        List<Post> posts = entityManager
                .createQuery(firstCriteria)
                .setHint(QueryHints.HINT_PASS_DISTINCT_THROUGH, false)
                .getResultList();
        
        // 第二步：基于已有Post集合，查询并加载tags集合
        CriteriaQuery<Post> secondCriteria = builder.createQuery(Post.class);
        Root<Post> secondRoot = secondCriteria.from(Post.class);
        
        // 左连接fetch tags
        secondRoot.fetch(Post_.tags, JoinType.LEFT);
        secondCriteria.select(secondRoot);
        secondCriteria.distinct(true);
        
        // 添加IN条件：匹配第一步查询到的posts
        Predicate inCondition = secondRoot.in(posts);
        secondCriteria.where(inCondition);
        
        posts = entityManager
                .createQuery(secondCriteria)
                .setHint(QueryHints.HINT_PASS_DISTINCT_THROUGH, false)
                .getResultList();
            
        return posts;
    }
}

关键说明 #

• 两次查询必须创建独立的CriteriaQuery和Root对象，避免同一查询中同时加载两个Bag类型集合（List默认属于Bag）。
• 使用builder.in(root)构建IN子句，直接传入第一步查询得到的posts集合即可，Hibernate会自动处理参数绑定。
• 保留distinct(true)和PASS_DISTINCT_THROUGH提示，既能避免返回重复Post实例，又能避免数据库执行不必要的DISTINCT语句，提升性能。

内容的提问来源于stack exchange，提问作者HyperFreak