java实现读取EXCEL数据50万入库2分钟搞定

1. 引入easyPOI 依赖

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>

2.Customer 层

3.

 public CustomerUserGroup insertEtprUserGroupImport(MultipartFile file, CustomerUserGroup etprUserGroup)throws Exception {
        ImportParams params = new ImportParams();
        /**  表头行数,默认1*/
        params.setTitleRows(1);
        /**  表格标题行数,默认0*/
        params.setHeadRows(1);
        List<CustomerUserGroupItemExcel> list = new ArrayList<>();
        ExcelImportUtil.importExcelBySax(file.getInputStream(), CustomerUserGroupItemExcel.class, params, new IReadHandler<CustomerUserGroupItemExcel>() {
            @Override
            public void handler(CustomerUserGroupItemExcel o) {
                list.add(o);
            }
            @Override
            public void doAfterAll() {

            }
        });
        if (list.size() > MsgSendConst.PHONE_NUM_5G_MAX) {
            //用户组号码超过50W，不予新增
            throw new BusinessException(ExceptionCode.EXCEPTION_CODE0283);
        }
        if (null == list || list.size() == 0) {
            //有效号码为0个，请检查导入文件
            throw new BusinessException(ExceptionCode.EXCEPTION_CODE0284);
        }

        for (CustomerUserGroupItemExcel userGroups : list) {
            if (StringUtils.isEmpty(userGroups.getUserGroupItemPhoneNum()) ) {
                throw new BusinessException(ExceptionCode.EXCEPTION_CODE0282);
            }
            if (StringUtils.isEmpty(userGroups.getUserGroupItemUserName()) ) {
                throw new BusinessException(ExceptionCode.EXCEPTION_CODE0282);
            }
            if (!userGroups.getUserGroupItemPhoneNum().matches(MsgSendConst.PHONE_REGEX)) {
                throw new BusinessException(ExceptionCode.EXCEPTION_CODE0275);
            }
            if(StringUtils.isNotEmpty(etprUserGroup.getUserGroupId())){
                CustomerUserGroupItem customerUserGroupItems = new CustomerUserGroupItem();
                customerUserGroupItems.setUserGroupId(etprUserGroup.getUserGroupId());
                int selectEtprUserGroupItemNum = etprUserGroupDao.selectEtprUserGroupItemNum(customerUserGroupItems);
                if(selectEtprUserGroupItemNum >=FILE_PHONE_NUM_MAX){
                    throw new BusinessException(ExceptionCode.EXCEPTION_CODE0285);
                }
                CustomerUserGroupItem customerUserGroupItem = new CustomerUserGroupItem();
                customerUserGroupItem.setUserGroupId(etprUserGroup.getUserGroupId());
                customerUserGroupItem.setUserGroupItemPhoneNum(userGroups.getUserGroupItemPhoneNum());
                //取出当前分组下联系人
                CustomerUserGroupItem customerUserGroupItem1 = customerUserGroupDao.selectEtprUserGroupItem(customerUserGroupItem);
                /** 判断手机号码是否存在*/
                if (Base.isNotNull(customerUserGroupItem1)) {
                    throw new BusinessException(customerUserGroupItem1.getUserGroupItemPhoneNum() + ExceptionCode.EXCEPTION_CODE0265);
                }
                //取出所有的上传的手机号码
               /* List<String> list1 = list.stream().map(CustomerUserGroupItemExcel::getUserGroupItemPhoneNum).collect(Collectors.toList());
                //取出数据库所有手机号码
                List<String> list2 = customerUserGroupItems.stream().map(CustomerUserGroupItem::getUserGroupItemPhoneNum).collect(Collectors.toList());
                //存在相同，进行移除操作
                list1.removeIf(list2::contains);*/
            }
        }
        // 用户导入类型 0新增 1更新
        if ("1".equals(etprUserGroup.getUserImportType()) && StringUtils.isEmpty((etprUserGroup.getUserGroupId()))) {
            etprUserGroupItemManage.updateUserGroupImportExcel(etprUserGroup);
        }
        if ("0".equals(etprUserGroup.getUserImportType())) {
            if (etprUserGroup.getUserGroupName().contains("/")) {
                throw new BusinessException(ExceptionCode.EXCEPTION_CODE0271);
            }
        }
        etprUserGroupItemManage.insertEtprUserGroupImport(etprUserGroup);
        //添加异步上传状态 0 处理中
        CustomerAsyncTask customerAsyncTask = new CustomerAsyncTask();
        String date = getCurrentDate(0, Base.YYYY_MM_DD_HH_MM_SS);
        customerAsyncTask.setBusinessId(etprUserGroup.getUserGroupId());
        customerAsyncTask.setAsyncTaskState("0");
        customerAsyncTask.setAsyncTaskCreateTime(date);
        customerAsyncTask.setAsyncTaskModifyTime(date);
        CustomerAsyncTask customerAsyncTasks = this.insert(customerAsyncTask);
        /**异步处理导入文件*/
        customerUserGroupServic.insertEtprUserGroup(etprUserGroup,customerAsyncTasks,list);
        return etprUserGroup;

4. 读取到50万数据（实测耗时30秒左右），进行批次拆分，我这里已2000一批进行分解，分批插入

 @Async(value = "threadPoolTaskExecutor")
    public String  insertEtprUserGroup(CustomerUserGroup etprUserGroup,CustomerAsyncTask customerAsyncTask,List<CustomerUserGroupItemExcel> list)throws Exception{
        int listSize = list.size();
        int toIndex = 3000;
        int keyToken = 0;
        for (int i = 0; i < list.size(); i += 3000) {
            //作用为toIndex最后没有2000条数据则剩余几条newList中就装几条
            if (i + 3000 > listSize) {
                toIndex = listSize - i;
            }
            List newList = list.subList(i, i + toIndex);
            keyToken++;
             etprUserGroupItemManage.insertEtprUserGroup(etprUserGroup, newList);
        }
        //上传完成更新异步表为已完成
        CustomerAsyncTask customerAsyncTasks = new CustomerAsyncTask();
        String date = getCurrentDate(0, Base.YYYY_MM_DD_HH_MM_SS);
        customerAsyncTasks.setAsyncTaskId(customerAsyncTask.getAsyncTaskId());
        customerAsyncTasks.setAsyncTaskState("1");
        customerAsyncTasks.setAsyncTaskModifyTime(date);
        this.update(customerAsyncTasks);
        return "导入有效号码"+list.size()+"个";
    }

5. 拆分的批次进入事务层，拼接LIst里面的value数据，组装成2000条数据一个SQL进行入库

public String insertEtprUserGroup(CustomerUserGroup etprUserGroup,List<CustomerUserGroupItemExcel> list)throws Exception {
        //添加异步上传状态 0 处理中
        String  value ="";
        for(int i=0; i<list.size();i++){
            String uid = Base.getUid(32);
            String userGroupId = etprUserGroup.getUserGroupId();
            value=value+("("+"'"+uid+"'"+","+"'"+userGroupId+"'"+","+"'"+list.get(i).getUserGroupItemUserName()+"'"+","
                    +"'"+list.get(i).getUserGroupItemPhoneNum()+"'"+")"+",");
        }
        value= value.substring(0, value.length() - 1);
        // 拼接value 分批入库
        int groupItem = customerUserGroupDao.insertEtprUserGroupItem(value);
        logger.info("导入有效号码"+groupItem+"个");
        /*long end = System.currentTimeMillis();
        System.out.println((end - start) / 1000);*/
        return "导入有效号码"+groupItem+"个";
    }

6.