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

This article introduces the relevant knowledge of "how to solve the pit of MyBatis native batch insertion". In the operation of the actual case, 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!

The "pit" of original batch insertion

First of all, let's take a look at the pit in MyBatis native batch insertion. When we insert 100000 pieces of data in batch, the implementation code 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; 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, insert * / @ Test void saveBatchByNative () {long stime = System.currentTimeMillis () in batch; / / count start time List list = new ArrayList (); for (int I = 0; I)

< MAXCOUNT; i++) { User user = new User(); user.setName("test:" + i); user.setPassword("123456"); list.add(user); } // 批量插入 userService.saveBatchByNative(list); long etime = System.currentTimeMillis(); // 统计结束时间 System.out.println("执行时间：" + (etime - stime)); }} 核心文件 UserMapper.xml 中的实现代码如下： INSERT INTO `USER`(`NAME`,`PASSWORD`) VALUES (#{item.name},#{item.password}) 当我们开心地运行以上程序时，就出现了以下的一幕： 沃，程序竟然报错了！ 这是因为使用 MyBatis 原生批量插入拼接的插入 SQL 大小是 4.56M，而默认情况下 MySQL 可以执行的最大 SQL 为 4M，那么在程序执行时就会报错了。 解决方案 以上的问题就是因为批量插入时拼接的 SQL 文件太大了，所以导致 MySQL 的执行报错了。那么我们第一时间想到的解决方案就是将大文件分成 N 个小文件，这样就不会因为 SQL 太大而导致执行报错了。也就是说，我们可以将待插入的 List 集合分隔为多个小 List 来执行批量插入的操作，而这个操作过程就叫做 List 分片。 有了处理思路之后，接下来就是实操了，那如何对集合进行分片操作呢？ 分片操作的实现方式有很多种，这个我们后文再讲，接下来我们使用最简单的方式，也就是 Google 提供的 Guava 框架来实现分片的功能。 分片 Demo 实战 要实现分片功能，第一步我们先要添加 Guava 框架的支持，在 pom.xml 中添加以下引用： com.google.guava guava 31.0.1-jre 接下来我们写一个小小的 demo，将以下 7 个人名分为 3 组（每组最多 3 个），实现代码如下： import com.google.common.collect.Lists; import java.util.Arrays;import java.util.List; /** * Guava 分片 */public class PartitionByGuavaExample { // 原集合 private static final List OLD_LIST = Arrays.asList( "唐僧,悟空,八戒,沙僧,曹操,刘备,孙权".split(",")); public static void main(String[] args) { // 集合分片 List newList = Lists.partition(OLD_LIST, 3); // 打印分片集合 newList.forEach(i ->

{System.out.println ("set length:" + i.size ());});}}

The implementation results of the above procedures are as follows:

As can be seen from the above results, we only need to use the Lists.partition method provided by Guava to easily fragment a collection.

Implementation of native batch insertion fragmentation

Next, we will modify our MyBatis batch insertion code, which is implemented as follows:

@ Testvoid saveBatchByNativePartition () {long stime = System.currentTimeMillis (); / / Statistical start time List list = new ArrayList (); / / build insert data for (int I = 0; I < MAXCOUNT; I +) {User user = new User (); user.setName ("test:" + I); user.setPassword ("123456"); list.add (user) } / / bulk insert int count = (int) Math.ceil (MAXCOUNT / 1000.0); / / divide into n pieces, each 1000 List listPartition = Lists.partition (list, count); / / insert for (List item: listPartition) {userService.saveBatchByNative (item);} long etime = System.currentTimeMillis () / / Statistical end time System.out.println ("execution time:" + (etime-stime));}

After performing the above procedures, the final results are as follows:

As can be seen from the above figure, the exception error reported during the previous batch insert has disappeared, and the execution efficiency of this implementation is higher than that of MyBatis Plus batch insert. The execution time of MyBatis Plus batch insert 10W pieces of data is as follows:

This is the end of the content of "how to solve the pit of MyBatis native batch insertion". Thank you for 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.