今天在使用mybatis批量更新时,按照教程写了以下代码
<update id="updateUser" parameterType="List">
<foreach collection="list" item="item" index="index" separator=";">
UPDATE im_user
<set>
easemob_user_name = #{item.easemobUserName},
easemob_password = #{item.easemobPassword}
</set>
WHERE id = #{item.userId}
</foreach>
</update>
运行时,总是报错,把错误提示的sql语句拷出来放在mysql上确能运行。
错误日志:
UPDATE im_user SET easemob_user_name = ?, easemob_password = ? WHERE id = ? ; UPDATE im_user SET easemob_user_name = ?, easemob_password = ? WHERE id = ? ;
这就很奇怪了,后来发现是数据库连接默认不支持多条语句执行,解决方法是在jdbc连接时显示的加上:&allowMultiQueries=true
如:
jdbc.url=jdbc:mysql://127.0.0.1:3306/bbs?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
还有另外一个语句可以避开这个问题,就是插入时更新。语句如下:
<update id="updateUser2" parameterType="List">
insert into im_user(id, easemob_user_name, easemob_password)
values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.userId}, #{item.easemobUserName}, #{item.easemobPassword})
</foreach>
on DUPLICATE key update easemob_user_name = values(easemob_user_name), easemob_password = values(easemob_password)
</update>
这样在插入时更新已经存在的。