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