自定义Spring JPA日期属性查询：指定月份WorkedDay查询问题

Hey Gustavo, I’ve been in your exact spot before—Spring Data JPA’s querying can feel a bit opaque at first, but let’s break down how to get your monthly WorkedDay list sorted out smoothly. Here are two straightforward approaches tailored to your PostgreSQL setup:

1. Use Spring Data JPA’s Query Method Naming Convention #

This is the simplest route if you want to avoid writing raw SQL/JPQL. Spring Data automatically generates the correct query based on your method name.

Critical Tip: Always include the year parameter! #

Filtering only by month will pull data from all years that have that month. Adding the year ensures you target the exact period you need for your reports.

Define this method in your WorkedDayRepository:

import org.springframework.data.repository.CrudRepository;
import java.util.List;

public interface WorkedDayRepository extends CrudRepository<WorkedDay, Long> {
    // Fetch all WorkedDay entries for a specific year and month
    List<WorkedDay> findByWeekDayYearAndWeekDayMonth(int year, int month);
}

This works because Spring Data recognizes Year and Month as valid suffixes for date fields (assuming your weekDay property is a LocalDate or java.util.Date mapped correctly to PostgreSQL’s DATE column).

2. Use @Query with JPQL or Native PostgreSQL SQL #

If you want more control over the query (or need to handle edge cases), the @Query annotation gives you full flexibility.

Option A: JPQL with Database Functions #

JPQL lets you tap into database-specific functions via FUNCTION():

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import java.util.List;

public interface WorkedDayRepository extends CrudRepository<WorkedDay, Long> {
    @Query("SELECT w FROM WorkedDay w WHERE FUNCTION('YEAR', w.weekDay) = :year AND FUNCTION('MONTH', w.weekDay) = :month")
    List<WorkedDay> findByMonthAndYear(@Param("year") int year, @Param("month") int month);
}

Option B: Native PostgreSQL Query #

Since you’re using PostgreSQL, leverage its native EXTRACT() function for precise date filtering:

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import java.util.List;

public interface WorkedDayRepository extends CrudRepository<WorkedDay, Long> {
    @Query(value = "SELECT * FROM worked_day WHERE EXTRACT(YEAR FROM week_day) = :year AND EXTRACT(MONTH FROM week_day) = :month", nativeQuery = true)
    List<WorkedDay> findByMonthAndYear(@Param("year") int year, @Param("month") int month);
}

Quick Entity Mapping Check #

Double-check your WorkedDay entity maps the weekDay field correctly to your PostgreSQL table:

import jakarta.persistence.*;
import java.time.LocalDate;

@Entity
@Table(name = "worked_day")
public class WorkedDay {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "week_day")
    private LocalDate weekDay;
    
    // Other fields, getters, setters
}

Start with the query method naming if you want minimal code, and switch to @Query if you need more customization. Either way, you’ll have the monthly WorkedDay list you need for your reporting service!

内容的提问来源于stack exchange，提问作者Gustavo Correa