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

This article introduces the relevant knowledge of "what are the methods of bulk data insertion in MyBatis". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Catalogue

Preface

Preparatory work

1. Cyclic single insertion

2.MP bulk insert

Implementation of ① controller

Implementation of ② business logic layer

Implementation of ③ data persistence layer

MP performance test

MP source code analysis

3. Native batch insertion

① business logic layer extension

② data persistence layer extension

③ add UserMapper.xml

Performance testing of native batch insertion

Defect analysis

Solution

Preface

Let's start with a brief account of the three bulk insertion features:

Cyclic single insertion

MP bulk insert function

Native batch insertion function.

Preparatory work

Before we begin, let's create the database and test data. The SQL script executed is as follows:

-create a database-SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0 drop `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 11purl 16') INSERT INTO `user`VALUES (2, 'Zhang Fei', '12345613', '2021-09-10 1818 INSERT INTO 28'); INSERT INTO `user`VALUES (3,' Guan Yu', '12345613', '2021-09-10 1818 INSERT INTO 11VALUES'); INSERT INTO `user`User`VALUES (4, 'Liu Bei', '123456mm,' 2021-09-10 1818 INSERT INTO 11INSERT INTO 41'); INSERT INTO `user`MAX (5, 'Cao Cao', '12345613', '2021-09-101818 INSERT INTO') SET FOREIGN_KEY_CHECKS = 1

1. Cyclic single insertion

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

The core code for a single insertion of the loop 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;@SpringBootTestclass UserControllerTest {/ / maximum number of cycles private static final int MAXCOUNT = 1000000; @ Autowired private UserServiceImpl userService / * Loop single insert * / @ Test void save () {long stime = System.currentTimeMillis (); / / Statistics start time for (int I = 0; I < MAXCOUNT; iTunes +) {User user = new User (); user.setName ("test:" + I); user.setPassword ("123456") UserService.save (user);} long etime = System.currentTimeMillis (); / / Statistical end time System.out.println ("execution time:" (etime-stime));}}

It took 88574 milliseconds to run the above program

2.MP bulk insert

There are three core implementation classes of MP bulk insert function: UserController (controller), UserServiceImpl (business logic implementation class) and UserMapper (database mapping class). Their calling process is as follows:

Note that the implementation of this method requires that you first add a MP framework, open the pom.xml file and add the following:

Com.baomidou mybatis-plus-boot-starter mybatis-plus-latest-version

Note: mybatis-plus-latest-version represents the latest version number of the MP framework, which can be accessed at mvnrepository.com/artifact/co. Query the latest version number, but when using it, be sure to replace the above "mybatis-plus-latest-version" with a specific version number, such as 3.4.3 to introduce the framework normally.

For more information on the MP framework, please move to its official website: baomidou.com/guide/

Implementation of ① controller

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 / * bulk insert (custom) * / @ RequestMapping ("/ mysavebatch") public boolean mySaveBatch () {List list = new ArrayList (); / / data to be added (user) for (User user I = 0; I < 1000; iSuppli +) {User user = new User (); user.setName ("test:" + I) User.setPassword ("123456"); list.add (user);} return userService.saveBatchCustom (list);}} ② business logic layer implementation

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;@Servicepublic class UserServiceImpl extends ServiceImpl implements UserService {@ Autowired private UserMapper userMapper; public boolean saveBatchCustom (List list) {return userMapper.saveBatchCustom (list) }} ③ data persistence layer implementation

Import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.example.demo.model.User;import org.apache.ibatis.annotations.Mapper;import java.util.List;@Mapperpublic interface UserMapper extends BaseMapper {boolean saveBatchCustom (List list);}

After the above code implementation, we can use MP to achieve the bulk data insertion function, but in this article, in addition to the specific implementation code, we also need to know the execution efficiency of each method, so let's write the test code of MP.

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;@SpringBootTestclass UserControllerTest {/ / maximum number of cycles private static final int MAXCOUNT = 1000000; @ Autowired private UserServiceImpl userService / * MP bulk insert * / @ Test void saveBatch () {long stime = System.currentTimeMillis (); / / Statistics start time List list = new ArrayList (); for (int I = 0; I < MAXCOUNT; iTunes +) {User user = new User (); user.setName ("test:" + I); user.setPassword ("123456") List.add (user);} / MP bulk insert userService.saveBatch (list); long etime = System.currentTimeMillis (); / / Statistical end time System.out.println ("execution time:" (etime-stime));}}

The execution of the above program took a total of 6088 milliseconds.

From the above results, it can be seen that using the bulk insert function of MP (inserting 10W pieces of data), its performance is 14.5 times higher than that of cyclic single insertion.

MP source code analysis

From the execution time of MP and loop single insertion, we can see that using MP is not as some friends think, or loop single execution. In order to explain this problem more clearly, we looked at the source code of MP.

The core implementation code of MP is the saveBatch method. The source code for this method is as follows:

We continue to follow up on the overloading method of saveBatch:

From the above source code, we can see that MP is to execute the data is divided into N parts, each 1000, every full 1000 will perform a batch insert, so its performance is much higher than the cycle single insert performance.

Then why should it be executed in batches instead of at once? Don't worry, we'll understand when we look at the third implementation.

3. Native batch insertion

The native batch insertion method relies on the foreach tag in MyBatis to splice the data into a native insert statement to be executed at once. The core implementation code is as follows.

① business logic layer extension

Add the saveBatchByNative method to UserServiceImpl. The implementation code is as follows:

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;@Servicepublic class UserServiceImpl extends ServiceImpl implements UserService {@ Autowired private UserMapper userMapper; public boolean saveBatchByNative (List list) {return userMapper.saveBatchByNative (list) }} ② data persistence layer extension

Add the 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;@Mapperpublic interface UserMapper extends BaseMapper {boolean saveBatchByNative (List list);} ③ add UserMapper.xml

Create a UserMapper.xml file and concatenate the SQL with foreach tags. The specific implementation code is as follows:

INSERT INTO `USER` (`NAME`, `PASSWORD`) VALUES (# {item.name}, # {item.password})

After the above steps, our native bulk insert function is almost complete, and then we use unit tests to check the efficiency of this method.

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;@SpringBootTestclass UserControllerTest {/ / maximum number of cycles private static final int MAXCOUNT = 1000000; @ Autowired private UserServiceImpl userService / * splice SQL by yourself and insert * / @ Test void saveBatchByNative () {long stime = System.currentTimeMillis (); / / Statistical start time List list = new ArrayList (); for (int I = 0; I < MAXCOUNT; iTunes +) {User user = new User (); user.setName ("test:" + I) User.setPassword ("123456"); list.add (user);} / bulk insert userService.saveBatchByNative (list); long etime = System.currentTimeMillis (); / / Statistical end time System.out.println ("execution time:" (etime-stime));}}

However, the following happens when we run the program:

What? The execution of the program was reported to be wrong.

Defect analysis

From the above error message, we can see that when we use the native method to splice 10W pieces of data into a SQL execution, the splicing SQL is too large (4.56m), which leads to the program execution error, because the maximum SQL (size) that MySQL can execute by default is 4m, so the program reports an error.

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

Solution

Of course, we can also solve the error problem by setting the maximum execution SQL of MySQL. The setting command is as follows:

-- setting the maximum execution SQL to 10Mset global max_allowed_packet=10*1024*1024; "what are the methods for inserting data in bulk with MyBatis"? thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.