Spring JPA @Query注解使用报错：查询type为income的金额总和失败

错误信息 #

Error creating bean with name 'expenseController': Unsatisfied dependency expressed through field 'expenseService'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'expenseService': Unsatisfied dependency expressed through field 'expenseRepository'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'expenseRepository' defined in com.ivy.expensely.repository.ExpenseRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Invocation of init method failed; nested exception is org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.Optional com.ivy.expensely.repository.ExpenseRepository.findByType(java.lang.String); Reason: Validation failed for query for method public abstract java.util.Optional com.ivy.expensely.repository.ExpenseRepository.findByType(java.lang.String)!; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.Optional com.ivy.expensely.repository.ExpenseRepository.findByType(java.lang.String)!

问题代码 #

ExpenseRepository #

import com.ivy.expensely.model.Expense;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Optional;

@Repository
public interface ExpenseRepository extends JpaRepository<Expense,Long> {

//    @Query(value = "select sum(amount) from expense where type:income",nativeQuery = true)
@Query("SELECT sum(amount) FROM expense WHERE type='income'")
    public Optional<Expense> findByType(String param_name);
}

Expense实体类 #

@NoArgsConstructor
@AllArgsConstructor
@Entity
@Data
@Table(name="expense")
public class Expense {

    @Id
    private Long id;

    private Instant expensedate;

    private String description;

    private String location;

    private Long amount;

    private String type;

    @ManyToOne
    private Category category;

    @JsonIgnore
    @ManyToOne
    private User user;

}

错误原因 #

1. JPQL语法错误：JPQL查询必须使用实体类名而非数据库表名，你写的expense是表名，对应的实体类名是Expense（区分大小写）。
2. 返回类型不匹配：查询的sum(amount)是Long类型数值，但方法返回Optional<Expense>，类型完全不匹配。
3. 方法名与逻辑冲突：方法名findByType会触发Spring Data JPA的方法名自动解析逻辑，与你自定义的求和查询矛盾，导致校验失败。
4. 参数未正确绑定：方法接收了String参数，但查询中硬编码type='income'，既未使用参数，也不符合通用查询需求。

修正后的代码 #

ExpenseRepository #

import com.ivy.expensely.model.Expense;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Optional;

@Repository
public interface ExpenseRepository extends JpaRepository<Expense, Long> {

    // JPQL写法（推荐，面向实体）
    @Query("SELECT SUM(e.amount) FROM Expense e WHERE e.type = :type")
    Optional<Long> getTotalAmountByType(String type);

    // 可选：原生SQL写法（直接操作数据库表）
    // @Query(value = "SELECT SUM(amount) FROM expense WHERE type = :type", nativeQuery = true)
    // Optional<Long> getTotalAmountByType(String type);
}

修正说明 #

• 将JPQL中的表名expense改为实体类名Expense，添加别名e后通过e.属性名引用实体字段。
• 返回类型改为Optional<Long>，匹配sum(amount)的数值类型，同时用Optional处理无匹配记录时的空值情况。
• 方法名改为getTotalAmountByType，明确表达查询总和的逻辑，避免Spring Data JPA自动解析方法名时的冲突。
• 使用:type绑定方法参数，替换硬编码的'income'，让方法支持传入任意type值查询，增强通用性。

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