Using Timestampdiff With Jpa Criteria Query And Hibernate As The Provider

I have a data table with columns setup and release, both of which hold timestamps. My goal is to create an equivalent of the SQL query below using CriteriaQuery. SQL Query:SELECT T

Solution 1:

Here is explanation of equivalent JPA Criteria Query of

SELECT * from calls where TIMESTAMPDIFF(SECOND, setup, released) < 3600;

First you have to create unit expression and extend it from BasicFunctionExpression for which take "SECOND" 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;

publicclassUnitExpressionextendsBasicFunctionExpression<String> implementsSerializable {

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

  @OverridepublicStringrender(RenderingContext renderingContext) {
    returngetFunctionName();
  }
}

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

EntityManager entityManager = emProvider.get();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
Root<Calls> thingyRoot = query.from(Calls.class);

    Expression<String> second = new UnitExpression(null, String.class, "SECOND");
    Expression<Integer> timeInSec = cb.function(
        "TIMESTAMPDIFF",
        Integer.class,
        second ,
        root.<Timestamp>get("setup"),
        root.<Timestamp>get("release"));
    List<Predicate> conditions = new ArrayList<>();
    conditions.add(cb.lessThan(timeInSec, 3600));
    cq.where(conditions.toArray(new Predicate[]{}));
    return session.createQuery(cq);

It is working.

Solution 2:

I bumped into the same problem: the SECOND will be surrounded by apostrophes and the query will throw an exception.

I worked around it by the following code:

CriteriaBuilderbuilder= em.getCriteriaBuilder();
CriteriaQuery<MyEntity> cq = builder.createQuery( MyEntity.class );
Root<MyEntity> root = cq.from( MyEntity.class );

javax.persistence.criteria.Expression<java.sql.Time> timeDiff = builder.function(
            "TIMEDIFF",
            java.sql.Time.class,
            root.<Date>get( "endDate" ),
            root.<Date>get( "startDate" ) );
javax.persistence.criteria.Expression<Integer> timeToSec = builder.function(
            "TIME_TO_SEC",
            Integer.class,
            timeDiff );

//lessThanOrEqualTo 60 minutes
cq.where( builder.lessThanOrEqualTo( timeToSec, 3600 ) );

return em.createQuery( cq ).getResultList();

And this gives me the same result.

Solution 3:

CASEWHEN1=1THEN TIMESTAMPDIFF(SECOND,startDatetime,endDatetime) ELSE0END

Mock it with case statement.

Solution 4:

Here's a slightly more general solution than Kalid Shah's.

Basically, what we need is a custom "opaque literal" Expression class that is capable of passing through arbitrary token expressions like MONTH, YEAR, INTERVAL 1 DAY, etc., to MySQL as function arguments:

import javax.persistence.criteria.CriteriaBuilder;

import org.hibernate.query.criteria.internal.CriteriaBuilderImpl;
import org.hibernate.query.criteria.internal.compile.RenderingContext;
import org.hibernate.query.criteria.internal.expression.LiteralExpression;

/**
 * Represents an opaque literal that gets pass through JPA unaltered into SQL.
 */@SuppressWarnings("serial")publicclassOpaqueLiteralExpressionextendsLiteralExpression<Void> {

    privatefinal String value;

    publicOpaqueLiteralExpression(CriteriaBuilderImpl builder, String value) {
        super(builder, Void.class, null);
        if (value == null)
            thrownewIllegalArgumentException("null value");
        this.value = value;
    }

// ExpressionImpl@Overridepublic String render(RenderingContext renderingContext) {
        returnthis.value;
    }
}

Then you can use it like this:

final Expression<LocalDate> birthDate = student.get(Student_.birthDate);
final Expression<Integer> age = builder.function("TIMESTAMPDIFF", Integer.class,
  newOpaqueLiteralExpression(builder, "YEAR"), birthDate, LocalDate.now());