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

date, documentation, java, jpql

Solution

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();

Problem

``` 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)?

Original source