JPA Criteria API: Difference between two dates in database?

Question

Simple Question: I have a (MySQL) table with two date columns (from, until) and ask it with

select * from mytable where until > from + interval 4 week;

This is really simple in MySQL.

How could you do that in JPA Query like

cq.where(cb.and(mytable_.get(until),...)

EDIT: Both dates come from the database row, I compare two fields of the database and not one field from application with one from database.

Phares

see #9449503 – Micrometeorology

@MarkS. In all the examples that I have seen, also in your example, is one date fixed from application (param in your link). My question has two dates from database. – Phares

whether you have one date parameter or two dates from fields is irrelevant to the question ... the Criteria API has methods for both ways. What you can't do simply in JPA Criteria is the "+ interval 4 week". – Velasquez

So there is no way to find reservations longer than a certain interval? That seems strange. – Phares

You can use CriteriaBuilder#function() docs.oracle.com/javaee/6/api/javax/persistence/criteria/… although this break compatibility with different dbs, or if you can alter the table structure, you can add a column with the date difference. – Haim

see my answer below for correct JPA Criteria Query. – Agist

Answer 1

Here is the Explanation of Equivalent JPA Criteria Query of

select * from mytable where until > from + interval 4 week;

First you have to create unit expression and extend it from BasicFunctionExpression for which take "WEEK" parameter as a unit and override its rendor(RenderingContext renderingContext) method only.

import java.io.Serializable;
import org.hibernate.query.criteria.internal.CriteriaBuilderImpl;
import org.hibernate.query.criteria.internal.compile.RenderingContext;
import org.hibernate.query.criteria.internal.expression.function.BasicFunctionExpression;

public class UnitExpression extends BasicFunctionExpression<String> implements Serializable {

  public UnitExpression(CriteriaBuilderImpl criteriaBuilder, Class<String> javaType,
      String functionName) {
    super(criteriaBuilder, javaType, functionName);
  }

  @Override
  public String render(RenderingContext renderingContext) {
    return getFunctionName();
  }
}

then you use this unit expression in your JPA criteria Query.

 CriteriaBuilder cb = session.getCriteriaBuilder();
    CriteriaQuery<MyTable> cq = cb.createQuery(MyTable.class);
    Root<MyTable> root = cq.from(MyTable.class);

    Expression<String> week= new UnitExpression(null, String.class, "WEEK");
    Expression<Integer> timeDiff = cb.function(
        "TIMESTAMPDIFF",
        Integer.class,
        week,
        root.<Timestamp>get(MyTable_.until),
        root.<Timestamp>get(MyTable_.from));
    List<Predicate> conditions = new ArrayList<>();
    conditions.add(cb.greaterThan(timeDiff, 4));
    cq.where(conditions.toArray(new Predicate[]{}));
    return session.createQuery(cq);

It is working fine.

Agist

Cool, that looks easy enough. If such a UnitExpression would be part of the libraries, a lot of expressions could be made easier. – Phares

Exactly that would be very helpful. – Agist

Instead of creating an entire class for it, couldn't you just have used Expression<String> week= cb.literal("WEEK"); – Merla

Answer 2

EDIT2: workaround found

Since we have to work only with functions that don't need a built-in parameter (like WEEK), I ended up with

cb.greaterThan(
  cb.diff(
    cb.function("unix_timestamp", Long.class, root.get(Table_.until)),
    cb.function("unix_timestamp", Long.class, root.get(Table_.from))
  )
, 3600L*longerThanHours)

For reference a version that leads to a dead end:

There is no way to make it work like this, you can not send "hour" without surrounding "" as a parameter to the database.

CriteriaBuilder cb = ...;
CriteriaQuery<MyTable> cq = cb.createQuery(MyTable.class);
Root<MyTable> mytable = cq.from(MyTable.class);

cb.greaterThan(
   cb.function(
      "timestampdiff"
      , Integer.class
      , WEEK // <-- this is where JPA gets unable to create SQL
      , mytable.get(MyTable_.from)
      , mytable.get(MyTable_.until)
   )
   , 4
)

Phares

Read my answer. It is possible to pass a "WEEK" parameter in function. – Agist

Answer 3

**A sample example using criteria api to fetch entity between two date ** where date range is give between startDate and endDate :-

public EntityClass getResultEntities(Date startDate, Date endDate){
          CriteriaBuilder builder = em.getCriteriaBuilder();
          CriteriaQuery<EntityClass> criteria = builder.createQuery(EntityClass.class);
          Root<OrderImpl> entityClass = criteria.from(EntityClassImpl.class);
          criteria.select(entityClass);
          criteria.where(builder.between(entityClass.<Date>get("submitDate"), startDate, endDate));
          criteria.orderBy(builder.desc(entityClass.get("submitDate")));
          TypedQuery<EntityClass> query = em.createQuery(criteria);
          return query.getResultList();
    }

Kravits