Spring Data JPA执行聚合函数查询特定日期数据报错无法获取结果

• 参数不匹配问题：Repository方法定义了Boolean isRemoved入参，但@Query语句中直接硬编码了isRemoved=false的过滤条件，完全没有使用该参数。Spring Data JPA会校验方法入参和查询语句中用到的参数是否匹配，匹配失败就会抛出你遇到的参数未找到异常。
• @Modifying注解误用：@Modifying注解仅用于UPDATE/DELETE这类修改数据的操作，你当前是SELECT查询，不需要加该注解，加了反而会导致执行逻辑异常。
• JPQL字段引用错误：JPQL中引用的是实体类的属性名，不是数据库表的字段名，你写的head_count是数据库字段名，实际应该对应JourneyFoodOrder实体类的headCount属性。
• 投影类构造参数顺序不匹配：你SELECT new时传入的参数顺序是mealRetrievalTime在前，聚合字段在后，但AggregateJourneyFoodOrder类的属性顺序是聚合字段在前，mealRetrievalDate在后，参数顺序不匹配会导致实例化投影类时报错。
• 缺少聚合分组条件：聚合查询必须搭配GROUP BY子句，否则查询逻辑不合法。

Repository查询方法 #

如果不需要动态控制isRemoved的状态，直接删除冗余的入参即可：

@Query("SELECT new AggregateJourneyFoodOrder(SUM(headCount), SUM(bread), SUM(achar), SUM(jam), SUM(others), SUM(puri), SUM(roti), SUM(thepla), mealRetrievalTime) " +
        "FROM JourneyFoodOrder WHERE isRemoved = false AND mealRetrievalTime = :mealRetrievalDate " +
        "GROUP BY mealRetrievalTime")
List<AggregateJourneyFoodOrder> getOrdersByDateAndNotDisabled(Date mealRetrievalDate);

对齐投影类构造函数 #

需要保证AggregateJourneyFoodOrder的构造函数参数顺序和SELECT后的字段顺序完全一致：

public class AggregateJourneyFoodOrder {
    private int totalHeadCount;
    private int totalBread;
    private int totalAchar;
    private int totalJam;
    private int totalOthers;
    private int totalPuri;
    private int totalRoti;
    private int totalThepla;
    private Date mealRetrievalDate;

    // 必须和SELECT后的参数顺序、类型完全匹配
    public AggregateJourneyFoodOrder(int totalHeadCount, int totalBread, int totalAchar, int totalJam, int totalOthers,
                                     int totalPuri, int totalRoti, int totalThepla, Date mealRetrievalDate) {
        this.totalHeadCount = totalHeadCount;
        this.totalBread = totalBread;
        this.totalAchar = totalAchar;
        this.totalJam = totalJam;
        this.totalOthers = totalOthers;
        this.totalPuri = totalPuri;
        this.totalRoti = totalRoti;
        this.totalThepla = totalThepla;
        this.mealRetrievalDate = mealRetrievalDate;
    }
    // 其余getter、setter省略
}

如果需要灵活控制isRemoved的过滤状态，不要硬编码在查询语句中，修改为参数绑定即可：

@Query("SELECT new AggregateJourneyFoodOrder(SUM(headCount), SUM(bread), SUM(achar), SUM(jam), SUM(others), SUM(puri), SUM(roti), SUM(thepla), mealRetrievalTime) " +
        "FROM JourneyFoodOrder WHERE isRemoved = :isRemoved AND mealRetrievalTime = :mealRetrievalDate " +
        "GROUP BY mealRetrievalTime")
List<AggregateJourneyFoodOrder> getOrdersByDateAndNotDisabled(@Param("isRemoved") Boolean isRemoved, @Param("mealRetrievalDate") Date mealRetrievalDate);

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