按数组内容及位置更新数据库表

在进行业务开发时，遇见一个需求，从前端返回一个list的ids，要按照ids去从数据库中查找，然后将它的列属性-优先级更新为该id在ids的位置。

例如：ids=[3, 6 ,2]，则将id为3、6、2的条目的优先级列设置成对应位置的1、2、3。

同时不能在代码中有循环，只能与数据库有一次连接。这也就意味着只能把循环写在mapper.xml中。然后就有了以下两种解决方案：

一次发多条sql语句

     这种方法就是在一个<update>标签内对update的sql使用<foreach>，如下：

<update id="updatePriorityByOrderMultiQueries" parameterType="java.util.List">
     <foreach collection="ids" item="item" index="index" separator=";">
        UPDATE table
        SET strategy = #{index} + 1
        WHERE id = #{item}
     </foreach>
</update>

这样每次循环都构建一个update，然后一下发送出去。问题解决~可是在测试的时候发现总是报错，但是发送的sql语句在Navicat里直接执行都是ok的。后来发现，原来是jdbc驱动默认不支持多条sql语句，需要在连接的url后面加上&allowMultiQueries=true，也就是需要这么写：

jdbc.url = jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true

 兴致冲冲地赶紧去试了试，发现项目中的mybtis配置中已经打开了。。又去新建一个工程，尝试发现是完全行得通的。

（经过一下午的努力，终于找到问题所在了。原因就是我们项目中用的mysql-connector版本是5.1.36，而我自己创建的项目用的版本是5.1.6。 5.1.36这个版本还不支持多条sql执行，也就是不支持参数allowMultiQueries=true，所以设置等于没设置，但是它也不告诉你这个没生效，真是大坑！）

      行吧，上面的问题暂时告一段落。但sql语句还没算结束，虽然目的是达到了，但在实际中，这样写很是问题。因为虽然是没在java的循环中完成，只是省去了每次连接数据库的时间，但发送的多条sql语句每条语句都会把数据库遍历一遍，明明可以一次遍历就能完成的事情，这性能也太低了。于是乎就有了第二种方法。

Join +  Union方法

 这种方法首先把查询的条件和需要修改的结果构造成一张表（假设叫update_data）。比如对于上例中，需要构造如下一张表：

id	strategy_priority
3	1
6	2
2	3

  然后将原表与update_data表根据id字段进行Join（内连接），对于匹配到的列，设置原表的列值为update_data表的列值即可达到更新的效果。修改后的<update>标签为：   

<update id="updatePriorityByOrder" parameterType="java.util.List">
    UPDATE `table` AS `raw_table` JOIN
    <foreach collection="ids" item="item" index="index" open="(" close=")" separator="UNION">
        SELECT ${item} AS `id`, ${index} + 1 AS `priority`
    </foreach>
    AS `update_data` USING(id)
    SET `raw_table`.`priority` = `update_data`.`priority`
</update>

使用这种方法不仅美观简洁，同时对于性能有大大提升！有实测不管是少记录更新还是多记录更新的平均耗时都比第一种方法少耗时将近一半！