如何使用JPQL JOIN FETCH一次性查询Post及关联数据避免N+1问题

实体类修正 #

首先你现有实体类存在2处关联属性映射错误，会直接导致关联查询失效：

1. Post类的postTags属性mappedBy值和关联类的属性名不匹配，改为小写开头：

// Post类原注解
@OneToMany(mappedBy = "Post", cascade = CascadeType.ALL)
// 修正后
@OneToMany(mappedBy = "post", cascade = CascadeType.ALL)
private Collection<PostTag> postTags = new HashSet<PostTag>();

2. PostTag类的items属性mappedBy值和关联类的属性名不匹配，改为驼峰格式：

// PostTag类原注解
@OneToMany(mappedBy = "posttag", cascade = CascadeType.ALL)
// 修正后
@OneToMany(mappedBy = "postTag", cascade = CascadeType.ALL)
private Set<Items> items= new HashSet<Items>();

正确JOIN FETCH写法 #

方案1：单查询直接返回Page（适合数据量小的场景）

直接使用JOIN FETCH逐层拉取所有关联，搭配独立countQuery实现分页，注意该场景下Hibernate会先拉取所有符合条件的全量关联数据，再在内存中做分页，数据量过大会有性能损耗：

@Query(value = "SELECT DISTINCT po FROM Post po " +
        "INNER JOIN FETCH po.postTags pt " +
        "INNER JOIN FETCH pt.tag t " +
        "LEFT JOIN FETCH pt.items i " +
        "LEFT JOIN FETCH i.subItems si " +
        "WHERE t.tagName LIKE :tName " +
        "AND pt.someDateField BETWEEN :sDate AND :eDate " +
        "ORDER BY pt.someDateField DESC, pt.pubDate DESC",
        countQuery = "SELECT COUNT(DISTINCT po.postId) FROM Post po " +
                "INNER JOIN po.postTags pt " +
                "INNER JOIN pt.tag t " +
                "WHERE t.tagName LIKE :tName " +
                "AND pt.someDateField BETWEEN :sDate AND :eDate")
Page<Post> findAllInOneQuery(@Param("tName") String tName, @Param("sDate") String sDate, @Param("eDate") String eDate, Pageable pageable);

方案2：分两次查询优化分页（适合数据量大的场景）

先分页查询符合条件的Post主键集合，再根据主键拉取全量关联数据，避免内存分页性能问题：

// 第一步：分页查符合条件的PostId
@Query("SELECT DISTINCT po.postId FROM Post po " +
        "INNER JOIN po.postTags pt " +
        "INNER JOIN pt.tag t " +
        "WHERE t.tagName LIKE :tName " +
        "AND pt.someDateField BETWEEN :sDate AND :eDate " +
        "ORDER BY pt.someDateField DESC, pt.pubDate DESC")
Page<String> findPostIdsByCondition(@Param("tName") String tName, @Param("sDate") String sDate, @Param("eDate") String eDate, Pageable pageable);

// 第二步：根据PostId拉取全关联数据
@Query("SELECT DISTINCT po FROM Post po " +
        "INNER JOIN FETCH po.postTags pt " +
        "INNER JOIN FETCH pt.tag t " +
        "LEFT JOIN FETCH pt.items i " +
        "LEFT JOIN FETCH i.subItems si " +
        "WHERE po.postId IN :postIds")
List<Post> findAllByPostIdsWithAllRelation(@Param("postIds") Collection<String> postIds);

业务层调用时先执行第一个查询拿到分页后的PostId集合，再调用第二个查询拿到全关联的Post数据，手动封装为Page对象即可。

原有写法错误说明 #

1. JPQL的JOIN FETCH必须基于实体类中定义的关联属性，不能直接跟实体名/表名，也不需要手动写ON条件，你第一次的写法直接把PostTag写在JOIN FETCH后面不符合JPQL语法
2. 第二次写法手动返回了零散字段而非Post实体，无法封装为Page<Post>，同时没有通过关联属性绑定Post和PostTag的关系，才会抛出集合属性引用异常

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