使用mybatis批量插入数据

最近在做项目的时候，有些明细数据，一条一条的插入太费资源和时间，所以得需要批量插入，今晚闲来无事写个小demo。

新建工程

    <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.3.1</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

工程目录

application.properties配置

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/dblog
spring.datasource.username=root
spring.datasource.password=666666
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
mybatis.mapper-locations=classpath:config/*Mapper.xml
mybatis.type-aliases-package=com.zy.entity

teacher表结构

mybatis语句

<insert id="insertTeacher">
        insert into teacher (name, age, sex) values
        <foreach collection="list" separator="," item="item">
            (#{item.name}, #{item.age}, #{item.sex})
        </foreach>
    </insert>

接口定义

@Repository
public interface TeacherMapper {
    public int insertTeacher(List<Teacher> teacher);
}

测试代码

@RestController
public class MybatisController {

    @Autowired
    public TeacherMapper teacherMapper;


    @GetMapping("teachers")
    public String teachers() {
        Long a = System.currentTimeMillis();
        for (int t=0;t<100;t++) {
            List<Teacher> teachers = new ArrayList<>();
            for (int n=0;n<1000;n++) {
                Teacher teacher = new Teacher();
                teacher.setName("李继"+t+""+n);
                teacher.setAge(35);
                teacher.setSex("男");
                teachers.add(teacher);
            }
            teacherMapper.insertTeacher(teachers);
        }
        long s = (System.currentTimeMillis()-a)/1000;
        System.out.println(s);
        return "success:"+s;
    }

}

启动类

@SpringBootApplication
@MapperScan(basePackages = "com.zy.dao", annotationClass = Repository.class)
public class MybatisDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(MybatisDemoApplication.class, args);
    }

}

测试结果

插入1万条数据用时1秒，插入10万条数据用时10秒。