Java: JPQL date function to add a time period to another date

Question

SELECT x FROM SomeClass
WHERE x.dateAtt BETWEEN CURRENT_DATE AND (CURRENT_DATE + 1 MONTH)

In the above JPQL statement, SomeClass has a memebr dateAttr, which is a java.util.Date and has a @Temporal(javax.persistence.TemporalType.DATE) annotation.

I need a way to do the (CURRENT_DATE + 1 MONTH) bit - it is obviously wrong in its current state - but cannot find the doc with the date function for JPQL.

Can anyone point me in the direction of a doc that documents JPQL date functions (and also how to do this particular query)?

Answer 1

If you have a date object that is + 1 month already you could do something like this:

public List findEmployees(Date endDate) {
  return entityManager.createQuery(
    "SELECT e from Employee e WHERE e.startDate BETWEEN ?1 AND ?2")
    .setParameter(1,new Date(), TemporalType.DATE)
    .setParameter(2,endDate, TemporalType.DATE).getResultList();
}

This however, requires that the dates be valid before hand.

UPDATE

If you always want the next month, you can use JodaTime which has a great and easy api. You could then modify your query like this:

//Get next month
DateTime dt = new DateTime();
entityManager.createQuery(
"SELECT e from Employee e WHERE e.startDate BETWEEN ?1 AND ?2")
.setParameter(1,new Date(), TemporalType.DATE)
.setParameter(2,dt.plusMonths(1).toDate(), TemporalType.DATE).getResultList();

Answer 2

Or use commons-lang instead of jodatime:

entityManager.createQuery(
    "SELECT e from Employee e WHERE e.startDate BETWEEN ?1 AND ?2"
)
.setParameter(1,new Date(), TemporalType.DATE)
.setParameter(2,DateUtils.addMonths(new Date(), 1), TemporalType.DATE)
.getResultList();

But I know this is not what you are asking and I'm pretty sure it can't be done in JPQL alone, you will either have to pass a parameter or use a native named query

Answer 3

Standard JPQL doesn't support such operations on dates. You will have to either use a native query or to do the computation on the Java side.