mybatis 批量更新运行异常, 数据库 postgres

CixHn 2019-06-12 11:25:40

Mapper配置:
<update id="cancelImport" parameterType="java.util.List">
update fm_attach_detail attach
<set>
remark = fromData.remark,
valid = fromData.valid,
modify_user = fromData.modifyUser::INTEGER
</set>
from (values
<foreach collection="list" item="item" index="index" open="" close="" separator=",">
<trim prefix="(" suffix=")">
#{item.id}, #{item.refId},#{item.remark},#{item.modifyUser},#{item.valid}
</trim>
</foreach>
) as fromData(id, refId, remark, modifyUser, valid)
<where>
attach.valid = '0' AND fromData.id::INTEGER = attach.id AND attach.ref_id= fromData.refId
</where>
</update>

异常信息:
org.springframework.jdbc.UncategorizedSQLException:
### Error updating database. Cause: java.sql.SQLException: sql injection violation, syntax error: error pos 235, line 3, column 98, token COMMA : update fm_attach_detail attach
SET remark = fromData.remark, valid = fromData.valid, modify_user = fromData.modifyUser::INTEGER
from (values ( ?, ?,?,?,? ) , ( ?, ?,?,?,? ) , ( ?, ?,?,?,? ) ) as fromData(id, refId, remark, modifyUser, valid)
WHERE attach.valid = '0' AND fromData.id::INTEGER = attach.id AND attach.ref_id= fromData.refId
### SQL: update fm_attach_detail attach SET remark = fromData.remark, valid = fromData.valid, modify_user = fromData.modifyUser::INTEGER from (values ( ?, ?,?,?,? ) , ( ?, ?,?,?,? ) , ( ?, ?,?,?,? ) ) as fromData(id, refId, remark, modifyUser, valid) WHERE attach.valid = '0' AND fromData.id::INTEGER = attach.id AND attach.ref_id= fromData.refId
### Cause: java.sql.SQLException: sql injection violation, syntax error: error pos 235, line 3, column 98, token COMMA : update fm_attach_detail attach
SET remark = fromData.remark, valid = fromData.valid, modify_user = fromData.modifyUser::INTEGER
from (values ( ?, ?,?,?,? ) , ( ?, ?,?,?,? ) , ( ?, ?,?,?,? ) ) as fromData(id, refId, remark, modifyUser, valid)
WHERE attach.valid = '0' AND fromData.id::INTEGER = attach.id AND attach.ref_id= fromData.refId
; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; sql injection violation, syntax error: error pos 235, line 3, column 98, token COMMA : update fm_attach_detail attach
SET remark = fromData.remark, valid = fromData.valid, modify_user = fromData.modifyUser::INTEGER
from (values ( ?, ?,?,?,? ) , ( ?, ?,?,?,? ) , ( ?, ?,?,?,? ) ) as fromData(id, refId, remark, modifyUser, valid)
WHERE attach.valid = '0' AND fromData.id::INTEGER = attach.id AND attach.ref_id= fromData.refId; nested exception is java.sql.SQLException: sql injection violation, syntax error: error pos 235, line 3, column 98, token COMMA : update fm_attach_detail attach
SET remark = fromData.remark, valid = fromData.valid, modify_user = fromData.modifyUser::INTEGER
from (values ( ?, ?,?,?,? ) , ( ?, ?,?,?,? ) , ( ?, ?,?,?,? ) ) as fromData(id, refId, remark, modifyUser, valid)
WHERE attach.valid = '0' AND fromData.id::INTEGER = attach.id AND attach.ref_id= fromData.refId
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:90)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy99.update(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy111.cancelImport(Unknown Source)
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.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy112.cancelImport(Unknown Source)
at com.tuns.ins.business.fm.impl.AttachDetailServiceImpl.batchUpdateData(AttachDetailServiceImpl.java:103)
at com.tuns.ins.business.fm.impl.AttachDetailServiceImpl$$FastClassBySpringCGLIB$$f4ee1105.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:669)
at com.tuns.ins.business.fm.impl.AttachDetailServiceImpl$$EnhancerBySpringCGLIB$$ec0bee9c.batchUpdateData(<generated>)
at com.tuns.ins.webgate.fm.ReceiveWebgate.cancelKnotBackImport(ReceiveWebgate.java:125)
at com.tuns.ins.webgate.fm.ReceiveWebgate$$FastClassBySpringCGLIB$$29183179.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
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.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673)
at com.tuns.ins.webgate.fm.ReceiveWebgate$$EnhancerBySpringCGLIB$$4a7f085e.cancelKnotBackImport(<generated>)
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:133)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

...全文