Spring Data JPA一对多关联匹配查询：主实体分页+关联实体限10条方案咨询

核心思路 #

先对符合条件的EntityA做分页，再在同一个查询里对关联的EntityB做行号限制，一次性取出所有需要的数据，不需要分两次查询，同时覆盖「EntityA自身匹配但无符合条件的关联EntityB」的场景，不会漏数据。

对应SQL语句 #

SELECT a.*, b.*
FROM (
    -- 先分页查询符合条件的EntityA，支持正常分页参数（limit、offset）
    SELECT DISTINCT a.*
    FROM entity_a a
    LEFT JOIN entity_b b ON b.entity_a_id = a.id
    WHERE a.search_text LIKE CONCAT('%', :textToFind, '%') OR b.search_text LIKE CONCAT('%', :textToFind, '%')
    ORDER BY a.id -- 可替换为自定义排序字段
    LIMIT :pageSize OFFSET :offset
) a
LEFT JOIN (
    -- 限制每个EntityA最多返回10条匹配的EntityB
    SELECT limited_b.*
    FROM (
        SELECT b.*,
               ROW_NUMBER() OVER(PARTITION BY b.entity_a_id ORDER BY b.created_date_time DESC) AS row_num
        FROM entity_b b
        WHERE b.search_text LIKE CONCAT('%', :textToFind, '%')
    ) limited_b
    WHERE limited_b.row_num <= 10
) b ON b.entity_a_id = a.id

JPA实现代码 #

直接在Repository接口中用@Query注解声明即可，完全符合JPA规范：

public interface EntityARepository extends JpaRepository<EntityA, UUID> {

    @Query(value = """
        SELECT a.*, b.*
        FROM (
            SELECT DISTINCT a.*
            FROM entity_a a
            LEFT JOIN entity_b b ON b.entity_a_id = a.id
            WHERE a.search_text LIKE CONCAT('%', :textToFind, '%') OR b.search_text LIKE CONCAT('%', :textToFind, '%')
            ORDER BY a.id
            LIMIT :pageSize OFFSET :offset
        ) a
        LEFT JOIN (
            SELECT limited_b.*
            FROM (
                SELECT b.*,
                       ROW_NUMBER() OVER(PARTITION BY b.entity_a_id ORDER BY b.created_date_time DESC) AS row_num
                FROM entity_b b
                WHERE b.search_text LIKE CONCAT('%', :textToFind, '%')
            ) limited_b
            WHERE limited_b.row_num <= 10
        ) b ON b.entity_a_id = a.id
        """, nativeQuery = true)
    List<Object[]> searchWithMatchedB(@Param("textToFind") String textToFind, 
                                      @Param("pageSize") int pageSize, 
                                      @Param("offset") int offset);
}

结果组装 #

查询返回的List<Object[]>中，每一行第一个元素是EntityA对象，第二个是EntityB对象（无匹配B时为null），直接用Stream流分组即可得到需要的Map<EntityA, List<EntityB>>：

Map<EntityA, List<EntityB>> result = searchResult.stream()
    .collect(Collectors.groupingBy(
        row -> (EntityA) row[0],
        Collectors.mapping(
            row -> (EntityB) row[1],
            Collectors.filtering(Objects::nonNull, Collectors.toList())
        )
    ));

• 如果使用Hibernate 6及以上版本，可以直接用JPQL写窗口函数逻辑，不需要写数据库相关的原生语句，完全兼容JPA的JPQL规范。
• 如果已经在使用方案3，可以在EntityA的listOfB字段上加上@BatchSize(size = 100)注解，批量加载关联的EntityB，减少查询次数，性能损失极小。

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