1. 循环插入
mapper.xml：
|
1 2 3 4 5 6 7 |
<? 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接口：
|
1 2 3 |
public interface StudentMapper { int insert(Student student); } |
测试代码：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
@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：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
<? 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接口：
|
1 2 3 4 5 |
public interface StudentMapper { int insert(Student student);
int insertBatch(List<Student> studentList); } |
测试代码：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
@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. 批处理
测试代码：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
@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; } } |
三种方式的对比
MySQL服务器版本：5.6.4
其他依赖版本如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
<? xml version = "1.0" encoding = "UTF-8" ?> < project xmlns = "http://maven.apache.org/POM/4.0.0" xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation = "http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd" > < modelVersion >4.0.0</ modelVersion > < parent > < groupId >org.springframework.boot</ groupId > < artifactId >spring-boot-starter-parent</ artifactId > < version >2.4.4</ version > < relativePath /> <!-- lookup parent from repository --> </ parent > < groupId >com.buhe</ groupId > < artifactId >demo</ artifactId > < version >0.0.1-SNAPSHOT</ version > < name >demo</ name > < description >Demo project for Spring Boot</ description > < properties > < java.version >1.8</ java.version > </ properties > < dependencies > < dependency > < groupId >org.springframework.boot</ groupId > < artifactId >spring-boot-starter-web</ artifactId > </ dependency >
< dependency > < groupId >org.springframework.boot</ groupId > < artifactId >spring-boot-starter-test</ artifactId > < scope >test</ scope > </ dependency >
< dependency > < groupId >mysql</ groupId > < artifactId >mysql-connector-java</ artifactId > < version >5.1.41</ version > </ dependency > < dependency > < groupId >org.mybatis.spring.boot</ groupId > < artifactId >mybatis-spring-boot-starter</ artifactId > < version >1.3.1</ version > </ dependency > </ dependencies >
< build > < plugins > < plugin > < groupId >org.springframework.boot</ groupId > < artifactId >spring-boot-maven-plugin</ artifactId > </ plugin > </ plugins >
< resources > < resource > < directory >src/main/java</ directory > < includes > < include >**/*.xml</ include > </ includes > </ resource > </ resources > </ build >
</ project > |
三种插入方式在不同数据量下的表现，测试结果：
| 循环插入 | 496ms | 3330ms | 15584ms | 33755ms |
| foreach标签 | 268ms | 366ms | 392ms | 684ms |
| 批处理 | 222ms | 244ms | 364ms | 426ms |
三种方式中，批处理的方式效率是最高的，尤其是在数据量大的情况下尤为明显。
其次是foreach标签，foreach标签是通过拼接SQL语句的方式完成批量操作的。但是当拼接的SQL过多，导致SQL大小超过了MySQL服务器中max_allowed_packet变量的值时，会导致操作失败，抛出PacketTooBigException异常。
最后是循环插入的方式，这种方式在数据量小的时候可以使用，在数据量大的情况下效率要低很多。
以上就是Mybatis的三种批量插入方式的详细内容，更多关于Mybatis 批量插入的资料请关注其它相关文章！
原文链接：https://www.cnblogs.com/seve/p/14654104.html
查看更多关于Mybatis三种批量插入数据的方式的详细内容...