MyBatis batch insert data 3 kinds of methods!

Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

Batch insert function is one of the more common business functions in our daily work, before I also wrote an article about “MyBatis Plus batch data insert function, YYDS!” There are two main problems: First, many people misunderstand the batch insertion function of MyBatis Plus (hereinafter referred to as MP), thinking that MP also uses cyclic single insertion data, so its performance is not improved; Second, there are drawbacks to the native batch insert method, but few people know about them.

Therefore, lei Ge decided to come to a summary of MyBatis batch insertion, and at the same time to do a performance test of three implementation methods, as well as the corresponding principle analysis.

First of all, there are three types of batch insertion functions:

1. Cyclic single insertion;
2. MP batch insert function;
3. Native batch insert function.

The preparatory work

To start, let’s create the database and test data, execute the following SQL script:

-- ----------------------------
Create database
-- ----------------------------
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP DATABASE IF EXISTS `testdb`;
CREATE DATABASE `testdb`;
USE `testdb`;

-- ----------------------------
Create table user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `createtime` datetime NULL DEFAULT CURRENT_TIMESTAMP.PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
Add test data
-- ----------------------------
INSERT INTO `user` VALUES (1.'zhaoyun'.'123456'.'the 2021-09-10 18:11:16');
INSERT INTO `user` VALUES (2.'zhang fei'.'123456'.'the 2021-09-10 18:11:28');
INSERT INTO `user` VALUES (3.'guan yu'.'123456'.'the 2021-09-10 18:11:34');
INSERT INTO `user` VALUES (4.'liu bei'.'123456'.'the 2021-09-10 18:11:41');
INSERT INTO `user` VALUES (5."Cao cao.'123456'.'the 2021-09-10 18:12:02');

SET FOREIGN_KEY_CHECKS = 1;
Copy the code

The final effect of the database is as follows:

1. Cycle single insertion

Next, we will use the Spring Boot project to batch insert 10W pieces of data to test the execution time of each method separately.

The core code for the cyclic single insert is as follows:

import com.example.demo.model.User;
import com.example.demo.service.impl.UserServiceImpl;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class UserControllerTest {

    // Maximum number of loops
    private static final int MAXCOUNT = 100000;

    @Autowired
    private UserServiceImpl userService;

    /** * loop single insert */
    @Test
    void save(a) {
        long stime = System.currentTimeMillis(); // Start time of statistics
        for (int i = 0; i < MAXCOUNT; i++) {
            User user = new User();
            user.setName("test:" + i);
            user.setPassword("123456");
            userService.save(user);
        }
        long etime = System.currentTimeMillis(); // Statistics end time
        System.out.println("Execution time:"+ (etime - stime)); }}Copy the code

Running the above program took 88574 ms, as shown below:

2. Insert MP in batches

There are three core implementation classes of MP batch insert function: UserController (controller), UserServiceImpl (business logic implementation class), and UserMapper (database mapping class). Their call flow is as follows:Note that to implement this method, you need to add the MP framework first. Open the POM. XML file and add the following content:

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>mybatis-plus-latest-version</version>
</dependency>
Copy the code

Note: mybatis – plus – latest – the latest version version said MP framework, accessible mvnrepository.com/artifact/co… Check the latest version number, but remember to use the above “mybatis-plus-latest-version” to replace the specific version number, such as 3.4.3 to normal import framework.

More information about the MP framework can be found at baomidou.com/guide/

① Controller implementation

import com.example.demo.model.User;
import com.example.demo.service.impl.UserServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping("/u")
public class UserController {

    @Autowired
    private UserServiceImpl userService;

    /** * Batch insert (custom) */
    @RequestMapping("/mysavebatch")
    public boolean mySaveBatch(a){
        List<User> list = new ArrayList<>();
        // Data to be added
        for (int i = 0; i < 1000; i++) {
            User user = new User();
            user.setName("test:"+i);
            user.setPassword("123456");
            list.add(user);
        }
        returnuserService.saveBatchCustom(list); }}Copy the code

② Implementation of business logic layer

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.demo.mapper.UserMapper;
import com.example.demo.model.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper.User>
        implements UserService {

    @Autowired
    private UserMapper userMapper;

    public boolean saveBatchCustom(List<User> list){
        returnuserMapper.saveBatchCustom(list); }}Copy the code

③ Implementation of data persistence layer

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.model.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserMapper extends BaseMapper<User>{

    boolean saveBatchCustom(List<User> list);
}
Copy the code

After the above code implementation, we can use MP to achieve the data batch insertion function, but in addition to the specific implementation code, we also need to know the execution efficiency of each method, so next we write MP test code.

MP performance test

import com.example.demo.model.User;
import com.example.demo.service.impl.UserServiceImpl;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.ArrayList;
import java.util.List;

@SpringBootTest
class UserControllerTest {

    // Maximum number of loops
    private static final int MAXCOUNT = 100000;

    @Autowired
    private UserServiceImpl userService;

    /** * MP */
    @Test
    void saveBatch(a) {
        long stime = System.currentTimeMillis(); // Start time of statistics
        List<User> list = new ArrayList<>();
        for (int i = 0; i < MAXCOUNT; i++) {
            User user = new User();
            user.setName("test:" + i);
            user.setPassword("123456");
            list.add(user);
        }
        // MP batch insert
        userService.saveBatch(list);
        long etime = System.currentTimeMillis(); // Statistics end time
        System.out.println("Execution time:"+ (etime - stime)); }}Copy the code

The execution of the above program took a total of 6088 ms, as shown below:According to the above results, using MP’s batch insert function (insert 10W data), its performance is 14.5 times higher than that of cyclic single insert.

Analysis of MP source code

From MP and cycle single insert execution time we can see that the use of MP is not as some friends think, or cycle single execution, in order to clarify this problem more clearly, we looked at the source of MP.

The core implementation code of MP is saveBatch method, the source code of this method is as follows:We follow up with saveBatch’s overloaded methods:As can be seen from the above source code, MP is to be executed data into N pieces, each 1000 pieces, every 1000 pieces will be executed a batch insert, so its performance is much higher than the cycle of single insert performance.

Then why do it in batches, not all at once? Don’t worry, we’ll see when we look at the third implementation.

3. Raw batch insert

The native batch insert method relies on the foreach tag in MyBatis to splicate the data into a native INSERT statement for one-time execution. The core implementation code is as follows.

① Business logic layer extension

Add saveBatchByNative method to UserServiceImpl with the following code:

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.demo.mapper.UserMapper;
import com.example.demo.model.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper.User>
        implements UserService {

    @Autowired
    private UserMapper userMapper;

    public boolean saveBatchByNative(List<User> list) {
        returnuserMapper.saveBatchByNative(list); }}Copy the code

(2) Data persistence layer extension

Add saveBatchByNative method to UserMapper, the implementation code is as follows:

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.model.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserMapper extends BaseMapper<User> {

    boolean saveBatchByNative(List<User> list);
}
Copy the code

(3) add UserMapper. XML

Create usermapper. XML file and use foreach tag to concatenate SQL as follows:

<? 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.example.demo.mapper.UserMapper">
    <insert id="saveBatchByNative">
        INSERT INTO `USER`(`NAME`,`PASSWORD`) VALUES
        <foreach collection="list" separator="," item="item">
            (#{item.name},#{item.password})
        </foreach>
    </insert>

</mapper>
Copy the code

Now that our native bulk insert functionality is almost complete, let’s use unit tests to see how efficient this approach is.

Native batch insert performance test

import com.example.demo.model.User;
import com.example.demo.service.impl.UserServiceImpl;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.ArrayList;
import java.util.List;

@SpringBootTest
class UserControllerTest {

    // Maximum number of loops
    private static final int MAXCOUNT = 100000;

    @Autowired
    private UserServiceImpl userService;
    
    / * * * native splicing SQL, bulk insert * /
    @Test
    void saveBatchByNative(a) {
        long stime = System.currentTimeMillis(); // Start time of statistics
        List<User> list = new ArrayList<>();
        for (int i = 0; i < MAXCOUNT; i++) {
            User user = new User();
            user.setName("test:" + i);
            user.setPassword("123456");
            list.add(user);
        }
        // Batch insert
        userService.saveBatchByNative(list);
        long etime = System.currentTimeMillis(); // Statistics end time
        System.out.println("Execution time:"+ (etime - stime)); }}Copy the code

However, when we run the program, the following happens:What? The execution of the program actually reported an error.

Defect analysis

As can be seen from the above error message, when we used the native method to splicing 10W data into an SQL execution, the program execution error was reported because the splicing SQL was too large (4.56m). By default, the maximum SQL (size) that MySQL can execute is 4M, so the program execution error was reported.

This is the disadvantage of the native batch insert method, and the reason why MP needs batch execution, is to prevent the program execution error caused by triggering the maximum execution SQL of the database.

The solution

MySQL > alter database set maximum execution SQL ();

-- Set maximum SQL execution to 10M
set global max_allowed_packet=10*1024*1024;
Copy the code

As shown below:

Note: The above commands need to be executed in the client connected to MySQL.

However, the above solution is still a palliative, since it is impossible to predict the size of the largest execution SQL in a program, the most common approach is to allocate a method that performs bulk inserts (as implemented by MP).

When we set MySQL’s maximum SQL to 10M, we run the above unit test code and execute it as follows:

conclusion

In this paper, we introduce three methods of MyBatis batch insertion, among which the performance of cyclic single insertion is the lowest and the least desirable; MyBatis splices native SQL one-time insert method has the highest performance, but this method may lead to program execution error (trigger the limit of the maximum SQL execution size of the database), so in view of the above situation, we can consider using the batch insert function of MP.

Follow the public account “Java Chinese Community” to see more MyBatis and Spring Boot series of articles.