使用TIMESTAMPDIFF和JPA标准查询,并使用hibernate作为提供者 [英] 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查询: SELECT TIMESTAMPDIFF(SECOND,setup,released))作为一个等价的SQL查询使用CriteriaQuery。 sum_duration FROM calls
CriteriaBuilder#diff()函数显然不起作用,因为它需要的参数是数字,所以我尝试使用 CriteriaBuilder#function :
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<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 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}
所以我尝试匿名覆盖 LiteralExpression#render 直接返回我提供给方法的字符串,但是这个异常。
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?
我使用Hibernate,我的数据库是MySQL。
I'm using Hibernate, my database is MySQL.
推荐答案
我碰到同样的问题: SECOND $ c $
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<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();
这给了我相同的结果。
这篇关于使用TIMESTAMPDIFF和JPA标准查询,并使用hibernate作为提供者的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!