将TIMESTAMPDIFF与JPA条件查询一起使用并休眠为提供者

Z时代
2024-01-10
分类：问答

我有一个带有设置和发布列的数据表，这两个表都保留时间戳。我的目标是使用CriteriaQuery在下面创建等效的SQL查询。

SQL查询：SELECT TIMESTAMPDIFF(SECOND, setup, released)) as sum_duration FROM

calls

该CriteriaBuilder＃DIFF（）

，因为它需要包含数字参数功能显然是不行的，所以我尝试使用CriteriaBuilder＃功能：

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。

回答：

我碰到了同样的问题：SECOND会被撇号包围，查询将引发异常。

我通过以下代码解决了这个问题：

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();