线程池+监听器+easyExcel 实现excel数据快速导入数据库

线程池+监听器+easyExcel 实现excel数据快速导入数据库

 <!-- alibaba excel依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
            <exclusions>
                <exclusion>
                    <artifactId>cglib</artifactId>
                    <groupId>cglib</groupId>
                </exclusion>
            </exclusions>
        </dependency>

使用监听器实现excel文件导入

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.cbb.gyl_serve.bean.ob.GylOrderDetailWithBLOBs;
import com.cbb.gyl_serve.dao.ob.GylOrderDetailMapper;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.concurrent.*;
import java.util.concurrent.atomic.AtomicInteger;

@Slf4j
public class GylOrderDetailListener extends AnalysisEventListener<GylOrderDetailWithBLOBs> {

    private List<GylOrderDetailWithBLOBs> cacheData = new ArrayList<>();
    private static final int BATCH_COUNT = 5000;

    private static AtomicInteger count = new AtomicInteger(1);
    private final GylOrderDetailMapper orderDetailMapper;
  

    public GylOrderDetailListener(GylOrderDetailMapper orderDetailMapper) {
        this.orderDetailMapper= orderDetailMapper;
    }



    @SneakyThrows
    @Override
    public void invoke(GylOrderDetailWithBLOBs orderDetailWithBLOBs, AnalysisContext analysisContext) {
        Calendar calendarType4 = Calendar.getInstance();
        calendarType4.add(Calendar.YEAR, -3);
        Date time = calendarType4.getTime();
        SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd");
        Date Orderdate = format.parse(orderDetailWithBLOBs.getOrderTime());
        if(Orderdate.compareTo(time)>=0){
            cacheData.add(orderDetailWithBLOBs);
        }
        if (cacheData.size() >= BATCH_COUNT) {
            log.info("保存数据--share auto-----{}条", cacheData.size());
            saveData();
            cacheData.clear();
        }
    }

    @SneakyThrows
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        if (!orderDetailList.get().isEmpty()) {
            log.info("保存数据--share auto-----{}条", orderDetailList.get().size());
            saveData();
        }

    }
    private void saveData() {
        // 这里可以使用MyBatis的批量插入方法
        orderDetailMapper.insertBatchOrderDetail(cacheData);
    }

 

 

}

到这里解决了excel大文件内存溢出问题，但是导入数据库数据慢，引入线程池实现快速批量导入

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.listener.ReadListener;
import com.cbb.gyl_serve.bean.ob.GylOrderDetailWithBLOBs;
import com.cbb.gyl_serve.dao.ob.GylOrderDetailMapper;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.concurrent.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.Consumer;

@Slf4j
public class GylOrderDetailListener extends AnalysisEventListener<GylOrderDetailListener > {
    private List<GylOrderDetailWithBLOBs> cacheData = new ArrayList<>();
    private static final int BATCH_COUNT = 5000;
    private ExecutorService executorService = Executors.newFixedThreadPool(10);
    private ThreadLocal<ArrayList<GylOrderDetailWithBLOBs>> orderDetailList = ThreadLocal.withInitial(ArrayList::new);
    private static AtomicInteger count = new AtomicInteger(1);
    private final GylOrderDetailMapper orderDetailMapper;
    @Autowired
    //线程池
    private ThreadPoolExecutor executor;

    public GylOrderDetailListener (GylOrderDetailMapper orderDetailMapper) {
        this.orderDetailMapper= orderDetailMapper;
    }



    @SneakyThrows
    @Override
    public void invoke(com.cbb.gyl_serve.bean.ob.GylOrderDetailWithBLOBs orderDetailWithBLOBs, AnalysisContext analysisContext) {
        Calendar calendarType4 = Calendar.getInstance();
        calendarType4.add(Calendar.YEAR, -3);
        Date time = calendarType4.getTime();
        SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd");
        Date Orderdate = format.parse(orderDetailWithBLOBs.getOrderTime());
        String formattedDate = format.format(time);
        Date nowTime = format.parse(formattedDate);
        if(Orderdate.compareTo(nowTime)>=0){
            orderDetailList.get().add(orderDetailWithBLOBs);
        }
        if (orderDetailList.get().size() >= BATCH_COUNT) {
            log.info("保存数据--share auto-----{}条", orderDetailList.get().size());
//            saveData();
//            cacheData.clear();
             asyncSaveData();
        }
    }

    @SneakyThrows
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        if (!orderDetailList.get().isEmpty()) {
            log.info("保存数据--share auto-----{}条", orderDetailList.get().size());
            asyncSaveData();
        }
        executorService.shutdown();
        //等待线程池执行完毕
        while(!executorService.isTerminated()){
        }
        count.set(1);
        System.out.println("线程池执行完毕");
    }


    public void asyncSaveData() throws ExecutionException, InterruptedException {
        if (!orderDetailList.get().isEmpty()) {
            ArrayList<GylOrderDetailWithBLOBs> OrderDetailDOS = (ArrayList<GylOrderDetailWithBLOBs>) orderDetailList.get().clone();
            executorService.execute(new GylOrderDetailListener .SaveTask(OrderDetailDOS,orderDetailMapper));
            orderDetailList.get().clear();
        }
    }

    static class SaveTask implements Runnable {

        private List<GylOrderDetailWithBLOBs> employeeList;
        private GylOrderDetailMapper orderDetailMapper;
        public SaveTask(List<GylOrderDetailWithBLOBs> employeeList, GylOrderDetailMapper orderDetailMapper) {
            this.employeeList = employeeList;
            this.orderDetailMapper = orderDetailMapper;
        }

        @Override
        public void run() {
            orderDetailMapper.insertBatchOrderDetail(employeeList);
//            Thread.currentThread().getId();
//            log.info("线程"+Thread.currentThread().getId()+","+new Date()+"插入"+employeeList.size() + "条数据");
            log.info("线程"+Thread.currentThread().getId()+"第" + count.getAndAdd(1) + "次插入" + employeeList.size() + "条数据");
        }
    }

}