### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL

╰半夏微凉° 2022-12-28 07:46 113阅读 0赞

# 前言 #

最近项目使用批处理更新报错：

--------------------

# 错误信息如下： #

Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 phb_collect
           SET state = 1,
            update_time = '2020-12-15 16:19' at line 7
    ### The error may exist in file 
    ### The error may involve com.xxxx### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 phb_collect
    .phb.mapper.PhbCollectMapper.updateBatch-Inline
    ### The error occurred while setting parameters
    ### SQL: update phb_collect        SET state = ?,         update_time = ?,         update_user = ?        WHERE id = ?      ;        update phb_collect        SET state = ?,         update_time = ?,         update_user = ?        WHERE id = ?      ;        update phb_collect        SET state = ?,         update_time = ?,         update_user = ?        WHERE id = ?      ;        update phb_collect        SET state = ?,         update_time = ?,         update_user = ?        WHERE id = ?
    ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 phb_collect
           SET state = 1,
            update_time = '2020-12-15 16:19' at line 7
    ; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 phb_collect
           SET state = 1,
            update_time = '2020-12-15 16:19' at line 7
    	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:234)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74)
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
    	at com.sun.proxy.$Proxy54.update(Unknown Source)
    	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:287)
    	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:67)
    	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:57)
    	at com.sun.proxy.$Proxy62.updateBatch(Unknown Source)
    	at com.xxxx.phb.service.impl.HomeServiceImpl.delete(HomeServiceImpl.java:84)
    	at com.xxxx.phb.service.impl.HomeServiceImpl$$FastClassBySpringCGLIB$$37ada9fb.invoke(<generated>)
    	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295)
    	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
    	at com.xxxx.phb.service.impl.HomeServiceImpl$$EnhancerBySpringCGLIB$$64bf9c1a.delete(<generated>)
    	at com.xxxx.phb.controller.HomeController.delete(HomeController.java:42)
    	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)

# 解决办法： #

<update id="updateBatch" parameterType="list" keyProperty="id" useGeneratedKeys="true">
        <foreach collection="list" index="index" item="item" open="" separator=";" close="">
          update phb_collect
          <set>
            state =#{ item.state,jdbcType=INTEGER},
            update_time=#{ item.updateTime,jdbcType=TIMESTAMP},
            update_user=#{ item.updateUser,jdbcType=VARCHAR}
          </set>
          WHERE id = #{ item.id,jdbcType=INTEGER}
        </foreach>
      </update>

需要数据库连接地址后面添加：

&allowMultiQueries=true