Difference of Mybatis Two Different Batch Insertion Methods
- 2021-11-13 07:48:59
- OfStack
Test
Spliced sql
batch insertion
Data comparison summary
Preface
In this paper, Mybatis is used for batch insertion, and the differences between two different insertion methods are compared.
Test
Bulk Insert Considerations:
1. Add the parameter allowMultiQueries=true when connecting to the database, which supports multi-statement execution and batch processing
2. Whether the database supports a large amount of data writing, set max_allowed_packet parameters to ensure the amount of data submitted in batches
Spliced sql
public void batchDemo() {
long start = System.currentTimeMillis();
List<User> 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 id="batchForeach" parameterType="com.fyy.druid.entity.User">
insert into
user(id,`name`,pwd)
values
<foreach collection ="userList" item="user" separator =",">
(#{user.id}, #{user.name}, #{user.pwd})
</foreach>
</insert>
batch Insert
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: The batch mode also has its own problems. For example, during the 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 a certain type of situation
Data comparison
拼接sql (ms) | batch插入 (ms) | |
---|---|---|
500条 | 1744 | 639 |
2000条 | 26966 | 2473 |
5000条 | 173668 | 7382 |