What is the best way to query a JPA entity for instances based on the current date+time?
java, jpa, jpql, spring-data, spring-data-jpa
Solution
You could simply have the following:
@Query("select t from Thing t where t.startDate <= :time and t.endDate >= :time")
public Iterable<Thing> findActiveThings(@Param("time") DateTime time);
Which has the advantage of using only 1 parameter , but also allows you to later have queries such as: "Give me the list of active things on January 24th 2011"
Problem
I have an entity type that has two dates, startDate and endDate: ``` import org.joda.time.DateTime; @Entity public class Thing { @Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime") private DateTime startDate; @Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime") private DateTime endtDate; } ``` I have a Spring Data-JPA repository (extends `CrudRepository`) for this entity type, and in there I need a way to query for all `Thing`s that are "active" where "active" is defined as startDate <= now <= endDate I tried a JPQL query like this, but it's not producing the expected results in some of our tests: ``` @Query("select t from Thing t where t.startDate <= CURRENT_TIMESTAMP and t.endDate >= CURRENT_TIMESTAMP") public Iterable<Thing> findAllActive(); ``` I also tried a method-name-convention query, which does produce the results I expect, but it's awkward: ``` public Iterable<Thing> findByStartDateBeforeAndEndDateAfter(DateTime minStartDate, DateTime maxEndDate); ``` I can wrap that in my `@Service` like ``` public Iterable<Thing> getActiveThings() { DateTime now = DateTime.now(); return repository.findByStartDateBeforeAndEndDateAfter(now, now); } ``` So the service interface is clean, but the repo interface is ugly and awkward (in my opinion). Is there a better way to construct this Repository query method using JPA, either a better method-name-based query or an explicit JPQL query? I'd prefer to not have to manually construct the Query object in code (right now I don't even have a concrete class for the Repository, only an interface), but if that's the best way I'll consider it.