[简单实验] mybatis如何快速插入1W条数据?

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第1天，点击查看活动详情

1. 使用 ReuseExecutor 执行器

public static void main(String[] args) throws IOException {
        // 读取配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        //构建工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        // 创建sqlsession对象
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.REUSE,true);
        // 获取接口代理对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        //清空表
        userMapper.deleteAll();

        //准备一万个数据
        ArrayList<User> arrayList = new ArrayList<>();

        for (int i = 0; i < 10000; i++) {
            User user = new User();
            user.setId(100+i);
            user.setName(i+"号");
            arrayList.add(user);
        }

        Long start = System.currentTimeMillis();
        arrayList.stream().forEach(user -> {userMapper.save(user);});
        System.out.printf("程序运行总耗时 : %s",System.currentTimeMillis()-start);
    }

程序最终耗时 : 12514 毫秒

2. 使用 SimpleExecutor 执行器

package com.sz;

import com.sz.mapper.UserMapper;
import com.sz.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.jdbc.SqlBuilder;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @author sz
 * @DATE 2022/7/31  15:00
 */
public class MybatisDemo {

    public static void main(String[] args) throws IOException {
        // 读取配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        //构建工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        // 创建sqlsession对象
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.SIMPLE,true);
        // 获取接口代理对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        //清空表
        userMapper.deleteAll();

        //准备一万个数据
        ArrayList<User> arrayList = new ArrayList<>();

        for (int i = 0; i < 10000; i++) {
            User user = new User();
            user.setId(100+i);
            user.setName(i+"号");
            arrayList.add(user);
        }

        Long start = System.currentTimeMillis();
        arrayList.stream().forEach(user -> {userMapper.save(user);});
        System.out.printf("程序运行总耗时 : %s",System.currentTimeMillis()-start);
    }

}

程序最终耗时 : 13954毫秒

3. 使用 BatchExecutor 执行器

package com.sz;

import com.sz.mapper.UserMapper;
import com.sz.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.jdbc.SqlBuilder;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @author sz
 * @DATE 2022/7/31  15:00
 */
public class MybatisDemo {

    public static void main(String[] args) throws IOException {
        // 读取配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        //构建工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        // 创建sqlsession对象
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,true);
        // 获取接口代理对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        //清空表
        userMapper.deleteAll();

        //准备一万个数据
        ArrayList<User> arrayList = new ArrayList<>();

        for (int i = 0; i < 10000; i++) {
            User user = new User();
            user.setId(100+i);
            user.setName(i+"号");
            arrayList.add(user);
        }

        Long start = System.currentTimeMillis();
        arrayList.stream().forEach(user -> {userMapper.save(user);});
        System.out.printf("程序运行总耗时 : %s",System.currentTimeMillis()-start);
    }

}

程序最终耗时 205 毫秒

4. 使用 动态SQL 批量插入数据

    <insert id="saveList">
        insert into  users values
             <foreach collection="userList" item="user" open="(" separator="," close=")">
                 #{user.id},#{user.name}
             </foreach>
    </insert>

程序最终耗时 402 秒

5. 最终结论

使用 BatchExecutor 执行器效率 与 使用 mybatis 动态sql 效率不相上下 甚至更优