Using TIMESTAMPDIFF by querying JPA criteria and hibernation as a provider

Question

I have a data table with settings and column output, both of which contain timestamps. My goal is to create the equivalent of the SQL query below using CriteriaQuery.

SQL query: SELECT TIMESTAMPDIFF(SECOND, setup, released)) as sum_duration FROM calls

The CriteriaBuilder # diff () function obviously does not work, since it requires parameters that are numbers, so I tried using the CriteriaBuilder # Function:

 EntityManager entityManager = emProvider.get(); CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Integer> query = criteriaBuilder.createQuery(); Root<Thingy> thingyRoot = query.from(Thingy.class); Path<DateTime> setup = root.get("setup"); Path<DateTime> release = root.get("release"); Expression secondLiteral = criteriaBuilder.literal("SECOND"); Expression func = criteriaBuilder.function("TIMESTAMPDIFF", Integer.class, secondLiteral, setup, release); entityManager.createQuery(query).getResultList(); 

However, when I tried to run this code, it made an exception; it seems that the literal was not displayed as a constant, but rather as a parameter:

 java.lang.IllegalStateException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode \-[METHOD_CALL] MethodNode: '(' +-[METHOD_NAME] IdentNode: 'TIMESTAMPDIFF' {originalText=TIMESTAMPDIFF} \-[EXPR_LIST] SqlNode: 'exprList' +-[NAMED_PARAM] ParameterNode: '?' {name=param0, expectedType=null} +-[DOT] DotNode: 'cdr0_.setup' {propertyName=setup,dereferenceType=ALL,propertyPath=setup,path=generatedAlias0.setup,tableAlias=cdr0_,className=com.vtsl.domain.CDR,classAlias=generatedAlias0} | +-[ALIAS_REF] IdentNode: 'cdr0_.id' {alias=generatedAlias0, className=com.vtsl.domain.CDR, tableAlias=cdr0_} | \-[IDENT] IdentNode: 'setup' {originalText=setup} \-[DOT] DotNode: 'cdr0_.release' {propertyName=release,dereferenceType=ALL,propertyPath=release,path=generatedAlias0.release,tableAlias=cdr0_,className=com.vtsl.domain.CDR,classAlias=generatedAlias0} +-[ALIAS_REF] IdentNode: 'cdr0_.id' {alias=generatedAlias0, className=com.vtsl.domain.CDR, tableAlias=cdr0_} \-[IDENT] IdentNode: 'release' {originalText=release} 

So, I tried anonymously overriding LiteralExpression # render to directly return the string that I am providing to the method, however this is an exception.

 java.lang.IllegalStateException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode \-[METHOD_CALL] MethodNode: '(' +-[METHOD_NAME] IdentNode: 'TIMESTAMPDIFF' {originalText=TIMESTAMPDIFF} \-[EXPR_LIST] SqlNode: 'exprList' +-[IDENT] IdentNode: 'SECOND' {originalText=SECOND} +-[DOT] DotNode: 'cdr0_.setup' {propertyName=setup,dereferenceType=ALL,propertyPath=setup,path=generatedAlias0.setup,tableAlias=cdr0_,className=com.vtsl.domain.CDR,classAlias=generatedAlias0} | +-[ALIAS_REF] IdentNode: 'cdr0_.id' {alias=generatedAlias0, className=com.vtsl.domain.CDR, tableAlias=cdr0_} | \-[IDENT] IdentNode: 'setup' {originalText=setup} \-[DOT] DotNode: 'cdr0_.release' {propertyName=release,dereferenceType=ALL,propertyPath=release,path=generatedAlias0.release,tableAlias=cdr0_,className=com.vtsl.domain.CDR,classAlias=generatedAlias0} +-[ALIAS_REF] IdentNode: 'cdr0_.id' {alias=generatedAlias0, className=com.vtsl.domain.CDR, tableAlias=cdr0_} \-[IDENT] IdentNode: 'release' {originalText=release} 

So the question is: how can I fix this operation that I am trying to do, or achieve the original goal?

I use Hibernate, my database is MySQL.

Answer 1

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

I worked on it with the following code:

 CriteriaBuilder builder = 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 that gives me the same result.