使用MySQL批处理对POI导入数据进行优化

对于要把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框架）

• 提交时的问题