What are the differences between the two different batch insertion methods of Mybatis?
Xiaobian to share with you what are the differences between the two different batch insertion methods of Mybatis, I hope you have gained something after reading this article, let's discuss it together!
preface
This article uses Mybatis for batch insertion and compares the differences between two different insertion methods.
test
Notes for batch insertion:
1. Add parameter allowMultiQueries=true when connecting database, support multi-statement execution, batch processing
2. Whether the database supports large data writing, set max_allowed_packet parameter to ensure the amount of data submitted in batches
Stitching SQL
public void batchDemo() { long start = System.currentTimeMillis(); List list = new ArrayList(); for (int i = 0; i < 5000; i++) { User user = new User(); user.setId(UUID.randomUUID().toString()); user.setName("feiyangyang"); user.setPwd("feiyangyang"); list.add(user); } userService.batchForeach(list); long end = System.currentTimeMillis(); System.out.println("---------------" + (start - end) + "---------------"); } insert into user(id,`name`,pwd) values (#{user.id}, #{user.name}, #{user.pwd}) batch insertion
public void batchInsert() { long start = System.currentTimeMillis(); SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH); UserService mapper = sqlSession.getMapper(UserService.class); for (int i = 0; i < 5000; i++) { User user = new User(); user.setId(UUID.randomUUID().toString()); user.setName("feiyangyang"); user.setPwd("feiyangyang"); mapper.batchInsert(user); } sqlSession.commit(); long end = System.currentTimeMillis(); System.out.println("---------------" + (start - end) + " ---------------");}
Note: batch mode also has its own problems. For example, during Insert operation, there is no way to obtain the self-increasing id before the transaction is committed, which does not meet the business requirements in some cases.
data comparison
Stitching sql (ms)batch insert (ms)500 17446392000 2696624735000 1736687382 After reading this article, I believe you have a certain understanding of "What are the differences between Mybatis two different batch insert methods". If you want to know more about it, welcome to pay attention to the industry information channel. Thank you for reading!
