SpringBoot+Mybatis实现批量更新

插如测试数据。

CREATE DEFINER=`root`@`localhost` PROCEDURE `add_user_optimizition`(in num INT)
BEGIN
        DECLARE rowid INT DEFAULT 0;
        DECLARE firstname CHAR(1);
        DECLARE name1 CHAR(1);
        DECLARE name2 CHAR(1);
        DECLARE lastname VARCHAR(3) DEFAULT '';
        DECLARE sex CHAR(1);
        DECLARE score CHAR(2);
        SET @exedata = "";
        WHILE rowid < num DO
            SET firstname = SUBSTRING('赵钱孙李周吴郑王林杨柳刘孙陈江阮侯邹高彭徐',FLOOR(1+21*RAND()),1); 
            SET name1 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1); 
            SET name2 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1); 
            SET sex=FLOOR(0 + (RAND() * 2));
            SET score= FLOOR(40 + (RAND() *60));
            SET rowid = rowid + 1;
            IF ROUND(RAND())=0 THEN 
            SET lastname =name1;
            END IF;
            IF ROUND(RAND())=1 THEN
            SET lastname = CONCAT(name1,name2);
            END IF;
            IF length(@exedata)>0 THEN
            SET @exedata = CONCAT(@exedata,',');
            END IF;
            SET @exedata=concat(@exedata,"('",firstname,"','",lastname,"','",sex,"','",score,"','",rowid,"')");
            IF rowid%1000=0
            THEN 
                SET @exesql =concat("insert into user100w_optimizition(first_name,last_name,sex,score,copy_id) values ", @exedata);
                prepare stmt from @exesql;
                execute stmt;
                DEALLOCATE prepare stmt;
                SET @exedata = "";
            END IF;
        END WHILE;
        IF length(@exedata)>0 
        THEN
            SET @exesql =concat("insert into user100w_optimizition(first_name,last_name,sex,score,copy_id) values ", @exedata);
            prepare stmt from @exesql;
            execute stmt;
            DEALLOCATE prepare stmt;
        END IF; 
    END

插入100w数据：

call add_user_optimizition(1000000)

controller层：

    @GetMapping("/batchUpdate")
    public int batchUpdate() {
        BigDataPojo dto=new BigDataPojo();
        dto.setScore(99);
        List<Integer> listIds=userService.listIds();
        dto.setIds(listIds);
        return userService.batchUpdate(dto);
    }

service层：

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    public List<Integer> listIds() {
        return userMapper.listIds();
    }

    public int batchUpdate(BigDataPojo dto) {
        return userMapper.batchUpdate(dto);
    }

}

Mapper层：

public interface UserMapper {

    List<Integer> listIds();
    int batchUpdate(BigDataPojo dt);

}

Mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.songguoliang.mybatis.mapper.UserMapper">

    <select id="listIds" resultType="java.lang.Integer">
        select id from user100w_optimizition u where u.first_name='杨'  order by id limit 1000;
    </select>

    <update id="batchUpdate" parameterType="com.songguoliang.mybatis.entity.BigDataPojo">
        update user100w_optimizition
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="score=case" suffix="end,">
                <foreach collection="ids" item="id" index="index">
                    <if test="id!=null">
                        when id=#{id} then #{score}
                    </if>
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="ids" separator="or" item="id" index="index">
            id=#{id}
        </foreach>
    </update>
</mapper>

实体类：

public class BigDataPojo {

    private Integer id;
    private String first_name;
    private String last_name;
    private String sex;
    private Integer score;
    private Integer copy_id;
    private String job;
    private List<Integer> ids;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getFirst_name() {
        return first_name;
    }

    public void setFirst_name(String first_name) {
        this.first_name = first_name;
    }

    public String getLast_name() {
        return last_name;
    }

    public void setLast_name(String last_name) {
        this.last_name = last_name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Integer getScore() {
        return score;
    }

    public void setScore(Integer score) {
        this.score = score;
    }

    public Integer getCopy_id() {
        return copy_id;
    }

    public void setCopy_id(Integer copy_id) {
        this.copy_id = copy_id;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }
}

参考博客： https://blog.csdn.net/yjaspire/article/details/81316885