对于要把Excel里的数据导入到数据库中,一般是选择使用POI获取到Excel里的数据,封装进对象,再一个一个add到List里,然后在mybatis的xml文件里使用foreach标签遍历数据,其实这样效率是很慢的,如果要导入的数据量比较大的话,是非常耗时的。于是就需要使用MySQL的批处理进行优化,下面就使用foreach导入数据与使用MySQL批处理导入数据进行一个对比,比较这两者的效率。
1、测试使用foreach导入数据
dao层代码:
/**
* 测试使用foreach导入数据
* @author LiJun
* @Date 2019/7/16
* @Time 10:51
* @param list
* @return
*/
int insertConsumeArea(List<Map> list);
对应的xml代码:
<!--测试使用foreach导入数据-->
<insert id="insertConsumeArea" parameterType="map" >
insert into ta_consume_area (
country_code, country_name
)
values
<foreach collection="list" item="item" separator =",">
(
#{item.countryCode},
#{item.countryName}
)
</foreach>
</insert>
service层与实现类的代码这里就不贴出来了
controller层代码:
@Slf4j
@Controller
@RequestMapping("test")
public class TestController {
@Autowired
private TestService testService;
/**
* 测试使用foreach导入数据
* @author LiJun
* @Date 2019/8/7
* @Time 16:20
* @param
* @return java.lang.String
*/
@RequestMapping(value = "insertConsumeArea")
@ResponseBody
public String insertConsumeArea(){
try {
Workbook workbook = POIUtil.getWorkbook(new File("C:\\Users\\20180721\\Desktop\\消费地区和消协机构、办理机构.xlsx"));
Sheet sheet = workbook.getSheetAt(0);//工作表编号
int rowNum = sheet.getLastRowNum();
List<Map> list = new ArrayList<>();
Map map;
long startTime = System.currentTimeMillis();
for (int i = 0;i < rowNum + 1;i++){
Row row = sheet.getRow(i);
String countryCode = PoiCellUtil.getCellValue(row.getCell(0));
String countryName = PoiCellUtil.getCellValue(row.getCell(1));
map = new HashMap();
map.put("countryCode",countryCode);
map.put("countryName",countryName);
list.add(map);
}
testService.insertConsumeArea(list);
log.info("测试使用foreach导入" + rowNum + "条数据耗时" + (System.currentTimeMillis() - startTime) / (1000) + "秒");
return "success";
}catch (Exception e){
log.error("测试使用foreach导入数据出现异常:",e);
return "error";
}
}
}
测试打印的结果:
2、测试使用批处理导入数据
dao层代码:
/**
* 测试使用批处理导入数据
* @author LiJun
* @Date 2019/7/17
* @Time 14:23
* @param map
* @return java.util.List<java.util.Map>
*/
int insertAgency(Map map);
对应的xml代码:
<!--测试使用批处理导入数据-->
<insert id="insertAgency" parameterType="map">
INSERT INTO ta_agency (
association_code,
association_name
)
VALUES
(
#{associationCode},
#{associationName}
)
</insert>
注意:这个是不需要service层和实现类的,因为可以在controller层里直接调用dao层里的方法
controller层代码:
@Slf4j
@Controller
@RequestMapping("test")
public class TestController {
@Autowired
private SqlSessionFactory sqlSessionFactory;
/**
* 测试使用批处理导入数据
* @author LiJun
* @Date 2019/8/8
* @Time 9:17
* @param
* @return
*/
@RequestMapping(value = "insertAgency")
@ResponseBody
public String insertAgency(){
//开启SQL批处理
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
//通过新的session获取mapper
TestDao testDao = sqlSession.getMapper(TestDao.class);
try {
Workbook workbook = POIUtil.getWorkbook(new File("C:\\Users\\20180721\\Desktop\\消费地区和消协机构、办理机构.xlsx"));
Sheet sheet = workbook.getSheetAt(1);//工作表编号
int rowNum = sheet.getLastRowNum();
Map map;
long startTime = System.currentTimeMillis();
for (int i = 0;i < rowNum + 1;i++){
Row row = sheet.getRow(i);
String countryCode = PoiCellUtil.getCellValue(row.getCell(0));
String countryName = PoiCellUtil.getCellValue(row.getCell(1));
map = new HashMap();
map.put("countryCode",countryCode);
map.put("countryName",countryName);
testDao.insertAgency(map);
if (i % 1000 == 0) {//每1000条数据提交一次
sqlSession.commit();//提交
sqlSession.clearCache();//清理缓存
map = null;
}
}
sqlSession.commit();//提交
sqlSession.clearCache();//清理缓存
log.info("测试使用批处理导入" + rowNum + "条数据耗时" + (System.currentTimeMillis() - startTime) / (1000) + "秒");
return "success";
}catch (Exception e){
log.error("测试使用批处理导入数据出现异常:",e);
return "error";
}finally {
if (sqlSession != null){
sqlSession.close();
}
}
}
}
测试打印的结果:
从测试打印的结果可以看到使用foreach导入3248条数据用了63秒,而使用MySQL批处理导入14391条数据只用了39秒,可见效率还是提升了不少。
3、涉及到的帮助类的代码
POIUtil.getWorkbook()方法代码:
/**
* 判断Excel的版本,获取Workbook
* @param file
* @return
* 2007后的版本要用XSSFWorkbook,不能用HSSFWorkbook
*/
public static Workbook getWorkbook(File file) {
try {
Workbook wb = null;
if (!file.exists()) {
throw new RuntimeException("文件不存在!");
} else if (file.getName().endsWith(XLS)) { // Excel 2003
wb = new HSSFWorkbook(new FileInputStream(file));
} else if (file.getName().endsWith(XLSx)) { // Excel 2007/2010
wb = new XSSFWorkbook(new FileInputStream(file));
}else{
throw new RuntimeException("文档格式不正确!");
}
return wb;
} catch (Exception e) {
throw new RuntimeException("Excel解析失败!");
}
}
PoiCellUtil.getCellValue()方法代码:
/**
* 获取单元格的值
* @param cell
* @return
*/
public static String getCellValue(Cell cell) {
if (cell == null)
return "";
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
return cell.getStringCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
return cell.getCellFormula();
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
String cellVal = NumberFormat.getInstance().format(cell.getNumericCellValue());
if(cellVal.indexOf(",") > -1){
cellVal = cellVal.replace(",", "");
}
return cellVal;
}
return "";
}
4、使用MySQL批处理需要注意的问题
- controller层注入的SqlSessionFactory是在spring-dao.xml里配置的bean(我这里用的是ssm框架)
- 提交时的问题