calling stored procedure spring boot application using jparepository
Solution 1
I found an answer to this question in another query. I can't find it now. But essentially, you need to remove the @NamedStoredProcedureQuery from your entity class and call the procedure directly in your service class:
@PersistenceContext
private EntityManager em;
// your method structure here
StoredProcedureQuery query = em.CreateStoredProcedureQuery("filtercreatives", CreativeConfig.class);
query.registerStoredProcedureParameter(0, String.class, ParameterMode.IN);
/*
* repeat the above format for each parameter. Notice the use of
* digits instead of parameter names. Then.....
*/
query.setParameter(0,"your value");
As I side note, another 'gotcha' I found was that the returned parameters were given underscores, even if there were none, if camel-case was used. So, if you have problems with it not finding 'configUrl', and your database doesn't have 'config_url', try changing it in your code to 'configurl'.
Solution 2
I also faced same issue I got resolved by jus removing @ sign from
updated
@StoredProcedureParameter( mode=ParameterMode.IN, type = String.class, name="category_id")
Admin
Updated on July 15, 2022Comments
-
Admin almost 2 years
I need to call a stored procedure in SQL Server using Spring JPARepository, I followed exactly that is suggested in documentation but I get the error saying “Invalid mix of positional and named parameters” Below I have given all the classes and stored procedures I have used, when I do a filterCreatives(...) call on the JPARepository it fails with the said error, and as you know if I make the return type of filterCreatives(..) void it passes now How can I make the code work with return type as List I have seen the same problem in stack overflow for a long time but it is unanswered
Please can you help me if there is a out of box solution tried searching in the net stood in vain thanks in advance.
Stored Procedure in SQL Server:
Alter Procedure filtercreatives /* Input Parameters */ @category_id nvarchar(MAX), @advertiser_id nvarchar(MAX), @platform_id nvarchar(MAX), @size_id nvarchar(MAX), @template_id nvarchar(MAX), @language_id nvarchar(MAX) AS BEGIN /* Variable Declaration */ Declare @SQLQuery AS NVarchar(4000) Declare @ParamDefinition AS NVarchar (2000) /* Build the Transact-SQL String with the input parameters */ Set @SQLQuery = 'Select * From creativegallery_config where ' /* check for the condition AND build the WHERE clause accordingly */ If (@category_id is not null AND @category_id != '') Set @SQLQuery = @SQLQuery + ' category_id in ('+@category_id+') AND' If (@advertiser_id is not null AND @advertiser_id != '') Set @SQLQuery = @SQLQuery + ' advertiser_id in ('+@advertiser_id+') AND' If (@platform_id is not null AND @platform_id != '') Set @SQLQuery = @SQLQuery + ' platform_id in ('+@platform_id+') AND' If (@size_id is not null AND @size_id != '') Set @SQLQuery = @SQLQuery + ' size_id in ('+@size_id+') AND' If (@template_id is not null AND @template_id != '') Set @SQLQuery = @SQLQuery + ' template_id in ('+@template_id+') AND' If (@language_id is not null AND @language_id != '') Set @SQLQuery = @SQLQuery + ' language_id in ('+@language_id+') ' Set @SQLQuery = RTRIM(@SQLQuery) Declare @len as varchar(200) Set @len = LEN(@SQLQuery) if( CHARINDEX('DNA',REVERSE(@SQLQuery)) < 2 and CHARINDEX('DNA',REVERSE(@SQLQuery)) >0) Set @SQLQuery = SUBSTRING(@SQLQuery,1,LEN(@SQLQuery)-3) if( CHARINDEX('EREHW',REVERSE(@SQLQuery)) < 2 and CHARINDEX('EREHW',REVERSE(@SQLQuery)) >0) Set @SQLQuery = SUBSTRING(@SQLQuery,1,LEN(@SQLQuery)-5) Execute sp_Executesql @SQLQuery If @@ERROR <> 0 GoTo ErrorHandler Return(0) ErrorHandler: Return(@@ERROR) END
Java entity class :
@NamedStoredProcedureQuery( name="filtercreatives", procedureName="filtercreatives", parameters={ @StoredProcedureParameter( mode=ParameterMode.IN, type = String.class,name="@category_id"), @StoredProcedureParameter( mode=ParameterMode.IN, type = String.class,name="@advertiser_id"), @StoredProcedureParameter( mode=ParameterMode.IN, type = String.class,name="@platform_id"), @StoredProcedureParameter( mode=ParameterMode.IN, type = String.class,name="@size_id"), @StoredProcedureParameter( mode=ParameterMode.IN, type = String.class,name="@template_id"), @StoredProcedureParameter( mode=ParameterMode.IN, type = String.class,name="@language_id") },resultClasses=CreativeConfig.class ) @Entity @Table(name = "creativegallery_config") public class CreativeConfig implements Serializable{ private static final long serialVersionUID = 6201074363091569476L; @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; private Integer advertiserId; private Integer sizeId; private Integer languageId; private Integer platformId; private Integer templateId; private Integer categoryId; private String configUrl; private String tileImageUrl; }
JPA Repository Interface:
@Repository public interface CreativeConfigRepository extends JpaRepository<CreativeConfig, Integer> { List<CreativeConfig> findByAdvertiserIdIn(List<Integer> advertiserId); @Procedure(name = "filtercreatives") List<CreativeConfig> filterCreatives(@Param("@category_id")String categoryId,@Param("@advertiser_id") String advertiserId,@Param("@platform_id") String platformId,@Param("@size_id") String sizeId, @Param("@template_id")String templateId, @Param("@language_id")String languageId ); }
Stack Trace:
org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: Invalid mix of named and positional parameters; nested exception is java.lang.IllegalArgumentException: Invalid mix of named and positional parameters at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:979) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:858) at javax.servlet.http.HttpServlet.service(HttpServlet.java:687) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:843) at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:808) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1669) at org.eclipse.jetty.websocket.server.WebSocketUpgradeFilter.doFilter(WebSocketUpgradeFilter.java:224) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) at org.springframework.boot.actuate.autoconfigure.EndpointWebMvcAutoConfiguration$ApplicationContextHeaderFilter.doFilterInternal(EndpointWebMvcAutoConfiguration.java:295) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at com.ignitionone.config.WebSecurityConfig$1.doFilterInternal(WebSecurityConfig.java:76) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.csrf.CsrfFilter.doFilterInternal(CsrfFilter.java:85) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:57) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192) at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) at org.springframework.boot.actuate.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:102) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:85) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) at org.springframework.boot.actuate.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:69) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143) at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577) at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223) at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127) at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515) at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185) at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97) at org.eclipse.jetty.server.Server.handle(Server.java:499) at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310) at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257) at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635) at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555) at java.lang.Thread.run(Thread.java:745) Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: Invalid mix of named and positional parameters; nested exception is java.lang.IllegalArgumentException: Invalid mix of named and positional parameters at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:381) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:223) at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417) at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59) at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:122) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) at com.sun.proxy.$Proxy91.FilterCreatives(Unknown Source) at com.ignitionone.service.ConfigAdvertiserDBService.getConfigs(ConfigAdvertiserDBService.java:176) at com.ignitionone.service.ConfigAdvertiserDBService.getConfigs(ConfigAdvertiserDBService.java:154) at com.ignitionone.controller.CreativesController.getCreativess(CreativesController.java:46) 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:497) at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:776) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:705) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:967) ... 75 common frames omitted Caused by: java.lang.IllegalArgumentException: Invalid mix of named and positional parameters at org.hibernate.jpa.internal.StoredProcedureQueryImpl.getOutputParameterValue(StoredProcedureQueryImpl.java:279) at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.extractOutputValue(StoredProcedureJpaQuery.java:120) at org.springframework.data.jpa.repository.query.JpaQueryExecution$ProcedureExecution.doExecute(JpaQueryExecution.java:298) at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:74) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:99) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:90) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:415) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:393) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.data.repository.core.support.RepositoryFactorySupport$DefaultMethodInvokingMethodInterceptor.invoke(RepositoryFactorySupport.java:506) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ... 98 common frames omitted Caused by: org.hibernate.procedure.ParameterStrategyException: Attempt to access positional parameter [7] but ProcedureCall using named parameters at org.hibernate.procedure.internal.ProcedureCallImpl.getParameterRegistration(ProcedureCallImpl.java:329) at org.hibernate.procedure.internal.ProcedureOutputsImpl.getOutputParameterValue(ProcedureOutputsImpl.java:68) at org.hibernate.jpa.internal.StoredProcedureQueryImpl.getOutputParameterValue(StoredProcedureQueryImpl.java:276) ... 113 common frames omitted