一.导入功能优化
普通for循环，对于导入大量数据时非常耗时。可以通过Mybatis的批量插入功能提高效率。
每批次导入的数据不能太多，否则会报错。通过测试发现，每批次200条为宜。
测试结果：
开启事务情况下：
文件大小(k) 时长(ms) 测试次数 数据量(条数)
batchInsert: 292 400 5 3000
240 230 3 2000
187 110 3 1000
104 70 5 500
292 8700 5 3000
for: 240 5800 3 2000
187 3200 3 1000
104 1500 2 500
1.Service
@Override
@Transactional
public String importListTest(InputStream in, String fileName) {
int num = 0;
String[][] table = null;
String msg = null;
try {
table = ExcelUtils.readExcel(in,fileName,0);
} catch (Exception e) {
e.printStackTrace();
return "文件导入失败，请检查文件格式！";
}
Map<String,Integer> tableColumMap = new HashMap<String, Integer>();
if (CheckUtils.isNullOrBlank(table)
|| table.length == 0) {
return "表格中没有任何数据！";
}
if (table.length == 1) {
return "表格中只存在表头数据！";
}
//校验导入文件内容
for (int i = 0; i < table[0].length; i++) {
String cell = table[0][i].trim();
tableColumMap.put(cell, i);
}
List<String> errorMsgs = new ArrayList<String>();
String[] titles ={"源声ID","源声描述","产品名称","产品内部型号","产品外部型号","ROM版本","系统版本","创建时间","IMEI","SN","问卷次数","满意度得分（问题1）","推荐值得分（问题2）"};
for (int i = 0; i < titles.length; i++) {
String title = titles[i];
if(!tableColumMap.containsKey(title)){
errorMsgs.add("Excel表格表头列有误,不存在"+title+"列，请修改Excel后导入");
if(errorMsgs.size() >= 10) return "导入文件表头不正确，请检查后重新导入！";
}
}
long start = System.currentTimeMillis();
if (errorMsgs.size() == 0) {
NPSSourceAnalysis npsSourceAnalysis=new NPSSourceAnalysis();
for (int i = 1; i < table.length; i++) {
String sourceID = table[i][tableColumMap.get("源声ID")].trim();
String descript = table[i][tableColumMap.get("源声描述")].trim();
if(descript.length()>1000){
descript = (String) descript.subSequence(0, 999);
}
String product = table[i][tableColumMap.get("产品名称")].trim();
String inner = table[i][tableColumMap.get("产品内部型号")].trim();
String outer = table[i][tableColumMap.get("产品外部型号")].trim();
String ROM = table[i][tableColumMap.get("ROM版本")].trim();
String createTime = table[i][tableColumMap.get("创建时间")].trim();
String SN = table[i][tableColumMap.get("SN")].trim();
String CSR = table[i][tableColumMap.get("满意度得分（问题1）")].trim();
String recommend = table[i][tableColumMap.get("推荐值得分（问题2）")].trim();
npsSourceAnalysis.setSourceID(Integer.parseInt(sourceID));
npsSourceAnalysis.setDescript(descript);
npsSourceAnalysis.setProduct(product);
npsSourceAnalysis.setInnerVersion(inner);
npsSourceAnalysis.setOuterVersion(outer);
npsSourceAnalysis.setROM(ROM);
npsSourceAnalysis.setCreateTime(createTime);
npsSourceAnalysis.setSN(SN);
npsSourceAnalysis.setCSR(Integer.parseInt(CSR));
npsSourceAnalysis.setRecommend(Integer.parseInt(recommend));
nPSSourceAnalysisMapper.insertTest(npsSourceAnalysis);
}
msg = "success-import:-"+num+"-items";
} else{
for (String errorMsg : errorMsgs) {
msg = msg + errorMsg +";";
}
}
/*if (errorMsgs.size() == 0) {
int total = table.length/200 + 1;
for (int j = 0; j < total-1; j++) {
List<NPSSourceAnalysis> list = new ArrayList<NPSSourceAnalysis>();
for (int i = 1+200*j; i < 1+200+200*j; i++) {
NPSSourceAnalysis npsSourceAnalysis=new NPSSourceAnalysis();
String sourceID = table[i][tableColumMap.get("源声ID")].trim();
String descript = table[i][tableColumMap.get("源声描述")].trim();
if(descript.length()>1000){
descript = (String) descript.subSequence(0, 999);
}
String product = table[i][tableColumMap.get("产品名称")].trim();
String inner = table[i][tableColumMap.get("产品内部型号")].trim();
String outer = table[i][tableColumMap.get("产品外部型号")].trim();
String ROM = table[i][tableColumMap.get("ROM版本")].trim();
String createTime = table[i][tableColumMap.get("创建时间")].trim();
String SN = table[i][tableColumMap.get("SN")].trim();
String CSR = table[i][tableColumMap.get("满意度得分（问题1）")].trim();
String recommend = table[i][tableColumMap.get("推荐值得分（问题2）")].trim();
npsSourceAnalysis.setSourceID(Integer.parseInt(sourceID));
npsSourceAnalysis.setDescript(descript);
npsSourceAnalysis.setProduct(product);
npsSourceAnalysis.setInnerVersion(inner);
npsSourceAnalysis.setOuterVersion(outer);
npsSourceAnalysis.setROM(ROM);
npsSourceAnalysis.setCreateTime(createTime);
npsSourceAnalysis.setSN(SN);
npsSourceAnalysis.setCSR(Integer.parseInt(CSR));
npsSourceAnalysis.setRecommend(Integer.parseInt(recommend));
list.add(npsSourceAnalysis);
}
nPSSourceAnalysisMapper.batchInsert(list);
}
//最后不够200条
List<NPSSourceAnalysis> list = new ArrayList<NPSSourceAnalysis>();
for (int i = 1+200*(total-1); i < table.length; i++) {
NPSSourceAnalysis npsSourceAnalysis=new NPSSourceAnalysis();
String sourceID = table[i][tableColumMap.get("源声ID")].trim();
String descript = table[i][tableColumMap.get("源声描述")].trim();
if(descript.length()>1000){
descript = (String) descript.subSequence(0, 999);
}
String product = table[i][tableColumMap.get("产品名称")].trim();
String inner = table[i][tableColumMap.get("产品内部型号")].trim();
String outer = table[i][tableColumMap.get("产品外部型号")].trim();
String ROM = table[i][tableColumMap.get("ROM版本")].trim();
String createTime = table[i][tableColumMap.get("创建时间")].trim();
String SN = table[i][tableColumMap.get("SN")].trim();
String CSR = table[i][tableColumMap.get("满意度得分（问题1）")].trim();
String recommend = table[i][tableColumMap.get("推荐值得分（问题2）")].trim();
npsSourceAnalysis.setSourceID(Integer.parseInt(sourceID));
npsSourceAnalysis.setDescript(descript);
npsSourceAnalysis.setProduct(product);
npsSourceAnalysis.setInnerVersion(inner);
npsSourceAnalysis.setOuterVersion(outer);
npsSourceAnalysis.setROM(ROM);
npsSourceAnalysis.setCreateTime(createTime);
npsSourceAnalysis.setSN(SN);
npsSourceAnalysis.setCSR(Integer.parseInt(CSR));
npsSourceAnalysis.setRecommend(Integer.parseInt(recommend));
list.add(npsSourceAnalysis);
}
nPSSourceAnalysisMapper.batchInsert(list);
msg = "success-import:-"+num+"-items";
} else{
for (String errorMsg : errorMsgs) {
msg = msg + errorMsg +";";
}
}*/
long end = System.currentTimeMillis();
logger.info("时长："+(end-start));
return msg;
}
dao:
int batchInsert(List<NPSSourceAnalysis> list);
mapper:
<insert id="batchInsert" >
insert into nps_source_analysis_test (sourceID, descript, product, innerVersion,outerVersion,ROM, createTime, SN, CSR, recommend)
values
<foreach item="item" index="index" collection="list" separator=",">
(#{item.sourceID,jdbcType=INTEGER}, #{item.descript,jdbcType=NVARCHAR}, #{item.product,jdbcType=NVARCHAR},#{item.innerVersion,jdbcType=NVARCHAR},#{item.outerVersion,jdbcType=NVARCHAR},
#{item.ROM,jdbcType=NVARCHAR}, #{item.createTime,jdbcType=NVARCHAR}, #{item.SN,jdbcType=NVARCHAR}, #{item.CSR,jdbcType=INTEGER}, #{item.recommend,jdbcType=INTEGER})
</foreach>
</insert>