Spring JPA中的NamedStoredProcedureQuery错误 - “找到与位置参数关联的命名存储过程参数”

问题描述：

我想用Spring JPA提供的NamedStoredProcedureQuery调用Postgresql中编写的存储过程。以下是代码片段。Spring JPA中的NamedStoredProcedureQuery错误 - “找到与位置参数关联的命名存储过程参数”

EntityMovement.java

@Entity 
@Table(name = "entity_movement") 
@NamedStoredProcedureQueries({ 
    @NamedStoredProcedureQuery(name = "near_by_entities", 
           procedureName = "near_by_entities", 
           parameters = { 
            @StoredProcedureParameter(mode = ParameterMode.IN, name = "location", type = String.class), 
            @StoredProcedureParameter(mode = ParameterMode.IN, name = "radius", type = Double.class), 
            @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class) 
           }) 
}) 
public class EntityMovement implements Serializable{ 

//Fields 

//Getters and Setters 

}

EntityMovementRepository

@Repository 
public interface EntityMovementRepository extends JpaRepository<EntityMovement, Entity>{ 
    @Procedure(name = "near_by_entities") 
    public List<EntityMovement> nearByEntities(@Param("location")String location,@Param("radius")double radius); 

}

调用

List<EntityMovement> entityMovements= entityMovementRepository.nearByEntities(location, radius);

Stored Procedure

查询被简化

CREATE OR REPLACE FUNCTION public.near_by_entities(
location character varying, 
radius double precision) 
RETURNS refcursor 
LANGUAGE 'plpgsql' 
AS $BODY$ 
DECLARE ref refcursor; 
BEGIN 
OPEN ref FOR SELECT * FROM public.entity_movement; 
RETURN ref; 
END 
$BODY$;

堆栈跟踪

org.springframework.dao.InvalidDataAccessApiUsageException: Found named stored procedure parameter associated with positional parameters; nested exception is java.lang.IllegalStateException: Found named stored procedure parameter associated with positional parameters 
at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:381) ~[spring-orm-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:246) ~[spring-orm-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:488) ~[spring-orm-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59) ~[spring-tx-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213) ~[spring-tx-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147) ~[spring-tx-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133) ~[spring-data-jpa-1.11.7.RELEASE.jar:na] 
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57) ~[spring-data-commons-1.13.7.RELEASE.jar:na] 
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at com.sun.proxy.$Proxy210.nearByEntities(Unknown Source) ~[na:na] 
at com.onwards.LocationEngine.business.EntityMovementBusinessImpl.findNearByEntities(EntityMovementBusinessImpl.java:38) ~[classes/:0.0.1-SNAPSHOT] 
at com.onwards.LocationEngine.business.EntityMovementBusinessImpl$$FastClassBySpringCGLIB$$99567b2c.invoke(<generated>) ~[classes/:0.0.1-SNAPSHOT] 
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282) ~[spring-tx-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at com.onwards.LocationEngine.business.EntityMovementBusinessImpl$$EnhancerBySpringCGLIB$$b7870dee.findNearByEntities(<generated>) ~[classes/:0.0.1-SNAPSHOT] 
at com.onwards.LocationEngine.controller.EntityMovementController.findNearByEntities(EntityMovementController.java:37) ~[classes/:0.0.1-SNAPSHOT] 
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_144] 
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_144] 
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_144] 
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_144] 
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133) ~[spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at javax.servlet.http.HttpServlet.service(HttpServlet.java:635) ~[servlet-api.jar:na] 
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) ~[servlet-api.jar:na] 
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) [catalina.jar:8.5.23] 
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.23] 
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) ~[tomcat-websocket.jar:8.5.23] 
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:8.5.23] 
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.23] 
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:8.5.23] 
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.23] 
at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:108) ~[spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:8.5.23] 
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.23] 
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81) ~[spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:8.5.23] 
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.23] 
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) ~[spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:8.5.23] 
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.23] 
at org.springframework.boot.web.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:115) [spring-boot-1.5.7.RELEASE.jar:1.5.7.RELEASE] 
at org.springframework.boot.web.support.ErrorPageFilter.access$000(ErrorPageFilter.java:59) [spring-boot-1.5.7.RELEASE.jar:1.5.7.RELEASE] 
at org.springframework.boot.web.support.ErrorPageFilter$1.doFilterInternal(ErrorPageFilter.java:90) [spring-boot-1.5.7.RELEASE.jar:1.5.7.RELEASE] 
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE] 
at org.springframework.boot.web.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:108) [spring-boot-1.5.7.RELEASE.jar:1.5.7.RELEASE] 
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:8.5.23] 
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.23] 
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199) [catalina.jar:8.5.23] 
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [catalina.jar:8.5.23] 
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478) [catalina.jar:8.5.23] 
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) [catalina.jar:8.5.23] 
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) [catalina.jar:8.5.23] 
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650) [catalina.jar:8.5.23] 
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) [catalina.jar:8.5.23] 
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) [catalina.jar:8.5.23] 
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803) [tomcat-coyote.jar:8.5.23] 
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-coyote.jar:8.5.23] 
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) [tomcat-coyote.jar:8.5.23] 
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459) [tomcat-coyote.jar:8.5.23] 
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-coyote.jar:8.5.23] 
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_144] 
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_144] 
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-util.jar:8.5.23] 
at java.lang.Thread.run(Thread.java:748) [na:1.8.0_144]

我新的春天JPA和它的注解。该参数的名称在@StoredProcedureParameter中明确提到，并且@param在存储库功能中使用了相同的名称。这看起来像是一个非常直接的错误信息，因为它表示我使用的是命名参数而不是位置参数，并且我缺少一些非常明显的东西。但我无法在任何论坛中找到任何解决方案。任何帮助，将不胜感激。谢谢！！

编辑 - 添加表结构

CREATE TABLE public.entity_movement ( entity bigint NOT NULL, location geography NOT NULL, movement_time timestamp with time zone NOT NULL, CONSTRAINT pk_entity PRIMARY KEY (entity), CONSTRAINT fk2sd7ux7x1atbbpdl4y0lwc9la FOREIGN KEY (entity) REFERENCES public.entity (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_entity FOREIGN KEY (entity) REFERENCES public.entity (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE )

您是否尝试过在定义过程参数时重新排序属性名称？在我发现的所有例子中，它们都是“名称，模式，类型”。在某些版本中，还存在一个与游标相关的错误：https://*.com/a/33638491/2553194似乎ParameterStrategy已被设置为POSITIONAL而不是NAMED。希望有人能帮助你。 – RubioRic

你解决了你的问题吗？在这种情况下，请张贴解决方案，所以我们都可以学习:-) – RubioRic

@RubioRic我尝试了一整天的各种排列和组合，但没有发生任何事情。在我尝试时，错误消息一直在“您正在使用位置参数的命名参数”更改为“返回类型是JDBC.Types.1111但注册了JDBC.Types.2012”。这是为了一个紧急的原型，所以我不得不求助于使用JDBC（在一个spring-boot，hibernate项目:(）来完成它。所以我还没有找到任何解决方案，我只是做了一个工作 – PriyaAnil

答

你可以尝试将名称添加到输出参数：

@StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, name = "out", type = void.class)

答

中为每个JPA2弹簧数据的JPA例子。 1使用存储过程

spring-data-examples

表示对存储库方法调用的两种不同解释，一种是与注释元数据显式映射，另一种从存储库派生过程元数据。

调用UserRepository.plus1BackedByOtherNamedStoredProcedure（...）将使用在用户域类上声明的元数据执行存储过程plus1inout。

UserRepository.plus1inout（...）将从存储库派生存储过程元数据，并默认为位置参数绑定，并期望后备存储过程的单个输出参数。

在这里，这可能是这样的情况下调用nearByEntities通过从回购得到解决，这是位置？

为了得到一个尝试，我们可以更新名称中标注

@NamedStoredProcedureQuery(name = "near_by_entities",

要

@NamedStoredProcedureQuery(name = "EntityMovement.nearByEntities",

随着

@Procedure(name = "near_by_entities") 
public List<EntityMovement> nearByEntities(@Param("location")String location,@Param("radius")double radius);

要

@Procedure(name = "EntityMovement.nearByEntities") 
public List<EntityMovement> nearByEntitiesNamed(@Param("location")String location,@Param("radius")double radius);

呼叫将

List<EntityMovement> entityMovements= entityMovementRepository.nearByEntitiesNamed(location, radius);

谢谢你我会尽快澄清它是否有效 – PriyaAnil

我试过了你的代码片段，我得到以下错误， '类型不能为空 \t at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl。 setHibernateType（AbstractParameterRegistrationImpl.java:178）〜[hibernate-core-5.0.12.Final.jar：5.0.12.Final]' 我看到这个警告也在顶部 'ohpinternal.ProcedureCallImpl：HHH000456：Named参数用于可调用语句，但数据库元数据指示不支持命名参数编辑 – PriyaAnil

@PriyaAnil关于警告有关于hibernate社区的解释，这些问题与正在使用的sql驱动程序有关，警告可以被忽略https://forum.hibernate.org/viewtopic.php?f=1&t= 1043165 – Rizwan

答

问题的根源是，你混合命名和位置参数。的JPA 2.1 specification在第3.10.17.1命名的存储过程的查询指出，这种用法导致未定义行为：

如果使用参数名称参数名称用于该参数值绑定，并提取输出值（如果参数是INOUT或OUT参数）。如果没有指定参数名称，则假定使用位置参数。命名参数和位置参数的混合是不确定的。

这也可能是Hibernate--确定参数策略时仅检查ParameterDefinition#L156中的第一个存储过程参数的原因。

“找到与位置参数关联的已命名的存储过程参数”错误消息有点令人误解，因为在ProcedureCallImpl#L423中，当参数策略被命名但参数是位置的并且相反的方式时，会使用相同的错误消息。在你的情况下的错误消息应该是这样的：“找到与命名参数关联的位置存储过程参数”（因为在你的情况下，策略被定义为named，但最后的REF_CURSOR参数是位置的）。

为了解决这个问题，我们可以将名称添加到REF_CURSOR参数：

@StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, name = "out", type = void.class)

但不幸的是，这将导致另一场（误导）错误信息：

org.springframework.orm.jpa.JpaSystemException: PostgreSQL supports only one REF_CURSOR parameter, but multiple were registered

尽管只有一个已注册REF_CURSOR参数我们收到一条关于注册多个的错误消息。唯一的例外是PostgresCallableStatementSupport#L66抛出，事实上其renderCallableStatement()方法包含有关要求的一些有用的信息时，规定了一个REF_CURSOR参数：

它应该是第一个参数
参数策略必须是位置

并且还在renderCallableStatement()方法的评论中明确指出混合命名和位置参数是不允许的。

所以我们应该删除所提供的名称：

@StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class), 
@StoredProcedureParameter(mode = ParameterMode.IN, type = String.class), 
@StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class)

由于目前春节数据不支持位置参数映射的首要（仅named parameter mapping）和我们的第一个参数是一个REF_CURSOR，我们得到了以下错误消息当弹簧数据试图映射REF_CURSOR到存储库中的接口中定义的第一种方法参数：

InvalidDataAccessApiUsageException: Parameter value [location] did not match expected type [void (n/a)]

所以@Procedure不能再被使用，但作为一种解决方法，我们可以创建并实施一个单独的EntityMovementRepositoryWithProcedure接口和做手工映射：调用存储过程时，否则

public interface EntityMovementRepositoryWithProcedure { 
    List<EntityMovement> nearByEntities(String location, double radius); 
} 

@Repository 
public interface EntityMovementRepository extends JpaRepository<EntityMovement, Integer>, EntityMovementRepositoryWithProcedure { } 

public class EntityMovementRepositoryImpl implements EntityMovementRepositoryWithProcedure { 

    @PersistenceContext 
    private EntityManager em; 

    @Override 
    public List<EntityMovement> nearByEntities(String location, double radius) { 
     StoredProcedureQuery nearByEntities em.createNamedStoredProcedureQuery("near_by_entities"); 
     nearByEntities.setParameter(2, location); 
     nearByEntities.setParameter(3, radius); 
     return nearByEntities.getResultList(); 
    } 
}

而且autocommit has to be disabled when using PostgreSQL REF_CURSOR下，会引发异常：

PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist

一个完整的实例可以在这里找到：https://github.com/sandor-balazs/example/tree/master/spring-data-postgresql-refcursor。

感谢您为什么错误即将到来的详细说明。但是，在我遇到问题时，我已经尝试了您在答案结尾处提出的解决方法。错误是'org.postgresql.util.PSQLException：执行了一个CallableStatement函数，out参数1的类型为java.sql.Types = 1111，但是注册了java.sql.Types = 2012的类型。建议，以确保我没有错过任何可能的解决方案。我不得不求助于JDBC来完成这项工作。 – PriyaAnil

@PriyaAnil你可以更新你的问题，关于你正在使用的表结构的更多细节？我试图重现你的错误，但我没有得到'PSQLException'与错误的注册类型相关。我还更新了我的答案，并提供了一个完整的示例链接。 –

@SandorBalazs很好的解释 – Rizwan

Spring JPA中的NamedStoredProcedureQuery错误 - “找到与位置参数关联的命名存储过程参数”

相关推荐