JPA query to check if a record exists for a specific year-month?

Question

I am trying to implement a JPA Query to check if there is any record with date timestamp exists which matches with current year-month. Right now I am fetching all the records and doing iteration to match. I know it's not right implementation, just wondering if is there any inbuilt JPA query available for this scenario.

This is my implementation until now

     List<Product> productList= productRepository.findAll();

        /*getMonthInt() and getYearInt() is the custom function which returns current 
        month and year. As date.getMonth() and date.getYear() is deprecated
        */

        int currentMonth=getMonthInt(new Date());
        int currentYear=getYearInt(new Date());

        for(Product product:productList){

          int fetchedMonth=getMonthInt(product.getShipmentDate()) ;
          int fetchedYear=getYearInt(product.getShipmentDate());

          if(fetchedMonth == currentMonth && fetchedYear == currentYear){
             //record exist 
          }
          else{
         //do something else
          }
     }

Answer 1

You don't need to fetch all of the records. If you are trying to filter the record by just comparing the MONTH and YEAR of the timestamp follow the following steps

APPROACH-1:

• construct startDate(first day of the month of that year)
• construct endDate (last day of the month of that year)
• use between keyword and construct a jpa query like following

APPROACH-2:

• construct a jpa query using @Query annotation like following

Your ProductRepository should be like following

public interface ProductRepository extends JpaRepository<Product, Integer> {

    List<Product> findAllByShipmentDateBetween(Date startDate, Date endDate);

    @Query("select p from Product p where year(p.shipmentDate) = ?1 and month(p.shipmentDate) = ?2")
    List<Product> findAllByShipmentDate(Integer year, Integer month);
}

By default, spring data jpa uses position-based parameter binding as shown in the 2nd query method which may cause issue during maintenace. More maintainable code can be written using the named parameters. For example

@Query("select p from Product p where year(p.shipmentDate) = :year and month(p.shipmentDate) = :month")
List<Product> findAllByShipmentDate(@Param("year") Integer year, @Param("month") Integer month);