mybatis 批量更新update

 

该篇博客主要涉及 更新多条记录为多个字段为不同的值

这种批量更新主要用一下两种方式：

1.组装多个update语句，这种方式需要设置jdbc连接  allowMultiQueries=true

1.1 数据库链接配置

请查看demo 中的sql语句

1.2 请看本次demo的sql语句

<update id="updateVersionAndGroup" parameterType="java.util.List">     <foreach collection="list" item="value" index="key" open="" close="" separator=";">         UPDATE tb_system_setting         <set>             item_value = #{value.itemValue}         </set>         <where>             item_name = #{value.itemName}         </where>     </foreach> </update>

2.case when 语句

<update id="updateVersionAndGroupCaseWhen" parameterType="java.util.List">     update tb_system_setting     <trim prefix="set" suffixOverrides=",">         <trim prefix="item_value=case" suffix="end,">             <foreach collection="list" item="item" index="index">                 when item_name=#{item.itemName} then #{item.itemValue}             </foreach>         </trim>     </trim>     where item_name in     <foreach collection="list" index="index" item="item" separator="," open="(" close=")">         #{item.itemName}     </foreach> </update>

实测性能：

 1 单条测试:

请看测试代码：

@Resource     private SystemSettingMapper systemSettingMapper;     @Test     public void updateGroupAndVersion() {         List<SystemSetting> list = new ArrayList<>();         SystemSetting srvVersion1 = new SystemSetting("srvVersion", "1");         SystemSetting group = new SystemSetting("group", "12345678");         list.add(srvVersion1);         list.add(group);         int result = 0;         String start = LocalDateTime.now().toString();         System.err.println("=====================开启了啊，哈哈哈=====================" + start); //      result = this.systemSettingMapper.updateVersionAndGroupCaseWhen(list);         result = this.systemSettingMapper.updateVersionAndGroup(list);         System.err.println("=====================" + result + "=====================");         System.err.println("=====================结束了啊=====================\tstart\t" + start + "\tend\t" + LocalDateTime.now().toString());     }

第一种：

 

可以看到时间相差为 31毫秒，现在咱们看看一百条数据量的差别，循环一百次查看性能：

代码：

@RunWith(SpringRunner.class) @SpringBootTest public class UpdateGroupAndVersion {     @Resource     private SystemSettingMapper systemSettingMapper;     @Test     public void updateGroupAndVersion() {         List<SystemSetting> list = new ArrayList<>();         SystemSetting srvVersion1 = new SystemSetting("srvVersion", "1");         SystemSetting group = new SystemSetting("group", "12345678");         list.add(srvVersion1);         list.add(group);         int result = 0;         String start = LocalDateTime.now().toString();         System.err.println("=====================开启了啊，哈哈哈=====================" + start);         for (int i = 0; i < 100; i++) { //            result = this.systemSettingMapper.updateVersionAndGroupCaseWhen(list);             result = this.systemSettingMapper.updateVersionAndGroup(list);         }         System.err.println("=====================" + result + "=====================");         System.err.println("=====================结束了啊=====================\tstart\t" + start + "\tend\t" + LocalDateTime.now().toString());     } }