Search code examples
mysqlsqlspring-bootjpajpql

Get records between two dates using Spring Data JPA

In my DB there is a start date and an end date for a certain booking. I want to get the booking records for a certain start date and an end date. So far I have this in the repo,

List<Booking> findByPickupTimeAfterAndDropoffTimeBefore(Date startDate, Date endDate);

and it returns nothing, but there is a record with dates that I'm using in the query in the DB.

NOTE: I'm using the java.util.Date here(Ignoring the new LocalDateTime package)

What am I doing wrong here?. Is there any other way I can get the data I'm expecting?

Solution

  • You are passing in the same dates to your JPA method as are in the database, which is not expected to return data with those start/end dates, because when the dates are the same, one is not before (or after) the other.

    As per the list of JPA Keywords documentation, After and Before translate to where dateCol > parameter and where dateCol < parameter.

    Change the method to use GreaterThanEqual and LessThanEqual keywords:

    List<Booking> findByPickupTimeGreaterThanEqualAndDropoffTimeLessThanEqual(Date startDate, Date endDate);