百万数据多行插入，几秒入库

最近遇到过一个导入大批量数据耗时过长的问题，查了一下资料，找到一个mybatis-puls的一个插入，大大提高了入库效率，这里给大家分析分析。

先介绍一下，本次测试用例是MVC三层结构的例子，大概结构如下：

pom.xml
com.tring.ysyn.entity.Pull.java //数据库实体类
com.tring.ysyn.service.IPullService.java //接口
com.tring.ysyn.service.impl.PullServiceImpl.java //实现类
com.tring.ysyn.mapper.PullMapper.java //实体对应的mapper
com.tring.ysyn.mapper.EasyBaseMapper.java //自定义mapper
com.tring.ysyn.controller.TestController.java //测试类

1、pom导入 mybatis-plus-extension

<!-- Mybatis-Plus 多行插入 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>3.5.2</version>
        </dependency>

2、编写一个EasyBaseMapper.java，继承 BaseMapper

package com.tring.ysyn.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import java.util.Collection;

/**
 * @author Tring
 * date 2023-03-26
 */
public interface EasyBaseMapper<T> extends BaseMapper<T> {

    /**
     * 多行插入（方法名字不能改）
     * @param entityList
     * @return
     */
    Integer insertBatchSomeColumn(Collection<T> entityList);
}

3、将PullMapper.java 由原来的继承BaseMapper,改为继承 自定义mapper，EasyBaseMapper.java

package com.tring.ysyn.mapper;

import com.tring.ysyn.entity.Pull;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author zirong.kuang
 * @since 2024-03-15
 */
public interface PullMapper extends EasyBaseMapper<Pull> {

}

4、接口IPullService.java 定义一个批量插入的方法 batchInsertPull 

package com.tring.ysyn.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.tring.ysyn.entity.Pull;

import java.util.List;

/**
 * <p>
 *  服务类
 * </p>
 *
 * @author zirong.kuang
 * @since 2024-03-15
 */
public interface IPullService extends IService<Pull> {

    void batchInsertPull(List<Pull> dto);

}

5、接口实现类 PullServiceImpl.java ,直接调用自定义mapper的 insertBatchSomeColumn 方法

package com.tring.ysyn.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.tring.ysyn.entity.Pull;
import com.tring.ysyn.mapper.PullMapper;
import com.tring.ysyn.service.IPullService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.util.StopWatch;

import java.util.List;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author zirong.kuang
 * @since 2024-03-15
 */
@Service
@Slf4j
public class PullServiceImpl extends ServiceImpl<PullMapper, Pull> implements IPullService {

    @Override
    public void batchInsertPull(List<Pull> dto) {
        StopWatch sw = new StopWatch();
        sw.start("sapOtherList");
        this.baseMapper.insertBatchSomeColumn(dto);
        sw.stop();
        log.info(sw.prettyPrint());
    }
}

6、最后写一个测试类 TestController.java

package com.tring.ysyn.controller;

import com.tring.ysyn.entity.Pull;
import com.tring.ysyn.service.IPullService;
import com.tring.ysyn.util.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.validation.Valid;
import java.util.List;

/**
 * @author Tring
 * date 2022-10-22
 */
@RestController
@RequestMapping("/test")
public class TestController {

    @Autowired
    private IPullService pullService;

    private AutoCloseable AutoCloseable;

    @PostMapping ("/test")
    public Result test(@Valid @RequestBody List<Pull> dto){
        Result result = new Result();

        pullService.batchInsertPull(dto);
        result.setCode(200);
        System.out.println(dto.size());
        result.setMsg("调用成功");
        return result;
    }
}

最后我用11万数据测试了一下，正常导入要几分钟的数据，这里只用了短短9秒入库。

觉得对你有帮助的，点个支持吧！