JPA - Returning entities that are After StartDate and Before EndDate

Question

I have two dates in my entity. ie.

Date startDate; Date endDate; 

How do I query so that given a date, it will return all entities where the specified date lies between startDate and endDate?

I already tried the following:

findByStartDateAfterAndEndDateBefore(Date givenDate); 

And Spring-Data-JPA didn't like this and running into errors. There is no specific error and the repo just can't be injected to my class.

What is the correct way? I know this can be done easily wqith Hibernate criteria or with Native SQL but trying to do that in Spring JPA.

Is this a problem with the query itself or some sort of incompatibility between the Date types Spring uses?

Tried findByStartDateAfterAndEndDateBefore(Date givenDate, Date givenDate) and that returns null however.

Answer 1

You can't use only one parameter because of Spring Data restrictions, but you can workaround it using code like this:

List<AnEntity> findByStartDateBeforeAndEndDateAfter(Date startDate, Date endDate);  default List<AnEntity> findByStartDateBeforeAndEndDateAfter(Date givenDate) {     return findByStartDateBeforeAndEndDateAfter(givenDate, givenDate); } 

This code should cover your needs. I also verified it with Spring Boot 1.5.9. using spring-data-get-started example.

Answer 2

To my surprise LessThan and GreaterThan working and Before and After failing badly.

I never thought I can use less than and greater than for dates and always look at date related functions like between, before, after.

That's mostly because of the documentation example

After  findByStartDateAfter  … where x.startDate > ?1  Before  findByStartDateBefore  … where x.startDate < ?1 

So I looked more close into docs of Spring Data JPA and found something interesting with the below example

 LocalDate date = new LocalDate().minusYears(2);  return builder.lessThan(root.get(_Customer.createdAt), date); 

So while comparing datatime the authors using the criteria lessthan for time property.

So given a shot with less than and worked and again gave a shot with greater than aswell and later together. So I came up with a conclusion

public MyDateEntity findByStartDateLessThanAndEndDateGreaterThan(Date sDate, Date eDate);  

And this is working so far. And I believe, there must be a clean way to handle dates probably with before,after,between but I just can't figure that out.

Would be great if someone figure that out.