JPA Repository nativeQuery带Pageable时size=1关联查询报错如何解决

Spring Data JPA 对原生查询自动生成 count 查询的逻辑存在局限性，遇到带表别名、GROUP BY 聚合、自定义字段别名的查询时，会直接粗暴替换原查询的 SELECT 部分为 count(第一个查询项的前缀)，你的原查询第一个返回字段是 ppd.userId，所以框架自动生成了非法的 select count(ppd) ... 语句，最终触发字段不存在报错。

提供两种可选方案，可根据业务需求选择：

方案1：自定义 countQuery 保留 Page 分页特性 #

如果你的业务需要用到 Page 自带的总条数、总页数等元数据，直接在 @Query 注解中添加 countQuery 参数，写入符合逻辑的计数语句即可，计数语句仅需要统计符合筛选条件的分组总数量，写法如下：

@Query(value = "SELECT ppd.userId, AVG(Coalesce(sm.score, 0)) AS avgScore " +
            " FROM Table1 ppd " +
            " LEFT JOIN Table2 sm ON sm.userId = ppd.userId AND sm.created BETWEEN :start AND :end " +
            " WHERE ppd.someId IN (:IdList) GROUP BY ppd.userId",
       countQuery = "SELECT COUNT(DISTINCT ppd.userId) " +
            " FROM Table1 ppd " +
            " LEFT JOIN Table2 sm ON sm.userId = ppd.userId AND sm.created BETWEEN :start AND :end " +
            " WHERE ppd.someId IN (:IdList)",
       nativeQuery = true)
Page<MyDtoProjection> getAvg(@Param("IdList") List<String> IdList,
                             @Param("start") Long start,
                             @Param("end") Long end,
                             Pageable pageable);

你也可以用子查询写法实现 countQuery，结果完全一致：

SELECT COUNT(*) FROM (
    SELECT ppd.userId FROM Table1 ppd 
    LEFT JOIN Table2 sm ON sm.userId = ppd.userId AND sm.created BETWEEN :start AND :end 
    WHERE ppd.someId IN (:IdList) GROUP BY ppd.userId
) t

方案2：返回List避免生成count查询（更适配你的场景） #

你的业务已经固定了 limit=1，不需要总页数、总条数等分页元数据的话，直接把方法返回值从 Page<MyDtoProjection> 改成 List<MyDtoProjection> 即可，框架不会再自动生成 count 查询，原有查询逻辑、Pageable 的分页和排序能力完全不受影响，你拿到结果后直接取第一个元素即可。

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