Shulou(Shulou.com)06/01 Report--

This article mainly introduces how Mybatis imports data in batches, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article.

1. Circular insertion

Mapper.xml:

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

Mapper interface:

Public interface StudentMapper {int insert (Student student);}

Test the code:

@ SpringBootTestclass DemoApplicationTests {@ Resource private StudentMapper studentMapper; @ Test public void testInsert () {/ / data generation List studentList = createData (100); / / insert long start = System.currentTimeMillis (); studentList.stream () .forEach (student-> studentMapper.insert (student)) System.out.println (System.currentTimeMillis ()-start);} private List createData (int size) {List 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： INSERT INTO tb_student (name, age, phone, address, class_id) VALUES (#{name},#{age},#{phone},#{address},#{classId}) INSERT INTO tb_student (name, age, phone, address, class_id) VALUES (#{item.name},#{item.age},#{item.phone},#{item.address},#{item.classId}) mapper接口： public interface StudentMapper { int insert(Student student); int insertBatch(List studentList);} 测试代码： @SpringBootTestclass DemoApplicationTests { @Resource private StudentMapper studentMapper; @Test public void testInsertByForeachTag(){ //数据生成 List studentList = createData(100); //使用foreach标签，拼接SQL插入 long start = System.currentTimeMillis(); studentMapper.insertBatch(studentList); System.out.println(System.currentTimeMillis() - start); } private List createData(int size){ List 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. 批处理 测试代码： @SpringBootTestclass DemoApplicationTests { @Autowired private SqlSessionFactory sqlSessionFactory; @Test public void testInsertBatch(){ //数据生成 List 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 createData (int size) {List studentList = new ArrayList (); Student student; for (int I = 0; I < size) ) {student = new Student (); student.setName ("Xiao Wang" + I); student.setAge (18); student.setClassId (1); student.setPhone ("1585xxxx669"); student.setAddress ("unknown") Comparison of three ways of studentList.add (student);} return studentList;}}

MySQL server version: 5.6.4

Other dependent versions are as follows:

4.0.0 org.springframework.boot spring-boot-starter-parent 2.4.4 com.buhe demo 0.0.1-SNAPSHOT demo Demo project for Spring Boot 1.8 Org.springframework.boot spring-boot-starter-web org.springframework.boot spring-boot-starter-test test Mysql mysql-connector-java 5.1.41 org.mybatis.spring.boot mybatis-spring-boot-starter 1.3.1 Org.springframework.boot spring-boot-maven-plugin src / main/java * * / .xml

The performance of the three insertion methods under different data volumes, and the test results:

Insert mode 10 100500 1000 loops insert 496ms3330ms15584ms33755msforeach tags 268ms366ms392ms684ms batch 222ms244ms364ms426ms

Among the three methods, the efficiency of batch processing is the highest, especially in the case of large amount of data.

The second is the foreach tag, the foreach tag completes the batch operation by splicing SQL statements. However, when too many SQL are concatenated, causing the SQL size to exceed the value of the max_allowed_packet variable in the MySQL server, the operation will fail and a PacketTooBigException exception will be thrown.

Finally, there is the way of circular insertion, which can be used when the amount of data is small, and it is much less efficient when the amount of data is large.

Thank you for reading this article carefully. I hope the article "how to import data in batches of Mybatis" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.