Mybatis plus batch data insertion function, yyds!

Time:2021-11-30

Recently, when reviewing the small partner code, a small problem was found. The small partner actually inserts the database in the for loop, which will lead to connection, insertion and disconnection in each loop, resulting in certain performance problems. The simplified code is as follows:

/**
 *Insert operation
 */
@RequestMapping("/save")
public Object save() {
    boolean flag = false; //  Return results
    //Data to be added (user)
    for (int i = 0; i < 1000; i++) {
        User user = new User();
        user.setName("test:"+i);
        user.setPassword("123456");
        //Insert data
        flag = userService.save(user);
        if(!flag) break;
    }
    return flag;
}

This will not change the final execution result of the program, but will have a great impact on the execution efficiency of the programFor example, if you want to send 10 pieces of goods from location a to location B, you can choose to send 1 piece at a time and send 10 times; You can also choose the scheme of sending 10 pieces at a time and sending them once. Which would you choose? This is the problem of multiple circular inserts and batch one-time inserts.

PS: the larger the amount of data to be inserted, the shorter the time for batch insertion (compared with multiple circular inserts) and the greater its advantages.

Batch insert implementation scheme

In this article, we use the savebatch method of mybatis plus (hereinafter referred to as MP) to realize the batch insertion function of data. Because MP is not the focus of this article, we won’t introduce it here. If you are unfamiliar with a friend, you can go to his official website to make up for it: https://baomidou.com/guide/ , Let’s focus on the specific steps of MP batch insertion.

1. Introduce MP framework

First, open your pom.xml file and add the following content to the file:

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

Note: mybatis plus latest version indicates the latest version number of MP framework, which can be accessed https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter Query the latest version number, but remember to replace the above “mybatis plus latest version” with a specific version number, such as 3.4.3, in order to introduce the framework normally.

2. Create databases and tables

This step can be omitted and is mainly used to realize the functions of this article. The script for creating database and data table is as follows:

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

-- ----------------------------
--Create user table
-- ----------------------------
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, 'Zhao Yun', '123456', '2021-09-10 18:11:16');
Insert into ` user ` values (2, 'Zhang Fei', '123456', '2021-09-10 18:11:28');
Insert into ` user ` values (3, 'Guan Yu', '123456', '2021-09-10 18:11:34');
Insert into ` user ` values (4, 'Liu Bei', '123456', '2021-09-10 18:11:41');
Insert into ` user ` values (5, 'Cao Cao', '123456', '2021-09-10 18:12:02');

SET FOREIGN_KEY_CHECKS = 1;

3. Specific code implementation (key points)

① Entity class

First, create the user entity class corresponding to the database:

import lombok.Getter;
import lombok.Setter;

import java.util.Date;

@Getter
@Setter
public class User {
    private int id;
    private String name;
    private String password;
    private Date createtime;
}

② Controller layer code

The core of this article is to use the savebatch method provided by iservice class in MP framework to realize the batch data insertion function. The corresponding implementation code in controller is as follows:

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;

    /**
     *MP batch insert
     */
    @RequestMapping("/savebatch")
    public boolean saveBatch() {
        List<User> list = new ArrayList<>();
        //Data to be added (user)
        for (int i = 0; i < 1000; i++) {
            User user = new User();
            user.setName("test:"+i);
            user.setPassword("123456");
            list.add(user);
        }
        //Batch insert
        return userService.saveBatch(list);
    }
}

③ Service layer code (key)

Next, we will create a userservice interface to inherit the iservice interface in the MP framework. The implementation code is as follows:

import com.baomidou.mybatisplus.extension.service.IService;
import com.example.demo.model.User;

public interface UserService extends IService<User> {

}

Then create an implementation class of userservice:

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.stereotype.Service;

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

}

PS: note that userserviceimpl must inherit the serviceimpl in the MP framework, or it will override many methods.

④ Mapper layer code

The implementation of mapper layer is relatively simple. You only need to create a mapper class to inherit the basemapper class in MP framework. 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;

@Mapper
public interface UserMapper extends BaseMapper<User>{

}

PS: basemapper provides the most basic crud operation for an object (class).

summary

In this article, we introduced the specific implementation steps of batch insertion in MP (mybatis plus). Its core is to call the savebatch method provided by iservice in MP. However, what should I do if the MP framework is not introduced into the project? Can I lie flat with MP? Don’t worry. In the next article, we’ll talk about another way of batch insertion (the implementation of native batch insertion) and the analysis of their advantages and disadvantages.

Pay attention to the Chinese official account of the Java community to see more articles on MyBatis and Spring Boot.