mybatis 批量新增批量修改_mybatis批量新增-CSDN博客

本文链接：https://blog.csdn.net/dream_snow2012/article/details/127108062

该博客展示了如何利用Hutool工具库将List数据批量插入和更新到数据库中。代码示例中，首先创建了一批SysUserDTO对象，然后通过CollUtil.splitList方法将大列表分割成小块，每块大小为100。对于插入操作，使用MyBatis的insertBatch方法处理每个子列表；对于更新操作，利用insertBatch配合数据库的ON DUPLICATE KEY UPDATE语句实现。这种方法可以有效地提高数据库操作的效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

使用hutool分割list

import cn.hutool.core.collection.CollUtil;

public ReturnT<Integer> batchInsert() {
        List<SysUserDTO> list = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            SysUserDTO sysUserDTO = new SysUserDTO();
            sysUserDTO.setUsername("admin" + i);
            sysUserDTO.setPassword("12345" + i);
            sysUserDTO.setUserAccount("admin" + i);
            sysUserDTO.setEmail("admin" + i + "@163.com");
            sysUserDTO.setCreateBy("admin");
            list.add(sysUserDTO);
        }
        int size = 100;
        int total = 0;
        List<List<SysUserDTO>> lists = CollUtil.splitList(list, size);
        for (List<SysUserDTO> sysUserDTOS : lists) {
            int rows = baseMapper.insertBatch(sysUserDTOS);
            total += rows;
        }
        return new ReturnT<>(total);
    }

    public ReturnT<Integer> updateBatch() {
        List<SysUserDTO> list = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            SysUserDTO sysUserDTO = new SysUserDTO();
            sysUserDTO.setUsername("admin200" + i);
            sysUserDTO.setPassword("12345" + i);
            sysUserDTO.setUserAccount("admin" + i);
            sysUserDTO.setEmail("admin" + i + "@163.com");
            sysUserDTO.setCreateBy("admin");
            list.add(sysUserDTO);
        }
        int size = 100;
        int total = 0;
        List<List<SysUserDTO>> lists = CollUtil.splitList(list, size);
        for (List<SysUserDTO> sysUserDTOS : lists) {
            int rows = baseMapper.updateBatch(sysUserDTOS);
            total += rows;
        }
        return new ReturnT<>(total);
    }

List<List<SysUserDTO>> lists = CollUtil.splitList(list, size);

1. 批量新增

<insert id="insertBatch">
        INSERT INTO `sys_user`(`username`, `password`, `user_account`, `email`, `salt`, `phone`, `create_time`,
        `create_by`)
        VALUES
        <foreach collection="list" item="sysUser" separator=",">
            (#{sysUser.username}, #{sysUser.password}, #{sysUser.userAccount}
            ,#{sysUser.email},#{sysUser.salt},#{sysUser.phone}
            ,now(),#{sysUser.createBy}
            )
        </foreach>
    </insert>

2. 批量更新

需要建唯一索引

<insert id="updateBatch">
        INSERT INTO `sys_user`(`username`, `password`, `user_account`, `email`, `salt`, `phone`, `create_time`,
        `create_by`)
        VALUES
        <foreach collection="list" item="sysUser" separator=",">
            (#{sysUser.username}, #{sysUser.password}, #{sysUser.userAccount}
            ,#{sysUser.email},#{sysUser.salt},#{sysUser.phone}
            ,now(),#{sysUser.createBy}
            )
        </foreach>
        ON DUPLICATE KEY UPDATE
        id=VALUES(id),username = VALUES(username),password = VALUES(password),user_account = VALUES(user_account),
        email = VALUES(email),salt = VALUES(salt),phone = VALUES(phone),
        create_time = VALUES(create_time),create_by = VALUES(create_by)
    </insert>