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

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

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/46786528/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-10-21 02:39:10 　来源:igfitidea点击:

Error in NamedStoredProcedureQuery in Spring JPA - "Found named stored procedure parameter associated with positional parameters"

javaspringpostgresqlhibernatejpa

提问by PriyaAnil

I am trying to call a Stored Procedure written in Postgresql using the NamedStoredProcedureQuery provided by Spring JPA. Following are code snippets.

我正在尝试使用 Spring JPA 提供的 NamedStoredProcedureQuery 调用在 Postgresql 中编写的存储过程。以下是代码片段。

EntityMovement.java

实体移动.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);

}

Calling

打电话

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

Stored Procedure

Stored Procedure

Query is simplified

查询简化

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$;

Stack Trace

堆栈跟踪

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$567b2c.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.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
@StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, name = "out", type = void.class)

0(ErrorPageFilter.java:59) [spring-boot-1.5.7.RELEASE.jar:1.5.7.RELEASE]
at org.springframework.boot.web.support.ErrorPageFilter.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]

I am new to Spring JPA and it's annotations. The name of the parameter is clearly mentioned in the @StoredProcedureParameterand the same is being used with the @paramin repository function. This seems like a very straight forward error message as it says that I am using named parameters instead of positional parameters and I am missing something very obvious. But I am not able to find any solution in any of the forums. Any help would be appreciated. Thanks!!

我是 Spring JPA 的新手，它是注释。参数的名称在 中明确提及，@StoredProcedureParameter并且与@param存储库函数中使用的名称相同。这似乎是一个非常直接的错误消息，因为它说我使用的是命名参数而不是位置参数，并且我遗漏了一些非常明显的东西。但是我无法在任何论坛中找到任何解决方案。任何帮助，将不胜感激。谢谢！！

EDIT - Adding Table Structure

编辑 - 添加表结构

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 )

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 )

回答by Sandor Balazs

The root of the problem is that you are mixing named and positional parameters. The JPA 2.1 specificationin section 3.10.17.1 Named Stored Procedure Queriesstates that this usage leads to undefined behavior:

问题的根源在于您混合了命名参数和位置参数。在JPA 2.1规范中部分命名的存储过程的查询3.10.17.1指出，这种用法导致未定义行为：

If parameter names are used, the parameter name is used to bind the parameter value and to extract the output value (if the parameter is INOUT or OUT parameter). If parameter names are not specified, it is assumed that positional parameters are used. The mixing of named and positional parameters is undefined.

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

This can also be the reason why Hibernate - when determining the parameter strategy - checks only the first stored procedure parameter in ParameterDefinition#L156.

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

The "Found named stored procedure parameter associated with positional parameters" error message is a bit misleading because in ProcedureCallImpl#L423the same error message is used when the parameter strategy is named but the parameter is positional and the other way around. The error message in your case should be this: "Found positional stored procedure parameter associated with named parameters" (since in your case the strategy was defined as named but your last REF_CURSORparameter is positional).

“找到与位置参数关联的命名存储过程参数”错误消息有点误导，因为在ProcedureCallImpl#L423命名参数策略但参数是位置参数时使用相同的错误消息，反之亦然。您的情况下的错误消息应该是这样的：“找到与命名参数关联的位置存储过程参数”（因为在您的情况下，策略被定义为命名但您的最后一个REF_CURSOR参数是位置）。

To fix this we can add a name to the REF_CURSORparameter:

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

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

But unfortunately this will lead to another (misleading) error message:

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

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

Despite having registered only one REF_CURSORparameter we get an error message about having registered more than one. The exception is thrown by PostgresCallableStatementSupport#L66and in fact its renderCallableStatement()method contains several useful information about the requirements when there is a REF_CURSORparameter defined:

尽管只注册了一个REF_CURSOR参数，但我们会收到一条关于注册多个参数的错误消息。当定义了参数时，异常由PostgresCallableStatementSupport#L66并且实际上它的renderCallableStatement()方法包含一些关于需求的有用信息REF_CURSOR：

  • It should be the first parameter
  • The parameter strategy has to be positional
  • 应该是第一个参数
  • 参数策略必须是位置性的

And also in a comment in renderCallableStatement()method it is explicitly stated that mixing named and positional parameters is not allowed.

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

So we should remove the provided names:

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

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

Since currently Spring Data does not support the overriding of positional parameter mapping (only named parameter mapping) and our first parameter is a REF_CURSORwe get the following error message when Spring Data tries to map REF_CURSORto the first method parameter defined in the repository interface:

由于当前 Spring Data 不支持覆盖位置参数映射（仅命名参数映射）并且我们的第一个参数是 aREF_CURSOR当 Spring Data 尝试映射REF_CURSOR到存储库接口中定义的第一个方法参数时，我们收到以下错误消息：

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

So @Procedurecan no longer be used but as a workaround we can create and implement a separate EntityMovementRepositoryWithProcedureinterface and do the mapping manually:

所以@Procedure不能再使用，但作为一种解决方法，我们可以创建和实现一个单独的EntityMovementRepositoryWithProcedure接口并手动进行映射：

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

Also autocommit has to be disabled when using PostgreSQL REF_CURSORotherwise the following exception will be thrown when calling the stored procedure:

也自动提交具有使用PostgreSQL时要禁用REF_CURSOR，否则下面会抛出异常调用存储过程时：

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

A fully working example is available here: https://github.com/sandor-balazs/example/tree/master/spring-data-postgresql-refcursor.

这里提供了一个完整的示例：https: //github.com/sandor-balazs/example/tree/master/spring-data-postgresql-refcursor。

回答by Simon Martinelli

Can you try to add a name to the out parameter:

您可以尝试在 out 参数中添加一个名称吗：

 @NamedStoredProcedureQuery(name = "near_by_entities",

回答by Rizwan

As per the spring-data jpa example for jpa2.1 for using Stored procedure

根据 jpa2.1 的 spring-data jpa 示例使用存储过程

spring-data-examples

弹簧数据示例

There represents two different interpretation of the call to the repository method one which explicitly maps with annotation metadata and other derives procedure metadata from the repository.

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

Calling UserRepository.plus1BackedByOtherNamedStoredProcedure(…) will execute the stored procedure plus1inout using the meta-data declared on the User domain class.

UserRepository.plus1inout(…) will derive stored procedure metadata from the repository and default to positional parameter binding and expect a single output parameter of the backing stored procedure.

调用 UserRepository.plus1BackedByOtherNamedStoredProcedure(...) 将使用 User 域类上声明的元数据执行存储过程 plus1inout。

UserRepository.plus1inout(...) 将从存储库中派生存储过程元数据，并默认为位置参数绑定，并期望支持存储过程的单个输出参数。

In here, this might be the case the call to nearByEntities is getting resolved by deriving from repo and that is positional?

在这里，这可能是通过从 repo 派生来解决对 NearByEntities 的调用的情况，这是位置？

To get a try we can update the name inside annotation

要尝试，我们可以更新注释中的名称

 @NamedStoredProcedureQuery(name = "EntityMovement.nearByEntities",

To

到

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

Along with

随着 

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

To

到 

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

Calling would be

调用将是

##代码##

相关推荐

postgresql 如何在 psql 控制台中打印当前工作目录？

postgresql 如何在 psql 控制台中打印当前工作目录？

我应该为我创建的新创建的 PostgreSQL 模式使用正确的文件扩展名是什么？

我应该为我创建的新创建的 PostgreSQL 模式使用正确的文件扩展名是什么？

PostgreSQL 上的同义词支持

PostgreSQL 上的同义词支持

postgresql 将逗号分隔的字符串转换为 Postgres 中的整数数组

postgresql 将逗号分隔的字符串转换为 Postgres 中的整数数组

相关推荐
最近更新
标签