使用Spring Data JPA按日期执行订单聚合操作报错如何解决？

以下是你代码中存在的几个核心错误，以及对应的修复方案： #

错误清单 #

• JPQL语法错误：你在SUM函数中写的head_count是数据库表字段名，而JPQL要求使用实体类的属性名，对应的实体属性是headCount，且你给JourneyFoodOrder定义了别名j，所有属性引用都要带上别名前缀。
• @Modifying注解误用：@Modifying仅用于UPDATE/DELETE这类数据变更操作，聚合查询属于读操作，添加该注解会导致JPA校验失败。
• 参数类型不匹配：Repository方法中mealRetrievalDate参数原本定义为Date类型，但实体类中该字段是LocalDate，业务层传递的参数也是LocalDate，类型不匹配导致查询校验失败。
• 聚合投影类配置错误：AggregateJourneyFoodOrder是用于接收聚合结果的POJO，不需要标注@Entity、@Id，只要提供和JPQL返回参数顺序、类型完全匹配的全参构造函数即可。
• 缺少GROUP BY子句：聚合查询按日期分组，需要添加GROUP BY j.mealRetrievalDate保证返回结果符合预期。
• 返回值类型不匹配：Repository方法返回的是List<AggregateJourneyFoodOrder>，业务层直接返回给控制层赋值给单个AggregateJourneyFoodOrder对象会报错，需要做判空取第一条的处理。

修正后的代码 #

1. JourneyFoodOrderRepository #

@Repository
public interface JourneyFoodOrderRepository extends JpaRepository<JourneyFoodOrder, Long>{

    @Query("SELECT new 你自己的AggregateJourneyFoodOrder全类名(SUM(j.headCount), SUM(j.bread), SUM(j.achar), SUM(j.jam) ,SUM(j.others), SUM(j.puri), SUM(j.roti), SUM(j.thepla), j.mealRetrievalDate)"
            + " FROM JourneyFoodOrder j WHERE j.orderStatus=:orderStatus AND j.mealRetrievalDate = :mealRetrievalDate GROUP BY j.mealRetrievalDate")
    List<AggregateJourneyFoodOrder> getOrdersByDateAndNotDisabled(@Param("orderStatus")String orderStatus, @Param("mealRetrievalDate")LocalDate mealRetrievalDate);
}

注意：构造函数要写全类名，避免JPA找不到对应类。

2. AggregateJourneyFoodOrder #

// 去掉@Entity、@Id注解，改为普通POJO
public class AggregateJourneyFoodOrder {
    private long totalHeadCount;
    private long totalBread;
    private long totalAchar;
    private long totalJam;
    private long totalOthers;
    private long totalPuri;
    private long totalRoti;
    private long totalThepla;
    private LocalDate mealRetrievalDate;

    // 必须提供和JPQL查询返回参数顺序、类型完全一致的全参构造函数
    public AggregateJourneyFoodOrder(long totalHeadCount, long totalBread, long totalAchar, long totalJam, long totalOthers, long totalPuri, long totalRoti, long totalThepla, LocalDate 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;
    }

    // 原有Getters/Setters保留即可
}

3. JourneyFoodServiceImpl #

@Transactional
@Service
public class JourneyFoodServiceImpl implements JourneyFoodService {
    private final JourneyFoodOrderRepository repository;

    @Autowired
    private UserService defaultUserService;
    
    public JourneyFoodServiceImpl(JourneyFoodOrderRepository repository) {
        this.repository = repository;
    }   
    @Override
    public AggregateJourneyFoodOrder getOrdersByDateAndNotDisabled(LocalDate mealRetrievalDate) {
        List<AggregateJourneyFoodOrder> resultList = repository.getOrdersByDateAndNotDisabled("PLACED",mealRetrievalDate);
        return resultList.isEmpty() ? null : resultList.get(0);
    }
}

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