我有一个包含列设置和释放的数据表,两者都有时间戳 . 我的目标是使用CriteriaQuery在下面创建一个等价的SQL查询 .
SQL查询: SELECT TIMESTAMPDIFF(SECOND, setup, released)) as sum_duration FROM calls
CriteriaBuilder#diff()函数显然不起作用,因为它需要参数是Numbers,所以我尝试使用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();
但是,当我尝试运行此代码时,它抛出异常;似乎文字不是作为常量呈现,而是作为参数呈现:
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直接返回我提供给该方法的字符串,但是这个例外 .
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}
所以问题是:我如何修复我正在尝试的操作,或者实现最初的目标?
我正在使用Hibernate,我的数据库是MySQL .
3 回答
我碰到了同样的问题:
SECOND
将被撇号包围,查询将抛出异常 .我通过以下代码解决了这个问题:
这给了我相同的结果 .
这是对等效JPA标准查询的解释
首先,您必须创建单位表达式并从
BasicFunctionExpression
扩展它,以"SECOND"参数为单位,并仅覆盖其rendor(RenderingContext renderingContext)
方法 .然后在JPA条件查询中使用此单位表达式 .
这是工作 .
用案例陈述嘲笑它 .