java批量插入10万条数据

---

前言

批量插入10万条数据，使用Mybatis以及MybatisPlus两种方式做耗时对比。

---

一、引入依赖

<dependency>
   <groupId>com.baomidou</groupId>
   <artifactId>mybatis-plus-boot-starter</artifactId>
   <version>3.5.1</version>
</dependency>

二、测试代码

准备实体类：User

@Data
@TableName("user")
public class User {
    private String id;
    private String name;
    private Integer age;
    private String address;
}

准备数据库表：user

CREATE TABLE `user` (
  `id` varchar(100) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

1.UserController.java代码

@RestController
@RequiredArgsConstructor
@RequestMapping("/api/user")
public class UserController {
    private final UserService userService;
    @PostMapping("/batchInsert")
    public Result<String> batchInsert(@RequestParam("count") Integer count) {
        long startTime = System.currentTimeMillis(); // 统计开始时间
        List<User> list = new ArrayList<>();
        for (int i = 0; i < count; i++) {
            User user = new User();
            user.setName("张三" + i);
            user.setAge(i);
            user.setAddress("上海" + i);
            list.add(user);
        }
        userService.saveBatch(list);
        long endTime = System.currentTimeMillis(); // 统计结束时间
        return new Result<>(Constant.SUCCESS_CODE, Constant.SUCCESS_MSG, "批量插入条数据总耗时：" + (endTime - startTime) + "ms");
    }

    @PostMapping("/batchInsert2")
    public Result<String> batchInsert2(@RequestParam("count") Integer count) {
        long startTime = System.currentTimeMillis(); // 统计开始时间
        List<User> users = new ArrayList<>();
        for (int i = 0; i < count; i++) {
            User user = new User();
            user.setName("张三" + i);
            user.setAge(i);
            user.setAddress("上海" + i);
            users.add(user);
        }
        userService.saveBatchInsert(users);
        long endTime = System.currentTimeMillis(); // 统计结束时间
        return new Result<>(Constant.SUCCESS_CODE, Constant.SUCCESS_MSG, "批量插入条数据总耗时：" + (endTime - startTime) + "ms");
    }
}

2.UserService.java代码

@Service
@Slf4j
@RequiredArgsConstructor
public class UserService extends ServiceImpl<UserMapper, User> {

    private final UserMapper userMapper;
    private static final int BATCH_SIZE = 1000;

    public void saveBatchInsert(List<User> users) {
        int totalUsers = users.size();
        for (int i = 0; i < totalUsers; i += BATCH_SIZE) {
            int end = Math.min(totalUsers, i + BATCH_SIZE);
            List<User> batchList = users.subList(i, end);
            userMapper.saveBatchUsers(batchList);
        }
    }
}

3.UserMapper.java代码

@Mapper
public interface UserMapper extends BaseMapper<User> {

    void saveBatchUsers(@Param("users") List<User> users);
}

4.UserMapper.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.pro.server.mapper.UserMapper">

    <insert id="saveBatchUsers" parameterType="java.util.List">
        INSERT INTO user (name, age,address)
        VALUES
        <foreach collection="users" item="user" separator=",">
            (#{user.name}, #{user.age}, #{user.address})
        </foreach>
    </insert>
</mapper>

三、测试结果

---

测试使用工具：ApiFox
1、使用MybatisPlus的saveBatch方法（耗时2809ms）

2、使用Mybatis的动态标签foreach方法（耗时4562ms）