十万级百万级数据量的Excel文件导入并写入数据库

public void upload1(MultipartFile file, @Validated UploadReq req) throws Exception { //从数据库查询出现有的数据,根据去重的字段分组去构建成一个HashMap,通过containsKey()判断 //将需要更新的数据放到updateList中 List<User> updateList=new ArrayList<>(); //已取值的行数 int rowNum = 0; //列号 int colNum = 0; //真正有数据的行数 int realRowCount = 0; //得到工作空间 Workbook workbook = null; try { workbook = ExcelUtil.getWorkbookByInputStream(file.getInputStream(), file.getOriginalFilename()); } catch (IOException e) { e.printStackTrace(); } //得到工作表 int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = ExcelUtil.getSheetByWorkbook(workbook, i) realRowCount = sheet.getPhysicalNumberOfRows(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); List<User> list = new ArrayList<>(); User user = null; for(Row row:sheet) { if(realRowCount == rowNum) { break; } //空行跳过 if(ExcelUtil.isBlankRow(row)) { continue; } if(row.getRowNum() == -1) { continue; }else { //第一行表头跳过 if(row.getRowNum() == 0) { continue; } } rowNum ++; colNum = 1; user = new User(); ExcelUtil.validCellValue(sheet, row, colNum, "id"); user.setId(Integer.valueOf(ExcelUtil.getCellValue(sheet, row, colNum - 1))); ExcelUtil.validCellValue(sheet, row, ++ colNum, "name"); user.setId(Integer.valueOf(ExcelUtil.getCellValue(sheet, row, colNum - 1))); //判断是否是已存在的数据,如果是就更新,不是就新增 //updateList.add(user); list.add(user); } //新增的逻辑 userService.saveBatch(list); System.out.println(list); } }

 

　　service层代码

@Service
public class UserServiceImpl implements IUserService {

    @Autowired
    private UserMapper userMapper;


    @Override
    public void saveBatch(List<User> list) throws Exception {
        //一个线程处理200条数据
        int count = 200;
        //数据集合大小
        int listSize = list.size();
        //开启的线程数
        int runSize = (listSize / count) + 1;
        //存放每个线程的执行数据
        List<User> newlist = null;

        //创建一个线程池，数量和开启线程的数量一样
        //Executors 的写法
        // ExecutorService executor = Executors.newFixedThreadPool(runSize);

        //ThreadPoolExecutor的写法
        ThreadPoolExecutor executor = new ThreadPoolExecutor(runSize, runSize, 1,
                TimeUnit.SECONDS, new ArrayBlockingQueue<Runnable>(3),
                new ThreadPoolExecutor.DiscardOldestPolicy());

        //创建两个个计数器
        CountDownLatch begin = new CountDownLatch(1);
        CountDownLatch end = new CountDownLatch(runSize);
        //循环创建线程
        for (int i = 0; i < runSize; i++) {
            //计算每个线程执行的数据
            if ((i + 1) == runSize) {
                int startIndex = (i * count);
                int endIndex = list.size();
                newlist = list.subList(startIndex, endIndex);
            } else {
                int startIndex = (i * count);
                int endIndex = (i + 1) * count;
                newlist = list.subList(startIndex, endIndex);
            }
            //线程类
            ImportThread mythead = new ImportThread(newlist, begin, end,userMapper);
            //这里执行线程的方式是调用线程池里的executor.execute(mythead)方法。
            executor.execute(mythead);
        }
        begin.countDown();
        end.await();
        //执行完关闭线程池
        executor.shutdown();
    }

　　线程类

public class ImportThread implements Runnable {


    public ImportThread() {
    }

    UserMapper userMapper;
    private List<User> list;
    private CountDownLatch begin;
    private CountDownLatch end;

    /**
     * 方法名: ImportThread
     * 方法描述: 创建个构造函数初始化 list,和其他用到的参数
     * @throws
     */
    public ImportThread(List<User> list, CountDownLatch begin, CountDownLatch end,UserMapper userMapper) {
        this.list = list;
        this.begin = begin;
        this.end = end;
        this.userMapper=userMapper;
    }

    @Override
    public void run() {
        try {
            //执行完让线程直接进入等待
            userMapper.saveBatch(list);
            begin.await();
        } catch (InterruptedException e) {
            e.printStackTrace();
        } finally {
            //这里要主要了，当一个线程执行完 了计数要减一不然这个线程会被一直挂起
            //这个方法就是直接把计数器减一的
            end.countDown();
        }
    }

}

 

　　

　　

　　

 

十万级百万级数据量的Excel文件导入并写入数据库

标签：过程   需要   list   tor   original   rri   ring   use   post