jpa mysql timestamp,使用TIMESTAMPDIFF和JPA标准查询，并使用hibernate作为提供者

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 TIMESTAMPDIFF(SECOND, setup, released)) as sum_duration FROM calls

The CriteriaBuilder#diff() function clearly does not work as it requires parameters that are Numbers, so I tried using CriteriaBuilder#function:

EntityManager entityManager = emProvider.get();

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

CriteriaQuery query = criteriaBuilder.createQuery();

Root thingyRoot = query.from(Thingy.class);

Path setup = root.get("setup");

Path 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 running this code it threw an exception; it appears that the literal was not rendered 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 the LiteralExpression#render to directly return the string I supply to the method, however that thew this 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 either fix this operation I'm trying to do, or achieve the original goal?

I'm using Hibernate, my database is MySQL.

解决方案

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:

CriteriaBuilder builder = em.getCriteriaBuilder();

CriteriaQuery cq = builder.createQuery( MyEntity.class );

Root root = cq.from( MyEntity.class );

javax.persistence.criteria.Expression timeDiff = builder.function(

"TIMEDIFF",

java.sql.Time.class,

root.get( "endDate" ),

root.get( "startDate" ) );

javax.persistence.criteria.Expression 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.