Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

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

Tags:

java

spring

postgresql

hibernate

jpa

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!!

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 )

like image 551
PriyaAnil Avatar asked Oct 17 '17 09:10

PriyaAnil

1 Answers

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.

like image 99
Sandor Balazs Avatar answered Nov 04 '22 21:11

Sandor Balazs
Sign in to Comment

Related questions

TreeMap and setValue method
Detect a key press in console
How to avoid two different threads read the same rows from DB (Hibernate and Oracle 10g)
replace classes from sun.security.* packages
Switching on Strings
Can WebSockets and HTTP server both run on the SAME port number?
Spring Security 4.0.0 + ActiveDirectoryLdapAuthenticationProvider + BadCredentialsException PartialResultException
Android scaling/transforming canvas doesn't modify clickable area
How to force eager loading with CrudRepository in spring-data?
Java compiler choosing wrong overload [duplicate]
Thymeleaf Map Form Binding
Why is ClassLoader's cache checked in ascending sequence?
Why does not ArrayList override equals() for better performance?
logback: no applicable action for [encoder], current ElementPath is [[configuration][appender][encoder]]
RxJava + Retrofit + polling
@SafeVarargs on interface method
Java and haarcascade face and mouth detection - mouth as the nose
Athena create table from parquet schema
How to see org.codehaus.jackson log messages - using logging.properties
JSON parse error: Already had POJO for id

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!