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

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!

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.