如何通过年月参数查询JPA实体的LocalDate字段？

我有一个包含LocalDate类型transactionDate字段的Revenue实体：

public class Revenue {

... (其他字段已省略)

  @Column(name = "transaction_date")
  private LocalDate transactionDate;
}

我希望检索某一年份指定月份的所有收入记录，URI设计为：/revenue/{year}/{month}。请问能否直接通过JPA查询方法实现该需求？

我尝试了如下实现：

@Repository
public interface RevenueRepository extends JpaRepository<Revenue, Long> {

  @Query(
      "SELECT r FROM revenue WHERE YEAR(r.transactionDate) = :year AND MONTH(r.transactionDate) = :month")
  Page<Revenue> findByYearAndMonth(
      @Param("year") int year, @Param("month") int month, Pageable pageable);
}

// Service接口
public interface RevenueService {
  Page<RevenueDTOResponse> getByYearAndMonth(int year, int month, Pageable pageable);
}

// Service实现类
@Service
public class RevenueServiceImpl implements RevenueService {

  @Autowired private RevenueRepository revenueRepository;

  @Override
  public Page<RevenueDTOResponse> getByYearAndMonth(int year, int month, Pageable pageable) {
    return revenueRepository.findByYearAndMonth(year, month, pageable).map(RevenueDTOResponse::new);
  }
}

// Controller方法
@GetMapping("/{year}/{month}")
public ResponseEntity<Page<RevenueDTOResponse>> getByYearAndMonth(
    @PathVariable int year, int month, Pageable pageable) {
  return new ResponseEntity<>(
      revenueService.getByYearAndMonth(year, month, pageable), HttpStatus.OK);
}

但遇到了如下异常：

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'revenueController': Unsatisfied dependency expressed through field 'revenueService': Error creating bean with name 'revenueServiceImpl': Unsatisfied dependency expressed through field 'revenueRepository': Error creating bean with name 'revenueRepository' defined in budget.control.project.repository.RevenueRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Could not create query for public abstract org.springframework.data.domain.Page budget.control.project.repository.RevenueRepository.findByYearAndMonth(int,int,org.springframework.data.domain.Pageable); Reason: Validation failed for query for method public abstract org.springframework.data.domain.Page budget.control.project.repository.RevenueRepository.findByYearAndMonth(int,int,org.springframework.data.domain.Pageable)
        at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.resolveFieldValue(AutowiredAnnotationBeanPostProcessor.java:787) ~[spring-beans-6.1.6.jar:6.1.6]
        ...
    Caused by: org.hibernate.query.sqm.UnknownEntityException: Could not resolve root entity 'revenue'
        ...

1. 修复JPQL查询语句错误 #

异常提示Could not resolve root entity 'revenue'，核心问题是JPQL中引用实体时必须使用实体类的名称（而非数据库表名）。你的实体类是Revenue，因此JPQL里需要写成FROM Revenue r而不是FROM revenue。

修改后的Repository方法：

@Repository
public interface RevenueRepository extends JpaRepository<Revenue, Long> {

  @Query(
      "SELECT r FROM Revenue r WHERE YEAR(r.transactionDate) = :year AND MONTH(r.transactionDate) = :month")
  Page<Revenue> findByYearAndMonth(
      @Param("year") int year, @Param("month") int month, Pageable pageable);
}

2. 使用Spring Data JPA派生查询（无需手动写JPQL） #

Spring Data JPA支持通过方法名称自动生成查询，你可以直接定义派生查询方法，省去编写@Query的麻烦：

@Repository
public interface RevenueRepository extends JpaRepository<Revenue, Long> {

  Page<Revenue> findByTransactionDateYearAndTransactionDateMonth(int year, int month, Pageable pageable);
}

这个方法会自动生成对应的JPQL查询，匹配transactionDate的年份和月份，完全满足需求。

额外注意事项 #

• 确保LocalDate类型的字段在JPA中能被正确解析（Spring Boot 2.x及以上版本默认支持Java 8日期类型，无需额外配置）。
• Controller方法中，@PathVariable需要分别标注每个路径变量，否则可能导致参数绑定错误：

@GetMapping("/{year}/{month}")
public ResponseEntity<Page<RevenueDTOResponse>> getByYearAndMonth(
    @PathVariable int year, @PathVariable int month, Pageable pageable) {
  return new ResponseEntity<>(
      revenueService.getByYearAndMonth(year, month, pageable), HttpStatus.OK);
}

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