excle大量数据导入实现数据新增与更新

1.逻辑：

1.将excle 数据 上传后 先存入到 临时表。

2.通过 临时表 与要更新的表 进行 相互处理

3.因为通过表与表的 置换 ，避免了 mysql 索引的遍历 ，所以速度 很快

2.统计：

原表有10W 条数据， 一次上传37301条  运行结果：

导入情况：总上传条数 [37301 条] 新增 [809 条 ] 更新 [11278 条 ] 异常 [0 条 ] 耗费时长：23秒   新增+更新 不等于 总上传条数    原因：1.sql 里写了 条件   2.excle里面有重复的合同号

/**
 * 导入 数据
 * @param list  excle数据转换成的 临时对象集合
 * @return
 */
@Async  //异步
@Transactional   //事务
public String importCaseInfo(List<CaseInfoTemporary> list){
    //创建临时表
    caseInfoTemporaryMapper.createTemp();
    List<ImportEntit> result=null;
    int zongInsertNumber=0;
    int zongUpdateNumber=0;
    int zongErrorNumber=0;
    int max=0;
    int maxCommit=500;
    try {
        long start=System.currentTimeMillis();
        if(StringUtils.isListNotEmpty(list)){
            List<CaseInfoTemporary> addList=new ArrayList<>();
            for (CaseInfoTemporary caseInfoTemporary : list) {

                //添加到数据库 
                 caseInfoTemporary.setSystemId("excles"); 
                 addList.add(caseInfoTemporary); 
                 max++;
                //每次提交500 到临时表，太多会异常
                if(max>maxCommit){
                    //批量新增 数据到临时表
                    log.info("分批提交： ["+max+" 条] 条件【"+maxCommit+"] 条");
                    if(StringUtils.isListNotEmpty(addList)){
                        this.caseInfoTemporaryMapper.insertList(addList);
                    }
                    max=0;
                    addList=new ArrayList<>();
                }

            }
            //批量新增 数据到临时表
            log.info("最后提交： ["+max+" 条] 条件【"+maxCommit+"] 条");
            if(StringUtils.isListNotEmpty(addList)){
                this.caseInfoTemporaryMapper.insertList(addList);
            }
        }
        
        // 以下是进行临时表 ==》 正式表 
        long end=System.currentTimeMillis();
        log.info("导入情况：总上传条数 ["+list.size()+" 条]  耗费时长："+((end-start)/1000)+"秒");
        zongUpdateNumber= caseInfoTemporaryMapper.updateCaseInfoTemporaryToCaseInfo();
        long end2=System.currentTimeMillis();
        log.info("导入情况-- 更新 耗费时长："+((end-end2)/1000)+"秒");
        zongInsertNumber=caseInfoTemporaryMapper.insertCaseInfoToCaseInfoTemporary();
        long end3=System.currentTimeMillis();
        log.info("导入情况--新增 耗费时长："+((end2-end3)/1000)+"秒");
        long end4=System.currentTimeMillis();
        //删除临时表数据
        caseInfoTemporaryMapper.deleteCaseInfoTemporary();
        log.info("导入情况--删除临时表 耗费时长："+((end3-end4)/1000)+"秒");
        
        log.info("导入情况：总上传条数 ["+list.size()+" 条] 新增 ["+zongInsertNumber+" 条 ]  更新  
["+zongUpdateNumber+" 条 ]  异常 ["+zongErrorNumber+" 条 ] 耗费时长："+((end3-start)/1000)+"秒");

    }catch (Exception e) {
        e.printStackTrace();
    }
    return "成功";
}

 

 

3. 以下是 创建临时表，新增以及更新的 sql   。 以合同号决定是更新还是新增 （contract_in）

case_info_temporary - ------临时表

case_info    --------------------正式表

3.1 创建 ---caseInfoTemporaryMapper.createTemp()

DROP TABLE IF EXISTS `case_info_temporary`;
CREATE TEMPORARY TABLE `case_info_temporary`  (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    `third_id` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '第三id',
    `system_id` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '数据识',
    `contract_in` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '合同编号'
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MEMORY AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '临时表';

ALTER TABLE case_info_temporary ADD INDEX t_system_id_name (system_id);
ALTER TABLE case_info_temporary ADD INDEX t_contract_in_name (contract_in);

3.2  更新 -caseInfoTemporaryMapper.updateCaseInfoTemporaryToCaseInfo

 UPDATE case_info,case_info_temporary
           SET case_info.system_id=case_info_temporary.system_id,
               case_info.bus_type=case_info_temporary.bus_type
   WHERE
case_info.contract_in = case_info_temporary.contract_in and case_info.system_id='excles' 

3.3 新增--caseInfoTemporaryMapper.insertCaseInfoToCaseInfoTemporary

INSERT INTO case_info (
          system_id,
       contract_in,
       bus_type
   )
   SELECT
       b.system_id,
           b.contract_in,
           b.bus_type
   FROM
       case_info_temporary AS b
   WHERE
       NOT EXISTS ( SELECT 1 FROM case_info AS c WHERE c.contract_in = b.contract_in )
       GROUP BY B.contract_in

3.4 删除临时表 --caseInfoTemporaryMapper.deleteCaseInfoTemporary()

DROP TABLE IF EXISTS `case_info_temporary`;