-1

Getting SQLSyntaxErrorException while updating List of values in MyBatis. I am using separator=";" in forecach tag, but still I am getting the error. Below is my sql query

   <update id="updateComplianceCheckListResponse" parameterType="java.util.List">
        <foreach collection="list" item="response" separator=";">
            UPDATE AM_ComplianceChecklistResponse 
            SET complianceDetailsId     =   #{response.complianceDetailsId},
                complianceChecklistId   =   #{response.complianceChecklistId},
                questionResponse        =   #{response.questionResponse},
                expiryDate              =   #{response.expiryDate},
                documentName            =   #{response.documentName},
                comments                =   #{response.comments}
            WHERE id = #{response.id}
        </foreach>
    </update>

org.apache.ibatis.exceptions.PersistenceException:

Error updating database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE AM_ComplianceChecklistResponse

        SET complianceDetailsId     =   1,
            com' at line 10

The error may exist in com/am/dao/managecompliance/ComplianceChecklistResponse.xml

The error may involve ComplianceChecklistResponse.updateComplianceCheckListResponse-Inline

The error occurred while setting parameters

SQL: UPDATE AM_ComplianceChecklistResponse SET complianceDetailsId = ?, complianceChecklistId = ?, questionResponse = ?, expiryDate = ?, documentName = ?, comments = ? WHERE id = ? ; UPDATE AM_ComplianceChecklistResponse SET complianceDetailsId = ?, complianceChecklistId = ?, questionResponse = ?, expiryDate = ?, documentName = ?, comments = ? WHERE id = ?

Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE AM_ComplianceChecklistResponse

        SET complianceDetailsId     =   1,
            com' at line 10
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:196)
at com.am.dao.managecompliance.ComplianceChecklistResponseDAO.updateComplianceCheckListResponse(ComplianceChecklistResponseDAO.java:50)
at com.am.service.managecompliance.ComplianceChecklistResponseService.updateComplianceCheckListResponse(ComplianceChecklistResponseService.java:122)
at com.am.webservice.controller.managecompliance.ManageComplianceController.updateComplianceCheckListResponse(ManageComplianceController.java:230)
at com.am.webservice.controller.managecompliance.ManageComplianceController$$FastClassBySpringCGLIB$$e3100610.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy.invokeMethod(CglibAopProxy.java:386)
at org.springframework.aop.framework.CglibAopProxy.access$000(CglibAopProxy.java:85)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:704)
at com.am.webservice.controller.managecompliance.ManageComplianceController$$EnhancerBySpringCGLIB$$4a5751a4.updateComplianceCheckListResponse(<generated>)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
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:1071)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:964)
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:555)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:623)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:209)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:337)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:115)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:81)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:122)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:116)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:149)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346)
at com.dbp.security.auth.spring.StatelessAuthenticationFilter.doFilter(StatelessAuthenticationFilter.java:55)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346)
at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:90)
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:75)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:221)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:186)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:354)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:267)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:168)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:481)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:670)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:390)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:928)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1794)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
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.base/java.lang.Thread.run(Thread.java:833)

Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE AM_ComplianceChecklistResponse SET complianceDetailsId = 1, com' at line 10 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916) at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:568) at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) at jdk.proxy5/jdk.proxy5.$Proxy81.execute(Unknown Source) at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:194) ... 69 common frames omitted

Improve this question
3
  • That may work if you enable allowMultiQueries connection property. However, if there aren't many elements in the list, you should use INSERT ... ON DUPLICATE KEY UPDATE ... instead. And when there are many elements, you should use batch update. See these answers 1 2. Commented Jan 1, 2024 at 14:40
  • @ave if I am updating multiple rows (based on primary key) using foreach tag, is that necessary to use batch Commented Jan 1, 2024 at 19:11
  • As I wrote, it depends on the number of rows to update. When using foreach, MyBatis has to generate one big SQL String that contains many placeholders ?. So, this method increases the memory footprint and could cause performance issues. Also, in case of MySQL, the max number of placeholders is 65,536, so the max rows you can update using this method is 9,362. Batch is designed to process large number of rows efficiently. Commented Jan 1, 2024 at 20:21

1 Answer 1

Reset to default
0

mysql batch update can use this sql insert into [table] values… on duplicate key update

 <insert id="updateComplianceCheckListResponse" parameterType="java.util.List">
        insert into AM_ComplianceChecklistResponse
        (id,complianceDetailsId,complianceChecklistId,questionResponse,expiryDate,documentName,comments) 
        VALUES
        <foreach collection="list" item="response" separator="," open="(" close=")">
            #{response.id}, #{response.complianceDetailsId},#{response.complianceChecklistId},#{response.questionResponse},#{response.expiryDate},#{response.documentName},#{response.comments}
        </foreach>
        on duplicate key update
        complianceDetailsId= VALUES(complianceDetailsId),
        complianceChecklistId=VALUES(complianceChecklistId),
        questionResponse=VALUES(questionResponse),
        expiryDate=VALUES(expiryDate),
        documentName=VALUES(documentName),
        comments=VALUES(comments)
</insert>
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.