JPA + Postgres Refcursor return error

132
19 февраля 2021, 19:10

Пытаюсь получить курсор из процедуры, но почему-то получаю ошибку

org.postgresql.util.PSQLException: Колонки id не найдено в этом ResultSet’’е.

Не могу понять почему, уже все перечитал. Что ему не нравится, уже голова кипит, помогите плиз(

Процедура

CREATE OR REPLACE FUNCTION lease_objects.get_lease_object(cleint_uid text)
RETURNS refcursor
LANGUAGE plpgsql
AS $$
declare
    cur refcursor;
    query text;
begin
    query := 
        'select 
            l.id as id,
            l.client_uid as client_uid, 
            l.equipment_uid as equipment_uid, 
            l.lease_contract_uid as lease_contract_uid, 
            l.product_uid as product_uid
        from lease_objects.lease_object l
        where l.client_uid = $1';
open cur for    
    execute query using cleint_uid;
return cur;
END;
$$

Entity

@Data
@Entity
@Table(name = "lease_object", schema = "lease_objects")
public class LeaseObject implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
@JsonIgnore
private String id;
@Column(name = "client_uid")
private String clientUid;
@Column(name = "equipment_uid")
private String equipmentUid;
@OneToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "product_uid", referencedColumnName = "uid")
private Product product;
@OneToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "lease_contract_uid", referencedColumnName = "uid")
private LeaseContract leaseContract;
}

И сама реализация DAO

@Override
public List getLeaseObject(@NonNull String clientUid, String equipmentUid, String productUid, String productStatus) {
    return entityManager
          .createStoredProcedureQuery("lease_objects.get_lease_object", LeaseObject.class)
          .registerStoredProcedureParameter("clientUid", String.class, ParameterMode.IN)
          .setParameter("clientUid", clientUid)
          .getResultList();
}

Стэк ошибки

org.postgresql.util.PSQLException: Колонки id не найдено в этом ResultSet’’е.
at org.postgresql.jdbc.PgResultSet.findColumn(PgResultSet.java:2593) ~[postgresql-42.2.5.jar:42.2.5]
at org.postgresql.jdbc.PgResultSet.getString(PgResultSet.java:2467) ~[postgresql-42.2.5.jar:42.2.5]
at com.zaxxer.hikari.pool.HikariProxyResultSet.getString(HikariProxyResultSet.java) ~[HikariCP-3.2.0.jar:na]
at org.hibernate.type.descriptor.sql.VarcharTypeDescriptor$2.doExtract(VarcharTypeDescriptor.java:62) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:243) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:329) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.loader.Loader.extractKeysFromResultSet(Loader.java:793) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:718) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.loader.Loader.processResultSet(Loader.java:990) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.result.internal.OutputsImpl$CustomLoaderExtension.processResultSet(OutputsImpl.java:321) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.result.internal.OutputsImpl.extractResults(OutputsImpl.java:140) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.result.internal.OutputsImpl.extractCurrentResults(OutputsImpl.java:131) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.result.internal.OutputsImpl.access$100(OutputsImpl.java:39) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.result.internal.OutputsImpl$CurrentReturnState.buildOutput(OutputsImpl.java:191) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.result.internal.OutputsImpl$CurrentReturnState.getOutput(OutputsImpl.java:175) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.result.internal.OutputsImpl.getCurrent(OutputsImpl.java:95) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.procedure.internal.ProcedureCallImpl.getResultList(ProcedureCallImpl.java:718) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at com.testing.leaseobj.dao.LeaseObjectDAOImpl.getLeaseObject(LeaseObjectDAOImpl.java:36) ~[classes/:na]
at com.testing.leaseobj.dao.LeaseObjectDAOImpl$$FastClassBySpringCGLIB$$de6c55bb.invoke(<generated>) ~[classes/:na]
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749) ~[spring-aop-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) ~[spring-aop-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at com.testing.leaseobj.dao.LeaseObjectDAOImpl$$EnhancerBySpringCGLIB$$f0438f2c.getLeaseObject(<generated>) ~[classes/:na]
at com.testing.leaseobj.controller.RestController.getIbject(RestController.java:29) ~[classes/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) ~[spring-webmvc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:892) ~[spring-webmvc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:797) ~[spring-webmvc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1039) ~[spring-webmvc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942) ~[spring-webmvc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1005) ~[spring-webmvc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:908) ~[spring-webmvc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:660) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:882) ~[spring-webmvc-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.19.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:92) ~[spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93) ~[spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) ~[spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.7.RELEASE.jar:5.1.7.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:200) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:836) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1747) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[na:na]
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.19.jar:9.0.19]
at java.base/java.lang.Thread.run(Thread.java:834)
Answer 1

Скорей всего, Ваша функция не возвращает ID объекта. Попробуйте запустить эту функцию напрямую в БД и посмотрите, что она Вам вернет. Будет ли там ID LeaseObject.

READ ALSO
Методы Scanner и проверка

Методы Scanner и проверка

пишу описание к дипломному проекту хотелось бы у вас уточнить правильность моей информации по Java, знаю как работает, а вот описать правильно...

104
Многопоточность*Thread

Многопоточность*Thread

такое задание - есть какой-то массив и число в этом массиве, нужно создать два потока, первый из которых рассматривает первую часть массива,...

118
Избавиться от Unnecessary boxing

Избавиться от Unnecessary boxing

Есть такой код

104