What is the best way to query a JPA object for instances based on current date + time?

Question

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 (extends CrudRepository) repository for this entity type, and there I need a query method for all Thingthat are "active", where "active" is defined as

startDate <= now <= endDate

I tried such a JPQL query, but did not expect 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 calling a method name that gives the expected results, but this is inconvenient:

public Iterable<Thing> findByStartDateBeforeAndEndDateAfter(DateTime minStartDate, DateTime maxEndDate);

I can wrap it in my @Servicelike

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 inconvenient (in my opinion).

JPA, , JPQL? , Query ( , ), , .

Answer 1

:

@Query("select t from Thing t where t.startDate <= :time and t.endDate >= :time")
public Iterable<Thing> findActiveThings(@Param("time") DateTime time);

, , : " 24 2011 "

Answer 2

, - :

@Query("SELECT t FROM Thing t WHERE t.startDate <= :maxStartDate AND :minEndDate <= t.endDate")
public Iterable<Thing> findAllActive();

DateTime maxStartDate DateTime minEndDate, , , 2015-01-01 00:00:00 2015-01-01 23:59:59

DateTime maxStartDate= new DateTime().withTime(23, 59, 59, 0);
DateTime minEndDate = new DateTime().withTimeAtStartOfDay();