search

spring-projects / spring-framework

Spring Framework
https://spring.io/projects/spring-framework
Apache License 2.0
56.63k stars 38.13k forks source link

NamedParameterJdbcTemplate execute complex Insert Query Fails #32965

Closed SD185415 closed 5 months ago

SD185415 commented 5 months ago

I want run a multi insert query but getting this error org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

Table Schema

create table if not exists student
(
student_id bigserial primary key,
student_name text
);

create table if not exists favourite_color
(
student_id bigint,
color text,
foreign key (student_id) references student (student_id)
);

create table if not exists favourite_animal
(
student_id bigint,
animal text,
foreign key (student_id) references student (student_id)
);

Working SQL Query

do $$
declare s_id bigint;
declare fav_color text;
declare s_name text;
declare fav_animal text;
begin
fav_color := 'Red';
fav_animal := 'Horse';
s_name := 'jhon';
insert into student (student_name)
values(s_name)
returning student_id into s_id;

insert into favourite_color(student_id,color)
values (s_id, fav_color);

insert into favourite_animal (student_id,animal)
values (s_id, fav_animal);
end
$$;

Spring Boot Code [Not Working]

public  void addStudent() {
        String sql = """
                do $$
                declare s_id bigint; declare fav_color text;
                declare s_name text; declare fav_animal text;
                begin
                fav_color := :color;
                fav_animal := :animal;
                s_name := :name;
                insert into student (student_name)
                values(s_name)
                returning student_id into s_id;

                insert into favourite_color(student_id,color)
                values (s_id, fav_color);

                insert into favourite_animal (student_id,animal)
                values (s_id, fav_animal);
                end
                $$;
                """;
        MapSqlParameterSource parameters = new MapSqlParameterSource();
        parameters.addValue("name", "Jhon");
        parameters.addValue("animal", "Horse");
        parameters.addValue("color", "Blue");
        template.update(sql, parameters);

    }

Error

2024-06-05T17:05:50.816Z DEBUG 260 --- [nio-8080-exec-9] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL update
 2024-06-05T17:05:50.816Z DEBUG 260 --- [nio-8080-exec-9] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [do $$
 declare s_id bigint; declare fav_color text;
 declare s_name text; declare fav_animal text;
 begin
 fav_color := ?;
 fav_animal := ?;
 s_name := ?;
 insert into student (student_name)
 values(s_name)
 returning student_id into s_id;

 insert into favourite_color(student_id,color)
 values (s_id, fav_color);

 insert into favourite_animal (student_id,animal)
 values (s_id, fav_animal);
 end
 $$;
 ]
 2024-06-05T17:05:50.836Z DEBUG 260 --- [nio-8080-exec-9] o.s.web.servlet.DispatcherServlet        : Failed to complete request: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [do $$
 declare s_id bigint; declare fav_color text;
 declare s_name text; declare fav_animal text;
 begin
 fav_color := ?;
 fav_animal := ?;
 s_name := ?;
 insert into student (student_name)
 values(s_name)
 returning student_id into s_id;

 insert into favourite_color(student_id,color)
 values (s_id, fav_color);

 insert into favourite_animal (student_id,animal)
 values (s_id, fav_animal);
 end
 $$;
 ]; The column index is out of range: 1, number of columns: 0.
 2024-06-05T17:05:50.837Z ERROR 260 --- [nio-8080-exec-9] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [do $$
 declare s_id bigint; declare fav_color text;
 declare s_name text; declare fav_animal text;
 begin
 fav_color := ?;
 fav_animal := ?;
 s_name := ?;
 insert into student (student_name)
 values(s_name)
 returning student_id into s_id;

 insert into favourite_color(student_id,color)
 values (s_id, fav_color);

 insert into favourite_animal (student_id,animal)
 values (s_id, fav_animal);
 end
 $$;
 ]; The column index is out of range: 1, number of columns: 0.] with root cause

 org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
         at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:70) ~[postgresql-42.3.8.jar:42.3.8]
         at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:133) ~[postgresql-42.3.8.jar:42.3.8]
         at org.postgresql.jdbc.PgPreparedStatement.bindString(PgPreparedStatement.java:1064) ~[postgresql-42.3.8.jar:42.3.8]
         at org.postgresql.jdbc.PgPreparedStatement.setString(PgPreparedStatement.java:365) ~[postgresql-42.3.8.jar:42.3.8]
         at org.postgresql.jdbc.PgPreparedStatement.setString(PgPreparedStatement.java:351) ~[postgresql-42.3.8.jar:42.3.8]
         at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setString(HikariProxyPreparedStatement.java) ~[HikariCP-5.0.1.jar:na]
         at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:440) ~[spring-jdbc-6.1.3.jar:6.1.3]
         at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:247) ~[spring-jdbc-6.1.3.jar:6.1.3]
         at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:163) ~[spring-jdbc-6.1.3.jar:6.1.3]
         at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:287) ~[spring-jdbc-6.1.3.jar:6.1.3]
         at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:245) ~[spring-jdbc-6.1.3.jar:6.1.3]
         at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:656) ~[spring-jdbc-6.1.3.jar:6.1.3]
         at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:970) ~[spring-jdbc-6.1.3.jar:6.1.3]
         at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:991) ~[spring-jdbc-6.1.3.jar:6.1.3]
         at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:337) ~[spring-jdbc-6.1.3.jar:6.1.3]
         at ncr.opencheck.checkcalculator.checkcalculatorservice.repository.impl.OrderRepositoryImpl.addStudent(OrderRepositoryImpl.java:61) ~[main/:na]
         at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) ~[na:na]
         at java.base/java.lang.reflect.Method.invoke(Method.java:580) ~[na:na]
         at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:351) ~[spring-aop-6.1.3.jar:6.1.3]
         at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) ~[spring-aop-6.1.3.jar:6.1.3]
         at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-6.1.3.jar:6.1.3]
         at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765) ~[spring-aop-6.1.3.jar:6.1.3]
         at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-6.1.3.jar:6.1.3]
         at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.3.jar:6.1.3]
         at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765) ~[spring-aop-6.1.3.jar:6.1.3]
         at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:717) ~[spring-aop-6.1.3.jar:6.1.3]
         at ncr.opencheck.checkcalculator.checkcalculatorservice.repository.impl.OrderRepositoryImpl$$SpringCGLIB$$0.addStudent(<generated>) ~[main/:na]
         at ncr.opencheck.checkcalculator.checkcalculatorservice.service.impl.OrderServiceImpl.validateAndCalculateOrder(OrderServiceImpl.java:20) ~[main/:na]
         at ncr.opencheck.checkcalculator.checkcalculatorservice.controller.OrderController.validateAndCalculateOrder(OrderController.java:22) ~[main/:na]
         at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) ~[na:na]
         at java.base/java.lang.reflect.Method.invoke(Method.java:580) ~[na:na]
         at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:261) ~[spring-web-6.1.3.jar:6.1.3]
         at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:189) ~[spring-web-6.1.3.jar:6.1.3]
         at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118) ~[spring-webmvc-6.1.3.jar:6.1.3]
         at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:917) ~[spring-webmvc-6.1.3.jar:6.1.3]
         at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:829) ~[spring-webmvc-6.1.3.jar:6.1.3]
         at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-6.1.3.jar:6.1.3]
         at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089) ~[spring-webmvc-6.1.3.jar:6.1.3]
         at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979) ~[spring-webmvc-6.1.3.jar:6.1.3]
         at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014) ~[spring-webmvc-6.1.3.jar:6.1.3]
         at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:914) ~[spring-webmvc-6.1.3.jar:6.1.3]
         at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:590) ~[tomcat-embed-core-10.1.18.jar:6.0]
         at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885) ~[spring-webmvc-6.1.3.jar:6.1.3]
         at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658) ~[tomcat-embed-core-10.1.18.jar:6.0]
         at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:205) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) ~[tomcat-embed-websocket-10.1.18.jar:10.1.18]
         at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:174) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-6.1.3.jar:6.1.3]
         at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.3.jar:6.1.3]
         at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:174) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-6.1.3.jar:6.1.3]
         at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.3.jar:6.1.3]
         at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:174) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.springframework.web.filter.ServerHttpObservationFilter.doFilterInternal(ServerHttpObservationFilter.java:109) ~[spring-web-6.1.3.jar:6.1.3]
         at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.3.jar:6.1.3]
         at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:174) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-6.1.3.jar:6.1.3]
         at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.3.jar:6.1.3]
         at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:174) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:340) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:391) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:896) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1744) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-10.1.18.jar:10.1.18]
         at java.base/java.lang.Thread.run(Thread.java:1583) ~[na:na]

When I am running the query without template placeholders, putting the values directly its working as expected, and no errors are thrown.

Postgres Driver 'org.postgresql:postgresql:42.3.8' Spring boot version 3.2.2

snicoll commented 5 months ago

NamedParameterJdbcTemplate isn't able to understand all subtleties of all dialects. That $$isn't SQL ANSI so you'll have to rely on regular ? placeholder for this.