时间戳

首页 > 解决方案 > 无法使用 Spring Data JPA 执行聚合函数并获取结果

mysql - 无法使用 Spring Data JPA 执行聚合函数并获取结果

问题描述

我正在尝试对特定日期执行聚合操作。请在下面找到相同的查询。


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

我得到以下例外

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'adminController': Unsatisfied dependency expressed through field 'journeyFoodServiceImpl'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'journeyFoodServiceImpl' defined in file [C:\Users\TiaaUser\Desktop\AWS\journeyfood\target\classes\org\brahmakumaris\journeyfood\service\JourneyFoodServiceImpl.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'journeyFoodOrderRepository' defined in org.brahmakumaris.journeyfood.repository.JourneyFoodOrderRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Invocation of init method failed; nested exception is java.lang.IllegalStateException: Using named parameters for method public abstract java.util.List org.brahmakumaris.journeyfood.repository.JourneyFoodOrderRepository.getOrdersByDateAndNotDisabled(java.lang.Boolean,java.util.Date) but parameter 'Optional[isRemoved]' not found in annotated query 'SELECT new AggregateJourneyFoodOrder(SUM(head_count), SUM(bread), SUM(achar), SUM(jam) ,SUM(others), SUM(puri), SUM(roti), SUM(thepla))  FROM JourneyFoodOrder WHERE isRemoved=false AND mealRetrievalTime = :mealRetrievalDate'!
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.resolveFieldValue(AutowiredAnnotationBeanPostProcessor.java:660)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:640)
    
Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'journeyFoodServiceImpl' defined in file [C:\Users\TiaaUser\Desktop\AWS\journeyfood\target\classes\org\brahmakumaris\journeyfood\service\JourneyFoodServiceImpl.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'journeyFoodOrderRepository' defined in org.brahmakumaris.journeyfood.repository.JourneyFoodOrderRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Invocation of init method failed; nested exception is java.lang.IllegalStateException: Using named parameters for method public abstract java.util.List org.brahmakumaris.journeyfood.repository.JourneyFoodOrderRepository.getOrdersByDateAndNotDisabled(java.lang.Boolean,java.util.Date) but parameter 'Optional[isRemoved]' not found in annotated query 'SELECT new AggregateJourneyFoodOrder(SUM(head_count), SUM(bread), SUM(achar), SUM(jam) ,SUM(others), SUM(puri), SUM(roti), SUM(thepla))  FROM JourneyFoodOrder WHERE isRemoved=false AND mealRetrievalTime = :mealRetrievalDate'!
    at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:800)
    at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:229)

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'journeyFoodOrderRepository' defined in org.brahmakumaris.journeyfood.repository.JourneyFoodOrderRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Invocation of init method failed; nested exception is java.lang.IllegalStateException: Using named parameters for method public abstract java.util.List org.brahmakumaris.journeyfood.repository.JourneyFoodOrderRepository.getOrdersByDateAndNotDisabled(java.lang.Boolean,java.util.Date) but parameter 'Optional[isRemoved]' not found in annotated query 'SELECT new AggregateJourneyFoodOrder(SUM(head_count), SUM(bread), SUM(achar), SUM(jam) ,SUM(others), SUM(puri), SUM(roti), SUM(thepla))  FROM JourneyFoodOrder WHERE isRemoved=false AND mealRetrievalTime = :mealRetrievalDate'!
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1786)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:602)

Caused by: java.lang.IllegalStateException: Using named parameters for method public abstract java.util.List org.brahmakumaris.journeyfood.repository.JourneyFoodOrderRepository.getOrdersByDateAndNotDisabled(java.lang.Boolean,java.util.Date) but parameter 'Optional[isRemoved]' not found in annotated query 'SELECT new AggregateJourneyFoodOrder(SUM(head_count), SUM(bread), SUM(achar), SUM(jam) ,SUM(others), SUM(puri), SUM(roti), SUM(thepla))  FROM JourneyFoodOrder WHERE isRemoved=false AND mealRetrievalTime = :mealRetrievalDate'!
    at org.springframework.data.jpa.repository.query.JpaQueryMethod.assertParameterNamesInAnnotatedQuery(JpaQueryMethod.java:172)
    at org.springframework.data.jpa.repository.query.JpaQueryMethod.<init>(JpaQueryMethod.java:139)

AggregateJourneyFoodOrder.java

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;
    //Getter Setters and Constructors
}

JourneyFoodOrder.java

@Entity
public class JourneyFoodOrder{
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long orderId;
        
    private Integer headCount;
        
    @DateTimeFormat(pattern = "dd/MM/yyyy")
    private Date dateOfOrderPlaced;
        
    @DateTimeFormat(pattern = "dd/MM/yyyy")
    private Date dateOfDeparture;
        
    @DateTimeFormat(pattern = "dd/MM/yyyy h:mm a")
    private Date mealRetrievalTime;
    
    @ManyToOne
    private UserEntity user; 
        
    private Integer thepla;
        
    private Integer puri;
        
    private Integer roti;
    
    private Integer achar;
        
    private Integer jam;
        
    private Integer bread;
        
    private Integer others;
        
    private Boolean isRemoved;
    //Getters and setters
}

我没有得到我到底做错了什么。还建议是否查询如何对特定日期执行聚合操作。

标签： mysqlspring-bootspring-data-jpajpql

解决方案

我认为这与聚合函数没有任何关系，而是与您尝试将LIKE运算符（和通配符修饰符）应用于不起作用%的类型参数有关。Date

由于我不确定该构造甚至应该意味着什么，因此我不能真正推荐其他替代方法：使用 aString或不同的比较运算符，例如=or >=。

注意：@Modifying注释似乎是错误的，因为这个查询是一个纯选择查询，应该这样执行。

推荐阅读