Issue
I have this special case. I have an Oracle stored-procedure that doesn't receive IN parameters but returns 4 OUT parameters.
My entity class is this ...
@Entity
@Table(name = "TABLE_NAME")
@NamedStoredProcedureQueries({
@NamedStoredProcedureQuery(
name = "MyPackage.spName",
procedureName = "MYPACKAGE.SPNAME",
parameters = {
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "PO_VALUE1", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "PO_VALUE2", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "PO_VALUE3", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "PO_VALUE4", type = String.class)
})
})
public class Tablename {
// omitted properties ...
}
My Repository class is this ...
@Repository
public interface TableNameRepository extends JpaRepository<TableName, Long> {
@Procedure(name = "MyPackage.spName")
Map<String, Object> spName();
}
And I'm calling the stored procedure like this ...
Map<String, Object> spNameMap = tableNameRepository.spName();
String value1 = spNameMap.get("PO_VALUE1").toString();
String value2 = spNameMap.get("PO_VALUE2").toString();
// more values ...
But it throws this exception ...
Positional parameter [1] is not registered with this procedure call;
2021-12-29 18:55:13,630 ERROR [http-nio-3000-exec-1] pe.gob.onpe.wsonpe.service.VersionService: InvalidDataAccessApiUsageException: Positional parameter [1] is not registered with this procedure call; nested exception is java.lang.IllegalArgumentException: Positional parameter [1] is not registered with this procedure call
2021-12-29 18:55:13,633 ERROR [http-nio-3000-exec-1] pe.gob.onpe.wsonpe.service.VersionService: org.springframework.dao.InvalidDataAccessApiUsageException: Positional parameter [1] is not registered with this procedure call; nested exception is java.lang.IllegalArgumentException: Positional parameter [1] is not registered with this procedure call
at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:374)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:235)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
at com.sun.proxy.$Proxy123.spFecha(Unknown Source)
at pe.gob.onpe.wsonpe.service.VersionService.verificarVersion(VersionService.java:32)
at pe.gob.onpe.wsonpe.controller.VersionController.userLogin(VersionController.java:44)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:681)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1722)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.IllegalArgumentException: Positional parameter [1] is not registered with this procedure call
at org.hibernate.query.procedure.internal.ProcedureParameterMetadata.getQueryParameter(ProcedureParameterMetadata.java:141)
at org.hibernate.procedure.internal.ProcedureCallImpl.getParameterRegistration(ProcedureCallImpl.java:311)
at org.hibernate.procedure.internal.ProcedureOutputsImpl.getOutputParameterValue(ProcedureOutputsImpl.java:53)
at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputParameterValue(ProcedureCallImpl.java:656)
at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.extractOutputParameterValue(StoredProcedureJpaQuery.java:158)
at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.extractOutputValue(StoredProcedureJpaQuery.java:142)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$ProcedureExecution.doExecute(JpaQueryExecution.java:335)
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:90)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:159)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:138)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
... 61 more
It seems that JPA is trying to map parameters by order instead by name. This behaviour is weird, since I'm using parameter names with the annotation @StoredProcedureParameter
Please internet, help me with this.
Solution
Spring boot JPA starter calls StoredProcedureJpaQuery
class on initialization.
That class contains a property called useNamedParameters
.
Calling your stored procedure like this ...
@Procedure(name = "MyPackage.spName")
Map<String, Object> spName();
means that spring JPA will map your IN and OUT parameters by position To avoid this, you have to use the annotation @Param in your repository, like this ...
@Procedure(name = "MyPackage.spName")
Map<String, Object> spName(@Param("PO_VALUE1") String value1);
using the @Param annotation will set the useNamedParameters
property to true and Spring JPA will not start looking by position instead by name. But, I don't have any IN parameters in my query. So I changed the method to INOUT like this ...
@StoredProcedureParameter(mode = ParameterMode.INOUT, name = "PO_VALUE1", type = String.class)
and passing an empty string when I call the repository ...
Map<String, Object> spNameMap = tableNameRepository.spName("");
That solution works for me, but I'm open to see a better solution for this.
Answered By - Guru_Clef
Answer Checked By - Clifford M. (JavaFixing Volunteer)