登录
当前位置： 首页 > 知识库问答 >
问题：

即使在直接SQL查询工作时，JPQL throws也无法确定搜索的case语句的数据类型

左康安
2023-03-14

我正试图通过以下方式更新一个表：

entityManager.createQuery(
        "UPDATE TestCaseExecutionDetailsEntity " +
        " SET status = " +
        " CASE WHEN (runOrder < :runOrder) THEN :completedStatus " +
        "      WHEN (runOrder = :runOrder) THEN :failedStatus " +
        "      WHEN (runOrder > :runOrder) THEN :abortedStatus " +
        " END " +
        " WHERE testExecutionJobId = :jobId")
    .setParameter("jobId", jobId)
    .setParameter("runOrder", runOrder)
    .setParameter("completedStatus", completed.name())
    .setParameter("failedStatus", failed.name())
    .setParameter("abortedStatus", aborted.name())
    .executeUpdate();

对于值jobid=1037和runorder=2，它抛出错误：

UPDATE TEST_CASE_EXECUTION_DETAILS tcxd
SET tcxd.STATUS =
CASE WHEN (tcxd.RUN_ORDER < 2) THEN 'completed'
WHEN (tcxd.RUN_ORDER = 2) THEN 'failed'
WHEN (tcxd.RUN_ORDER > 2) THEN 'aborted'
END
WHERE TEST_EXECUTION_JOB_ID = 1037;

java.lang.IllegalArgumentException: org.hibernate.QueryException: Could not determine data type for searched case statement [update com.pkg.dataaccess.model.TestCaseExecutionDetailsEntity tcede  set tcede.status =  CASE WHEN (tcede.runOrder < :runOrder) THEN :completedStatus       WHEN (tcede.runOrder = :runOrder) THEN :failedStatus       WHEN (tcede.runOrder > :runOrder) THEN :abortedStatus  END  WHERE tcede.testExecutionJobId = :jobId]
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1679) ~[hibernate-entitymanager-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602) ~[hibernate-entitymanager-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1608) ~[hibernate-entitymanager-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:294) ~[hibernate-entitymanager-5.1.0.Final.jar:5.1.0.Final]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_121]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_121]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_121]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_121]
        at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:347) ~[spring-orm-4.3.10.RELEASE.jar:4.3.10.RELEASE]
        at com.sun.proxy.$Proxy116.createQuery(Unknown Source) ~[?:?]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_121]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_121]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_121]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_121]
        at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:298) ~[spring-orm-4.3.10.RELEASE.jar:4.3.10.RELEASE]
        at com.sun.proxy.$Proxy80.createQuery(Unknown Source) ~[?:?]
        at com.pkg.dataaccess.repository.TestDataAccessorRepository.updateCaseDetailsForFailedJob(TestRunsDataAccessorRepository.java:542) ~[classes/:?]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_121]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_121]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_121]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_121]
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333) ~[spring-aop-4.3.10.RELEASE.jar:4.3.10.RELEASE]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) ~[spring-aop-4.3.10.RELEASE.jar:4.3.10.RELEASE]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.3.10.RELEASE.jar:4.3.10.RELEASE]
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) ~[spring-aop-4.3.10.RELEASE.jar:4.3.10.RELEASE]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.10.RELEASE.jar:4.3.10.RELEASE]
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) ~[spring-aop-4.3.10.RELEASE.jar:4.3.10.RELEASE]
        at com.sun.proxy.$Proxy83.updateCaseDetailsForFailedJob(Unknown Source) ~[?:?]
        at com.pkg.rest.controllers.EmailController.get(EmailController.java:51) ~[classes/:?]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_121]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_121]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_121]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_121]
        at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81) ~[jersey-server-2.25.jar:?]
        at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144) ~[jersey-server-2.25.jar:?]
        at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:161) ~[jersey-server-2.25.jar:?]
        at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:160) ~[jersey-server-2.25.jar:?]
        at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:99) ~[jersey-server-2.25.jar:?]
        at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389) ~[jersey-server-2.25.jar:?]
        at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347) ~[jersey-server-2.25.jar:?]
        at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102) ~[jersey-server-2.25.jar:?]
        at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:326) [jersey-server-2.25.jar:?]
        at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271) [jersey-common-2.25.jar:?]
        at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267) [jersey-common-2.25.jar:?]
        at org.glassfish.jersey.internal.Errors.process(Errors.java:315) [jersey-common-2.25.jar:?]
        at org.glassfish.jersey.internal.Errors.process(Errors.java:297) [jersey-common-2.25.jar:?]
        at org.glassfish.jersey.internal.Errors.process(Errors.java:267) [jersey-common-2.25.jar:?]
        at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317) [jersey-common-2.25.jar:?]
        at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:305) [jersey-server-2.25.jar:?]
        at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1154) [jersey-server-2.25.jar:?]
        at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:473) [jersey-container-servlet-core-2.25.jar:?]
        at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:427) [jersey-container-servlet-core-2.25.jar:?]
        at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:388) [jersey-container-servlet-core-2.25.jar:?]
        at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:341) [jersey-container-servlet-core-2.25.jar:?]
        at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:228) [jersey-container-servlet-core-2.25.jar:?]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292) [catalina.jar:8.0.41]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) [catalina.jar:8.0.41]
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) [tomcat-websocket.jar:8.0.41]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240) [catalina.jar:8.0.41]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) [catalina.jar:8.0.41]
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212) [catalina.jar:8.0.41]
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94) [catalina.jar:8.0.41]
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504) [catalina.jar:8.0.41]
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141) [catalina.jar:8.0.41]
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) [catalina.jar:8.0.41]
        at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620) [catalina.jar:8.0.41]
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) [catalina.jar:8.0.41]
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:509) [catalina.jar:8.0.41]
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1104) [tomcat-coyote.jar:8.0.41]
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684) [tomcat-coyote.jar:8.0.41]
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1524) [tomcat-coyote.jar:8.0.41]
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1480) [tomcat-coyote.jar:8.0.41]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_121]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_121]
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-util.jar:8.0.41]
        at java.lang.Thread.run(Thread.java:745) [?:1.8.0_121]
Caused by: org.hibernate.QueryException: Could not determine data type for searched case statement [update com.pkg.dataaccess.model.TestCaseExecutionDetailsEntity tcede  set tcede.status =  CASE WHEN (tcede.runOrder < :runOrder) THEN :completedStatus       WHEN (tcede.runOrder = :runOrder) THEN :failedStatus       WHEN (tcede.runOrder > :runOrder) THEN :abortedStatus  END  WHERE tcede.testExecutionJobId = :jobId]
        at org.hibernate.QueryException.generateQueryException(QueryException.java:120) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:218) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:76) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:302) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:240) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1907) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291) ~[hibernate-entitymanager-5.1.0.Final.jar:5.1.0.Final]
        ... 72 more
Caused by: org.hibernate.QueryException: Could not determine data type for searched case statement
        at org.hibernate.hql.internal.ast.tree.SearchedCaseNode.getDataType(SearchedCaseNode.java:62) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.hql.internal.ast.tree.BinaryLogicOperatorNode.extractDataType(BinaryLogicOperatorNode.java:239) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.hql.internal.ast.tree.BinaryLogicOperatorNode.initialize(BinaryLogicOperatorNode.java:49) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.hql.internal.ast.HqlSqlWalker.prepareLogicOperator(HqlSqlWalker.java:1369) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.hql.internal.ast.HqlSqlWalker.evaluateAssignment(HqlSqlWalker.java:1270) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.assignment(HqlSqlBaseWalker.java:1063) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.setClause(HqlSqlBaseWalker.java:763) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.updateStatement(HqlSqlBaseWalker.java:379) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:267) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:262) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:190) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:76) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:302) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:240) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1907) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291) ~[hibernate-entitymanager-5.1.0.Final.jar:5.1.0.Final]
        ... 72 more

共有1个答案

琴英华
2023-03-14

我在下面的查询中遇到了同样的问题。我只需在case语句中强制转换变量即可：

SELECT SUM(
    CASE WHEN type = 'SOME_TYPE' THEN CAST(price AS float) ELSE CAST (-price AS FLOAT) END
)
FROM MyEntity;
 类似资料：
  • Querydsl-无法确定搜索的case语句的数据类型

    这个问题在几年前就有了答案（标题类似），但我正在努力解决这个问题。似乎无论我在then（）谓词中放了什么表达式，我都会得到一个Hibernate错误： 无法确定搜索case语句的数据类型

  • mysql case语句无法正常工作

    我知道这不是一个应该在这里问的问题。请帮我解决这个问题。 我试图在下面查询中使用case语句，但是我找不到我在其中所做的错误。请你帮帮我.. 我得到的错误如下 SQL]选择tsc_accessory_details_for_repaired_and_qc_pass_phone_list.ccord(tsc_accessory_details_for_repaired_and_qc_pass_pho

  • 使用CASE语句的MS Access查询

    问题内容： 我只想获取一个表“ b”的值，如果表“ a”的值是“-”如果表“ b”的值是空的，那么即使它是“-”，也要获取表“ a”的值 Microsoft Access对以下查询说“ Missing operator”： 我认为该错误是在CASE表达式行上。 问题答案： MS Access不支持语句。用途： 我不确定Access是否支持别名，但是它可能支持 注意（尽管标签正确），问题的标题可能会

  • 执行COPY..TO语句时，无法确定参数$1的数据类型

    我想使用JDBC prepared语句将一个表的内容从数据库写入CSV文件。我正在使用的PSQL查询是： 我的代码如下： 它不能与copy..to语句一起工作的原因是什么？

  • 使用选择语句的SQL连接查询

    问题内容： 我有一个oc_category_description表，其中的列是： category_id 姓名 和其他表oc_category，其中的列是： category_id 图像 parent_id 这是oc_category_description表的示例图片 oc_category表 在这里，我想显示名称，category_id，图像，parent_id，其中oc_category

  • mySQL：如何在SELECT查询的CASE语句中使用PDO绑定参数？

    在mySQL查询中，我希望根据WHERE子句中PDO绑定的搜索参数条件选择客户机表的行。类似于 有两种情况：A）搜索参数包含字符串或B）搜索参数为空 在A的情况下，param包含例如一个客户端号'123'。 因此，我使用:，如果客户机在表中，则查询返回客户机123。 在B的情况下，字符串为空。因此我没有使用WHERE子句,查询只是返回表中的所有客户端 我当然可以写一个IF..SELECT查询之外的

相关问答

PHP搜索不工作与CASE函数用CASE语句检查mysql查询中的存在性java.nio.file即使在Eclipse中使用JDK1.7也无法工作即使SQL语句失败，也强制迁移SQL/MyBATIS，由上一个case语句生成的查询列值

相关文章

即时搜索PB级数据SQL Case语句在where子句中指定条件？转换为VBA时SELECT查询不起作用-无效的SQL语句SQL语句-SELECT此查询的逆函数使用数组作为switch中的case语句

相关阅读

SQL连接查询MySQL 常见数据库优化sql语句MySQL的基础架构以及一条查询sql语句的执行流程SQL DELETE语句SQL Update语句

相关工具

Redis数据查看工具联系人搜索算法丁丁主数据查重工具php 实现数据库连接池、直播平台团购搜索

相关文档

SQL Server 数据库教程语言本能：人类语言进化的奥秘数据挖掘算法鲜活的数据数据可视化指南工作流程
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.