Mybatis 批量更新数据

Mybatis 批量更新数据

方法一：使用when…then + foreach对我们list数组进行遍历然后给对于id更新对于的厂家信息,优点在不改变数据库连接参数allowMultiQueries下可完成批量更新,缺点阅读性较差,性能较差

<update id="updateByList" parameterType="list">
   	update power_device
    set  name=
    <foreach collection="list" item="item" index="index"
             separator=" " open="case" close="end">
        when id= #{item.id} then #{item.name}
    </foreach>
    ,code=
    <foreach collection="list" item="item" index="index"
             separator=" " open="case" close="end">
        when id= #{item.id} then #{item.code}
    </foreach>
    where id in
    <foreach collection="list" index="index" item="item"
             separator="," open="(" close=")">
        #{item.id}
    </foreach>
</update>

上面的mapper换成sql

UPDATE power_device 
SET manufacturer = 
CASE 
WHEN id=1176700809508212736 THEN 'name'
WHEN id=1176700813236948992 THEN 'name'
WHEN id=1176700816571420672 THEN 'name'
WHEN id=1176700819826200576 THEN 'name'
WHEN id=1176700822942568448 THEN 'name'
END 
CASE 
WHEN id=1176700809508212736 THEN 'code'
WHEN id=1176700813236948992 THEN 'code'
WHEN id=1176700816571420672 THEN 'code'
WHEN id=1176700819826200576 THEN 'code'
WHEN id=1176700822942568448 THEN 'code'
END 
WHERE
	id IN ( '1176700809508212736', '1176700813236948992', '1176700816571420672', '1176700819826200576', '1176700822942568448' )

方法二：使用foreach生成批量update语句使用逗号分割，这种方法有一个坑就是数据库url后必须加上allowMultiQueries=true，允许多重sql操作不然就会报错，优点只用foreach循环阅读和理解性很好，重要的事情要说3次有坑数据库连接参数必须加上allowMultiQueries=true

<update id="updateList">
        <foreach collection="list" item="item" index="index" open="" close="" separator="">
            UPDATE test
            <set>
                <if test="item.companyCode != null" >
                    name= #{item.name},
                </if>
                <if test="item.demandProposeTime != null" >
                    code= #{item.code},
                </if>
                <if test="item.updateBy != null" >
                    update_by = #{item.updateBy},
                </if>
                    update_date = now(),
            </set>
            where id = #{item.id};
        </foreach>
    </update>

上面的mapper换成sql

UPDATE test
name= ?,
code= ?,
update_by = ?,
update_date = now()
where id = ?;
UPDATE test
name= ?,
code= ?,
update_by = ?,
update_date = now()
where id = ?;

使用方法二数据库连接需要追加allowMultiQueries=true的参数

url: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8&allowMultiQueries=true