项目场景:
SpringBoot + Mybatis 项目
问题描述:
Mybatis批量更新、修改 问题SQL语法错误;
异常信息:(sql语句单独放在navicat中是可以成功执行的)
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 weibo_content
SET
EMOTION = 0.0
W' 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 weibo_content
原因分析:
(1)一次执行多条SQL语句,是指SQL本身并无语法错误,批量执行更新语句时报错
<update id="batchUpdate" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update test
<set>
test=${item.test}+1
</set>
where id = ${item.id}
</foreach>
</update>
(2)把集合里的对象的 字段 都改为相同的值 这种不会报错,因为生成的SQL语句是一条语句
<update id="updateBatch" parameterType="Map">
update aa set
a=#{fptm},
b=#{csoftrain}
where c in
<foreach collection="cs" index="index" item="item" pen="("separator=","close=")">
#{item}
</foreach>
</update>
解决方案:
修改数据库连接配置:&allowMultiQueries=true
新增这条语句。
这条语句允许一次提交多条sql语句。
例如:jdbc.url=jdbc\:mysql\://localhost\:13506/command?serverTimezone=GMT%2b8&useUnicode\=true&characterEncoding\=UTF-8&useSSL\=false&allowMultiQueries=true