mybatis - 通过3种方式 批量更新

1. 通过foreach批量更新
2. 通过case when批量更新(100条级别使用，批量更新数据太多会适得其反)
3. 通过ExecutorType.Batch批量更新

---

通过foreach批量更新

## mysql 
update table set id=1,name="泽虞生" where id = 1;
update table set id=2,name="泽虞生" where id = 2;
update table set id=3,name="泽虞生" where id = 3;
...

## mybaits
<foreach collection="list" item="i" index="index" open="" close="" separator=";">
     UPDATE table
     <trim prefix="set" suffixOverrides=",">
         <if test="i.id!=null">id= #{i.id}</if>
         <if test="i.name!=null">name= #{i.name}</if>
      </trim>
      WHERE id= #{i.id} and is_deleted = 0
</foreach>

tk.mybatis 扩展  注：需要有主键

## mybatis extend -- tk.mybatis
# 设置通用Mapper 使用@RegisterMapper注解到接口上

@UpdateProvider(type = XXXProvider.class, method = "dynamicSQL")
int updateBatchByForeach(List<? extends T> recordList);

## 设置自定义provider 继承 ExampleProvider

public String updateBatchByForeach(MappedStatement ms) {
        final Class<?> entityClass = getEntityClass(ms);
        //开始拼sql
        StringBuilder sql = new StringBuilder();
        sql.append("<foreach collection=\"list\" item=\"i\" index=\"index\" open=\"\" close=\"\" separator=\";\"> ");
        sql.append(SqlHelper.updateTable(entityClass, tableName(entityClass)));
        sql.append("<trim prefix=\"set\" suffixOverrides=\",\">");
        // 获取主键
        Set<EntityColumn> pkColumnSet = EntityHelper.getPKColumns(entityClass);
        EntityColumn entityColumn = Lists.newArrayList(pkColumnSet).get(0);
        String pkColumn = entityColumn.getColumn();
        String pkProperty = entityColumn.getProperty();

        //获取全部列
        Set<EntityColumn> columnList = EntityHelper.getColumns(entityClass);
        for (EntityColumn column : columnList) {
            sql.append("<if test=\"i.").append(column.getEntityField().getName()).append("!=null\"> ");
            sql.append(column.getColumn()).append(" = #{i.").append(column.getProperty()).append("},");
            sql.append("</if>");
        }
        sql.append(" </trim> ");
        sql.append(" WHERE ").append(pkColumn).append(" = ").append("#{i.").append(pkProperty).append("}").append(" and is_deleted = 0 ");
        sql.append(" </foreach> ");

        return sql.toString();
    }

---

---

通过case when批量更新

## mysql 
update table set
id = case 
	when id = 1 then 1 
	when id = 2 then 2
	when id = 3 then 3 end,
name = case
	when id = 1 then '泽虞生1'
	when id = 2 then '泽虞生2'
	when id = 3 then '泽虞生3' end,
where id in (1,2,3)
...

## mybaits
UPDATE sms_send_callback
<trim prefix="set" suffixOverrides=",">
      <trim prefix="id=case" suffix="end,">
            <foreach collection="list" item="i" index="index">
                    <if test="i.id!=null">
                        when id=#{i.id} then #{i.id}    
                    </if>
            </foreach>
      </trim>
      <trim prefix="name=case" suffix="end,">
            <foreach collection="list" item="i" index="index">
                    <if test="i.name!=null">
                        when id=#{i.id} then #{i.name}
                    </if>
             </foreach>
      </trim>
</trim>
WHERE id IN
<trim prefix="(" suffix=")">
     <foreach collection="list" separator=", " item="i" index="index">
         #{i.id}
     </foreach>
</trim>

tk.mybatis 扩展  注：需要有主键

## mybatis extend -- tk.mybatis
public String updateBatchByCaseWhen(MappedStatement ms) {
        final Class<?> entityClass = getEntityClass(ms);
        //开始拼sql
        StringBuilder sql = new StringBuilder();
        sql.append(SqlHelper.updateTable(entityClass, tableName(entityClass)));
        sql.append("<trim prefix=\"set\" suffixOverrides=\",\">");
        // 获取主键
        Set<EntityColumn> pkColumnSet = EntityHelper.getPKColumns(entityClass);
        EntityColumn entityColumn = Lists.newArrayList(pkColumnSet).get(0);
        String pkColumn = entityColumn.getColumn();
        String pkProperty = entityColumn.getProperty();

        //获取全部列
        Set<EntityColumn> columnList = EntityHelper.getColumns(entityClass);
        for (EntityColumn column : columnList) {
            if (!column.isId() && column.isUpdatable()) {
                sql.append("  <trim prefix=\"").append(column.getColumn()).append(" =case\" suffix=\"end,\">");
                sql.append("    <foreach collection=\"list\" item=\"i\" index=\"index\">");
                sql.append("      <if test=\"i.").append(column.getEntityField().getName()).append("!=null\">");
                sql.append("         when ").append(pkColumn).append("=#{i.").append(pkProperty).append("} then #{i." + column.getEntityField().getName() + "}");
                sql.append("      </if>");
                sql.append("    </foreach>");
                sql.append("  </trim>");
            }
        }
        sql.append("</trim>");
        sql.append("WHERE ");
        sql.append(pkColumn).append(" IN ");
        sql.append("<trim prefix=\"(\" suffix=\")\">");
        sql.append("<foreach collection=\"list\" separator=\", \" item=\"i\" index=\"index\" >");
        sql.append("#{i.").append(pkProperty).append("}");
        sql.append("</foreach>");
        sql.append("</trim>");

        return sql.toString();
    }

---

通过ExecutorType.Batch批量更新

@Autowired
private SqlSessionFactory sqlSessionFactory;

public void updateBatchByExecutorType(){
        // 初始化更新数据
        List<Table> tables = initUpdateBatchData(1000, "批量手动更新数据");
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        TableMapper mapper = sqlSession.getMapper(TableMapper.class);
        int i = 0;
        for (Table table : tables){
            mapper.updateByPrimaryKeySelective(table);
            // 每100条执行一次
            if ((i + 1) % 100 == 0){
                sqlSession.flushStatements();
            }
            i++;
        }
        sqlSession.flushStatements();
}