Friday, October 27, 2017

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

By Hường Hana 12:00 AM , , , , Leave a Comment

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

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

Calling 

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

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

I am new to Spring JPA and it's annotations. The name of the parameter is clearly mentioned in the @StoredProcedureParameter and the same is being used with the @param in 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!!

3 Answers

Answers 1

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

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.

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

The "Found named stored procedure parameter associated with positional parameters" error message is a bit misleading because in ProcedureCallImpl#L423 the 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_CURSOR parameter is positional).

To fix this we can add a name to the REF_CURSOR parameter:

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

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

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

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

  • 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.

So we should remove the provided names: 

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

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

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

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

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

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

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

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

Answers 2

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

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

Answers 3

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

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.

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

To get a try we can update the name inside annotation

 @NamedStoredProcedureQuery(name = "near_by_entities",

To

 @NamedStoredProcedureQuery(name = "EntityMovement.nearByEntities",

Along with 

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

To 

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

Calling would be 

List<EntityMovement> entityMovements= entityMovementRepository.nearByEntitiesNamed(location, radius);
If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment