一.问题描述
开发中遇到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;
}