mybatis三种批量插入方式

1. 循环插入

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.buhe.demo.mapper.StudentMapper">

  <insert id="insert" parameterType="Student">

    INSERT INTO tb_student (name, age, phone, address, class_id) VALUES (#{name},#{age},#{phone},#{address},#{classId})

  </insert>

</mapper>

mapper接口：

public interface StudentMapper {

 int insert(Student student);

}

测试代码：

@SpringBootTest

class DemoApplicationTests {

    @Resource

    private StudentMapper studentMapper;

    @Test

    public void testInsert(){

        //数据生成

        List<Student> studentList = createData(100);

        //循环插入

        long start = System.currentTimeMillis();

        studentList.stream().forEach(student -> studentMapper.insert(student));

        System.out.println(System.currentTimeMillis() - start);

    }

    private List<Student> createData(int size){

        List<Student> studentList = new ArrayList<>();

        Student student;

        for(int i = 0; i < size; i++){

            student = new Student();

            student.setName("小王" + i);

            student.setAge(18);

            student.setClassId(1);

            student.setPhone("1585xxxx669");

            student.setAddress("未知");

            studentList.add(student);

        }

        return studentList;

    }

}

2. foreach标签

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.buhe.demo.mapper.StudentMapper">

  <insert id="insert" parameterType="Student">

    INSERT INTO tb_student (name, age, phone, address, class_id) VALUES (#{name},#{age},#{phone},#{address},#{classId})

  </insert>

  <insert id="insertBatch">

    INSERT INTO tb_student (name, age, phone, address, class_id) VALUES

    <foreach collection="list" separator="," item="item">

        (#{item.name},#{item.age},#{item.phone},#{item.address},#{item.classId})

    </foreach>

  </insert>

</mapper>

mapper接口：

public interface StudentMapper {

    int insert(Student student);

    int insertBatch(List<Student> studentList);

}

测试代码：

@SpringBootTest

class DemoApplicationTests {

    @Resource

    private StudentMapper studentMapper;

    @Test

    public void testInsertByForeachTag(){

        //数据生成

        List<Student> studentList = createData(100);

        //使用foreach标签，拼接SQL插入

        long start = System.currentTimeMillis();

        studentMapper.insertBatch(studentList);

        System.out.println(System.currentTimeMillis() - start);

    }

    private List<Student> createData(int size){

        List<Student> studentList = new ArrayList<>();

        Student student;

        for(int i = 0; i < size; i++){

            student = new Student();

            student.setName("小王" + i);

            student.setAge(18);

            student.setClassId(1);

            student.setPhone("1585xxxx669");

            student.setAddress("未知");

            studentList.add(student);

        }

        return studentList;

    }

}

3. 批处理

测试代码：

@SpringBootTest

class DemoApplicationTests {

    @Autowired

    private SqlSessionFactory sqlSessionFactory;

    @Test

    public void testInsertBatch(){

        //数据生成

        List<Student> studentList = createData(100);

                //使用批处理

        long start = System.currentTimeMillis();

        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);

        StudentMapper studentMapperNew = sqlSession.getMapper(StudentMapper.class);

        studentList.stream().forEach(student -> studentMapperNew.insert(student));

        sqlSession.commit();

        sqlSession.clearCache();

        System.out.println(System.currentTimeMillis() - start);

    }

    private List<Student> createData(int size){

        List<Student> studentList = new ArrayList<>();

        Student student;

        for(int i = 0; i < size; i++){

            student = new Student();

            student.setName("小王" + i);

            student.setAge(18);

            student.setClassId(1);

            student.setPhone("1585xxxx669");

            student.setAddress("未知");

            studentList.add(student);

        }

        return studentList;

    }

}

三种插入方式在不同数据量下的表现，测试结果：

插入方式	10条	100条	500条	1000条
循环插入	496ms	3330ms	15584ms	33755ms
foreach标签	268ms	366ms	392ms	684ms
批处理	222ms	244ms	364ms	426ms

其次是foreach标签，foreach标签是通过拼接SQL语句的方式完成批量操作的。但是当拼接的SQL过多，导致SQL大小超过了MySQL服务器中max_allowed_packet变量的值时，会导致操作失败，抛出PacketTooBigException异常。

最后是循环插入的方式，这种方式在数据量小的时候可以使用，在数据量大的情况下效率要低很多。