java中批量插入数据优化问题_java大批量入库操作优化

一.问题描述

开发中遇到excel大量数据批量入库时候，逐条插入数据很慢导致卡顿问题。

二.解决方案

<!--xml-->
 
<insert id="insertBatchTemplateData" useGeneratedKeys="false"             parameterType="java.util.HashMap">
		insert into ZJDSS.BUDGET_ORGAN_ISSUED_IMP
		(BUDGET_ID,
		ORGAN_ID,
		COST_ALL,
		IMPORT_ID,
		STATE,
		ACCT_YEAR) VALUES
		<foreach collection="DATA_LIST" item="DATA" separator=",">
			(
			#{DATA.BUDGET_ID},
			#{DATA.ORGAN_ID},
			#{DATA.COST_ALL},
			#{DATA.IMPORT_ID},
			'1',
			#{DATA.acctYear})
		</foreach>
</insert>

impl：
/***
     * STEP 2.20 文件入库operating insert,进入导入日志，记录导入内容
     */
 
    private boolean executeInsertDataLog(final List ls,Map<String,Object> params) {
 
        try {
 
                    Map param = new HashMap();
                    param.put("areaNo", params.get("areaNo"));
                    param.put("acctYear", params.get("acctYear"));
                    mapper.delBudgetWg(param);
            System.out.println("shujuliang--------------------------------------------"+ls.size());
                    int listSize = ls.size();
                    int toIndex=100;
                    int count = 0;
                    for(int i = 0;i<ls.size();i+=100) {
                        System.out.println("i================================================="+i);
                    if (i + 100 > listSize) {  //作用为toIndex最后没有100条数据则剩余几条newList中就装几条
                        toIndex = listSize - i;
                        System.out.println("不够一百条了");
                    }
                    List DATA_LIST = ls.subList(i, i + toIndex);//每100个为一组
                        for(int j = 0; j < DATA_LIST.size(); j++){
                            Map m = (Map) DATA_LIST.get(j);
                            m.put("IMPORT_ID", params.get("batchId"));
                            m.put("OPER_USER", params.get("loginId"));
                            m.put("acctYear", params.get("acctYear"));
                        }
                        param.put("DATA_LIST",DATA_LIST);
                        mapper.insertBatchTemplateData(param);
                        mapper.insertBatchTemplateDataLog(param);
                    }
                    
        } catch (Exception e) {
            Map param = new HashMap();
            param.put("areaNo", params.get("areaNo"));
            param.put("acctYear", params.get("acctYear"));
            mapper.delBudgetWg(param);
            return false;
        }
        return true;
    }