mybatis批量更新遇到的坑

前言

实际上，我们经常会遇到这样的需求，那就是利用Mybatis批量更新或者批量插入（一般是sql语句有分号时有问题），但是，实际上即使Mybatis完美支持你的sql，你也得看看你操作的数据库是否支持。

---

问题

先带大家来看一段sql的配置，

<update id="updateState">
    <foreach collection="list" item="item" index="index"  separator=";">
      update wx_convert_detail
      <set>
        fahao=#{item.fahao},convert_time=#{item.convertTime},state=#{item.type},cause=#{item.reason}
      </set>
      WHERE id=#{item.id}
    </foreach>
  </update>

看似似乎没有一点问题，这里用到了Mybatis的动态sql，实际上说白了也就是拼sql，不过这个繁杂的工作交给Mybatis帮我们去做了。可是，只要一执行就要报语法错误。调试了好久，发现只要list的成员只有一个就没有问题。

解决方案

后来搜索发现，原来mysql的批量更新是要我们主动去设置的， 就是在数据库的连接url上设置一下，加上&allowMultiQueries=true 即可。当然这是因为我们更新多条记录多个字段为不同的值才需要修改，如果更改多条记录的同一字段是可以直接使用的。

jdbc.url= jdbc:log4jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true

---

扩展

上面这种批量更新相当耗效率，一条记录update一次，性能比较差，容易造成阻塞。

因为Mybatis映射文件中的sql语句不允许 ; 符号。按照可行的case when处理方式，也可以使用Mybatis映射文件书写方式如下：

<update id="updateState" parameterType="java.util.List">
    UPDATE wx_convert_detail SET
    fahao=
    <foreach collection="list" item="item" index="index" open="CASE id" close="END">
        WHEN #{item.id} THEN #{item.fahao}
    </foreach>
    ,convert_time=
    <foreach collection="list" item="item" index="index" open="CASE id" close="END">
        WHEN #{item.id} THEN #{item.convertTime}
    </foreach>
    ,state=
    <foreach collection="list" item="item" index="index" open="CASE id" close="END">
        WHEN #{item.id} THEN #{item.type}
    </foreach>
    ,cause=
    <foreach collection="list" item="item" index="index" open="CASE id" close="END">
        WHEN #{item.id} THEN #{item.reason}
    </foreach>
    WHERE id IN
    <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
        #{item.id}
    </foreach>
</update>

同时，实际的业务系统里面oracle数据库也用的非常的多，当然，oracle数据库不需要做特殊的配置，但是相应的sql要做变化，加上begin和end。

<update id="updateState">
    <foreach collection="list" item="item" index="index" open="begin" close="end;" separator=";">
      update wx_convert_detail
      <set>
        fahao=#{item.fahao},convert_time=#{item.convertTime},state=#{item.type},cause=#{item.reason}
      </set>
      WHERE id=#{item.id}
    </foreach>
  </update>

总结

有些时候，遍寻代码而无错也找不到问题的时候，不妨去找找系统环境的问题，说不定，就只在一瞬，问题迎刃而解。

关于 allowMultiQueries=true的原理可以去关注下这篇博文https://my.oschina.net/zhuguowei/blog/411853