JPQL 子查询示例抛出不存在的错误关系"table_name"，Spring 引导

Question

我试图在Spring Boot，JPQL中的查询示例中实现一个Subquery，但得到了错误

2022-03-21 16:13:20.582警告23580-[nio-8080-exec-5]o.h.engine.jdbc.spi.SqlExceptionHelper:SQL错误：0，SQL状态：42P012022-03-21 16:13:20.582错误23580-[nio-8080-exec-5]o.h.engine.jdbc.spi.SqlExceptionHelper:错误：relationship；案例"；不存在职位：30org.springframework.dao.InvalidDataAccessResourceUsageException：无法提取结果集；SQL[n/a]；嵌套异常为org.hubinate.exception.SQLGrammaException：无法提取ResultSet网址：org.springframework.orm.jpa.vender.Hibernate JpaDialect.covertHibernate AccessException(Hibernate jpa Dialect.java:259)网址：org.springframework.om.jpa.vendor。Hibernate JpaDialection.translateExceptionIfPossible(Hibernate jpa Dialection.java:233)位于org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)网址：org.springframework.dao.support.ChainedSistenceExceptionTranslator.translateExceptionIfPossible(ChainedSistenceexceptionTranslator.java:61)网址：org.springframework.dao.support.DataAccessUtils.translateIfNenecessary(DataAccessUtils.java:242)位于org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.ioke(PersistenceExceptionTranslationInterceptor.java:152)网址：org.springframework.aop.framework.ReflectiveMethodInvocation.prough(ReflectiveMethodInvocation.java:186)位于org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.ioke(CrudMethodMetadataPostProcessor.java:145)网址：org.springframework.aop.framework.ReflectiveMethodInvocation.prough(ReflectiveMethodInvocation.java:186)位于org.springframework.aop.intercept.ExposeInvocationInterceptor.ioke(ExposeInvocation interceptor.java:97)网址：org.springframework.aop.framework.ReflectiveMethodInvocation.prough(ReflectiveMethodInvocation.java:186)网址：org.springframework.aop.framework.JdkDynamicAopProxy.invoke网址：com.sun.proxy.$Proxy464.searchCases(未知来源)网址：com.ericsson.smart.meter.smartmeter.service.Impl.CaseServiceImpl.searchCases(CaseServiceImpl.java:125)网址：com.ericson.smart.meter.smartmeter.controller.CaseController.searchCases(CaseController.java:187)在sun.reflect.NativeMethodAccessorImpl.invoke0(本机方法)位于sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)在sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)位于java.lang.reflect.Method.ioke(Method.java:498)位于org.springframework.web.method.support.IInvocableHandlerMethod.doInvoke(InvocableHandler method.java:205)位于org.springframework.web.method.support.InvocaleHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)位于org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)位于org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)网址：org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandledAdapter.java:808)网址：org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethod Adapter.java:87)位于org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java：1067)网址：org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java：963)网址：org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java：1006)网址：org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java：909)位于javax.servlet.httpHttpServlet.service(HttpServlet.java：681)网址：org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java：883)位于javax.servlet.httpHttpServlet.service(HttpServlet.java：764)网址：org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)网址：org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)网址：org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)网址：org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)网址：org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)网址：org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)网址：org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequest filter.java:117)网址：org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)网址：org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)网址：org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)网址：org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequest filter.java:117)网址：org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)网址：org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)网址：org.springframework.boot.activate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96)网址：org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequest filter.java:117)网址：org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)网址：org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)网址：org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncoding filter.java:201)网址：org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequest filter.java:117)网址：org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)网址：org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)网址：org.apache.catalina.core.StandardWrapperValv.invoke(StandardWrapperValve.java:197)网址：org.apache.catalina.core.StandardContextValv.invoke(StandardContextValv.java:97)网址：org.apache.catalina.authenticator.AuthenticatorBase.ioke(AuthenticatorBase.java:540)网址：org.apache.catalina.core.StandardHostValv.invoke(StandardHostValv.java:135)网址：org.apache.catalina.vals.ErrorReportValve.ioke(ErrorReportValve.java:92)网址：org.apache.catalina.core.StandardEngineValv.invoke(StandardEngineValv.java:78)网址：org.apache.catalina.connecter.CoyoteAdapter.service(Coyotedapter.java:357)网址：org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)网址：org.apache.coyote.AbstractProcessorLight.produce(AbstractProcessorLight.java:65)网址：org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)网址：org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1732)网址：org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)网址：org.apache.tomcat.util.threads.ThreadPoolExecutior.runWorker(ThreadPoolExecutior.java:1191)网址：org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)网址：org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)在java.lang.Thread.run(线程.java：748)由以下原因引起：org.hubinate.exception.SQLGrammaException：无法提取ResultSet位于org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversion Delegate.java:103)网址：org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37)位于org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)位于org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)位于org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67)在org.hibernate.loader.loader.getResultSet(loader.java:2322)在org.hibernate.loader.loader.executeQueryStatement(loader.java:2075)在org.hibernate.loader.loader.executeQueryStatement(loader.java:2037)在org.hibernate.loader.loader.doQuery(loader.java:956)位于org.hibernate.loader.loader.doQueryAndInitializeNonLazyCollection(loader.java:357)位于org.hibernate.loader.loader.doList(loader.java:2868)位于org.hibernate.loader.loader.doList(loader.java:2850)位于org.hibernate.loader.loader.listIgnoreQueryCache(loader.java:2682)在org.hibernate.loader.loader.list(loader.java:2677)网址：org.hibernate。loader.custom.CustomLoader.list(CustomLoader.java:338)位于org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2181)网址：org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1204)位于org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:177)网址：org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProduced query.java：1617)在org.hibernate.query.query.getResultList(query.java:165)网址：org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:128)网址：org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:90)网址：org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155)网址：org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143)位于org.springframework.data.restore.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)位于org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvokeer.java:121)位于org.springframework.data.repository.core.support.QueryExecutiorMethodInterceptor.doInvoke(QueryExecutiorMethod Interceptor.java:159)网址：org.springframework.data.repository.core.support.QueryExecutiorMethodInterceptor.ioke(QueryExecutiorMethod Interceptor.java:138)网址：org.springframework.aop.framework.ReflectiveMethodInvocation.prough(ReflectiveMethodInvocation.java:186)位于org.springframework.data.project.DefaultMethodInvokingMethodInterceptor.ioke(DefaultMethodInvokingMethodInterceptor.java:80)网址：org.springframework.aop.framework.ReflectiveMethodInvocation.prough(ReflectiveMethodInvocation.java:186)位于org.springframework.transaction.enterceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)位于org.springframework.transaction.intercept.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)位于org.springframework.transaction.interceptor.TransactionInterceptor.ininvoke(TransactionInterceptor.java:119)网址：org.springframework.aop.framework.ReflectiveMethodInvocation.prough(ReflectiveMethodInvocation.java:186)位于org.springframework.do.support.PersistenceExceptionTranslationInterceptor.ioke(PersistenceExceptionTranslationInterceptor.java:137)…还有63引起原因：org.postgresql.util.PSQLException：ERROR:relationship"案例"；不存在职位：30位于org.postgresql.core.v3.QueryExecutiorImpl.receiveErrorResponse(QueryExecutiorimpl.java:2674)网址：org.postgresql.core.v3.QueryExecutiorImpl.processResults(QueryExecutiorimpl.java:2364)网址：org.postgresql.core.v3.QueryExecutiorImpl.execute(QueryExecutiorimpl.java:354)网址：org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:484)网址：org.postgresql.jdbc.PgStatement.execute(PgStatement.java:404)网址：org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparadStatement.java:162)网址：org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparadStatement.java:114)网址：com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreperedStatement.java:52)访问com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariPoxyPreparedStatement.java)位于org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)…还有94个

我的实体表结构如下：

package com.ericsson.smart.meter.smartmeter.model;
import java.util.Date;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;
@NoArgsConstructor
@AllArgsConstructor
@ToString
@Getter
@Setter
@Entity
@Table(name = ""P_CC_CASE"", schema = "public")
public class Cases {
@Id
@Column(name = ""CASE_ID"")
private long caseId;
@Column(name = ""CASE_FOCUS"")
private String caseFocus;
@Column(name = ""CASE_TYPE"")
private String caseType;
@Column(name = ""CASE_CATEGORY"")
private String caseCategory;
@Column(name = ""CASE_RPT_CATEGORY"")
private String caseRptCategory;
@Column(name = ""CASE_STATUS"")
private String caseStatus;
@Column(name = ""CASE_CUST_SEVERITY"")
private String caseCustSeverity;
@Column(name = ""CASE_EON_SEVERITY"")
private String caseeonSeverity;
@Column(name = ""CASE_PRIORITY"")
private String casePriority;
@Column(name = ""CASE_ISSUE"")
private String caseIssue;
@Column(name = ""CREATED_ON"")
private Date createdOn;
@Column(name = ""EXPECTED_RESPONSE_DATE"")
private Date expectedResponseDate;
@Column(name = ""LAST_CONTACT_DATE"")
private Date lastContactDate;
@Column(name = ""FIRST_RESPONSE_TO_CUST"")
private Date firstResponseToCust;
@Column(name = ""CONTACT_DATE"")
private Date contactDate;
@Column(name = ""CREATED_BY"")
private String createdBy;
@Column(name = ""ORGANIZATION_ID"")
private String organizationId;
@Column(name = ""QUEUE"")
private String queue;
@Column(name = ""CHANGED_BY"")
private String changedBy;
@Column(name = ""EQUIPMENT_ID"")
private String equipmentId;
@Column(name = ""EVENT_ID"")
private String eventId;
@Column(name = ""CASE_TITLE"")
private String caseTitle;
@Column(name = ""CASE_DESC"")
private String caseDesc;
@Column(name = ""SUPPORT_KEY"")
private String supportKey;
@Column(name = ""OBJEVENTS"")
private String objevents;
@Column(name = ""POD"")
private String pod;
@Column(name = ""CHNAGED_DATE"")
private Date chnagedDate;
}

我试图实现的查询如下：

SELECT T1.* FROM (SELECT * FROM "P_CC_CASE" WHERE "CASE_TYPE" IN ('GAS','HEAT','ELECTRICITY') OR "CASE_TYPE" IS NULL) T1;

类似：public static final String GET_CASES_BY_PARAMS = "SELECT t from (SELECT c from Cases c where c.caseType IN (:caseCategories) OR c.caseType IS NULL) t";

@Query(value = GET_CASES_BY_PARAMS, nativeQuery = true)
List<Cases> searchCases(@Param("caseCategories") List<String> caseCategories);

我无法理解内部查询也返回了一组类型Cases，整个查询也是如此，但在一起编写时会出现错误。有人能帮忙吗？我该如何实施？

提前谢谢。

Answer 1

SELECT T1.* FROM (SELECT * FROM "P_CC_CASE" WHERE "CASE_TYPE" IN ('GAS','HEAT','ELECTRICITY') OR "CASE_TYPE" IS NULL) T1;

请将@Column和@Entity的值属性更改为@Column("Column")和@Entity("p_CC_CASE")

表名为"；P_ CC_；配额加倍。您需要在@Entity annotation 中写入P_CC_CASE

SELECT*FROM"；P_ CC_；表示表名是字符串，因此找不到它。

package com.ericsson.smart.meter.smartmeter.model;
import java.util.Date;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;
@NoArgsConstructor
@AllArgsConstructor
@ToString
@Getter
@Setter
@Entity
@Table(name = "P_CC_CASE", schema = "public")
public class Cases {
@Id
@Column(name = "CASE_ID")
private long caseId;
@Column(name = "CASE_FOCUS")
private String caseFocus;
...
}

另外还有一个问题。如果nativeQuery为true，则不能使用jqpl。

@Query(value = GET_CASES_BY_PARAMS) List<Cases> searchCases(@Param("caseCategories") List<String> caseCategories);