以CSV文件导入MySQL的批量数据插入操作之Java操作

最近工作涉及将excel中的数据导入到MySQL数据库，由于Excel中数据并不规范，需要进行二次加工。将excel中数据加工后，通过mybatis批量插入mySQL数据库，其相关联的技术点比较简单，经过半天的编写，算是把任务完成了。但测试时性能太差，处理2W条数据的excel文件需要将近两分钟，后来在网上了解到了通过load data infile的方式，让数据库去加载csv数据文件，效率能提高几十倍，所以小可打算尝试一下，最终效果真的很不错，加载5W条数据的excel文件时间可以控制在6秒以内【小可已经很满足了】，应该还可以优化，但是限于小可的能力，先把完成的工作做一个总结，也算是一个分享吧。

编程语言：Java

平台框架：Spring、Spring MVC、MyBatis

解析Excel：Apache POI

生成CSV：Apache commons-csv

一、解析excel

        Workbook workBook = parseExcelGetWorkbook(is,fileName);        Sheet sheet = workBook.getSheetAt(0);        int rowsNum = sheet.getPhysicalNumberOfRows();        List<List<String>> resultList = Lists.newArrayList();        Row rootRow = sheet.getRow(0);        int cellsNum = rootRow.getPhysicalNumberOfCells();        for(int j=1;j<rowsNum;j++){            Row row = sheet.getRow(j);            List<String> list = new ArrayList<String>();            for(int i=0;i<cellsNum;i++){                Cell cell = row.getCell(i);                if(cell!=null){                    cell.setCellType(Cell.CELL_TYPE_STRING);                    list.add(StringUtils.noNull(cell.getStringCellValue()));                }else{                    list.add("");                }            }            resultList.add(list);        }

其中resultList则为excel中所有数据，通过Mybatis进行批量插入数据库处理

        StringBuffer sb = new StringBuffer("insert into ").append(tableName).append("(");        StringBuilder messageFormatStr = new StringBuilder("(");        for(int i=0;i<columnList.size();i++){            if(i!=0){                sb.append(",");                messageFormatStr.append(",");            }            sb.append(columnList.get(i));            messageFormatStr.append("#'{'dataList[{0,number,#}]."+columnList.get(i)+"}");        }        sb.append(") values ");        messageFormatStr.append(")");        MessageFormat messageFormat = new MessageFormat(messageFormatStr.toString());        for(int i=0;i<dataList.size();i++){            sb.append(messageFormat.format(new Object[]{i}));            if (i < dataList.size() - 1) {                sb.append(",");            }        }

以上是通过Mybatis将数据插入的MySQL数据库中，性能非常差。如果几千条数据还能接受，上W条数据就会非常卡，一下通过生成CSV文件操作方式，性能提升几十倍

1.通过解析excel处理数据【解析excel部分省略】，然后根据数据生成.CSV文件

准备commons-csv.jar

使用的API主要有CSVFormat.class和CSVPrinter.class

CSVFormat csvFileFormat = CSVFormat.DEFAULT.withRecordSeparator("\n");//创建CSVFormat ，每行记录间隔符使用换行【\n】

说明：此处对CSV文件的具体使用方式不做过多介绍，如果不了解可以查阅相关资料

创建文件输出对象Writer，此文中使用BufferedWriter

BufferedWriter fileWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(new File(filepath)),"UTF-8"));

说明：此处使用UTF-8编码，这样确保生成CSV文件的编码格式为UTF-8【根据项目环境自行配置utf-8,gbk....】，如果此处不做转码处理，可能导致csv文件中的中文字段不能正确的导入mySQL数据库当中

我的项目中不涉及到生成表头，如果涉及到生成表头的话，可以通过如下方式设置：

csvFilePrinter.printRecord(new String[]{"id","username","password","name","age"});

CSVPrinter csvFilePrinter = new CSVPrinter(fileWriter, csvFileFormat);//根据格式化对象和输出对象创建CSV文件的写入对象

从Excel文件中获取数据，将数据进行处理，将处理后的数据通过csvFilePrinter对象写入到目标文件中

            Workbook workBook = parseExcelGetWorkbook(is,fileName);            Sheet sheet = workBook.getSheetAt(0);            int rowsNum = sheet.getPhysicalNumberOfRows();            Row rootRow = sheet.getRow(0);            int cellsNum = rootRow.getPhysicalNumberOfCells();            StringBuilder recordStr = new StringBuilder();            for(int j=1;j<rowsNum;j++){                Row row = sheet.getRow(j);                recordStr.append(StringUtils.noNull(j)+"&%$");                for(int i=0;i<cellsNum;i++){                    Cell cell = row.getCell(i);                    if(cell!=null){                        cell.setCellType(Cell.CELL_TYPE_STRING);                        recordStr.append(StringUtils.noNull(cell.getStringCellValue())+"&%$");                    }else{                        recordStr.append("");                    }                }                recordStr.deleteCharAt(recordStr.length()-1);                csvFilePrinter.printRecord(recordStr.toString());                recordStr.delete(0, recordStr.length()-1);            }

说明：recordStr.append(StringUtils.noNull(j)+"&%$");中的“&%$”是每个字段间的分割符，因为我们处理的数据比较特殊，可以用数据不特殊可以使用逗号，空格只来的，比如

recordStr.append(StringUtils.noNull(j)+",");或是recordStr.append(StringUtils.noNull(j)+"\t");都可以。

 csvFilePrinter.printRecord(recordStr.toString());//写入每行数据

这样就生成了目标csv文件，文件地址则为上述的filepath，到此csv文件生成完毕，剩下的工作则通过java操作数据库，将csv文件导入到mySQL数据库中，我们测试了两种方式，分别为原生态JDBC操作和Mybatis操作，在此将两种处理方式的代码粘贴下来供大家参考：

JDBC方式：

        DruidDataSource dataSource = SpringContextHolder.getBean("dataSource");        Connection conn = null;        PreparedStatement pstmt = null;        try {            conn = dataSource.getConnection();            String sql = "LOAD DATA LOCAL INFILE '" + filepath                    + "' INTO TABLE " + tableName + " "                    + " FIELDS TERMINATED BY '&%$'";            pstmt = conn.prepareStatement(sql);            if (pstmt.isWrapperFor(com.mysql.jdbc.Statement.class)) {                com.mysql.jdbc.PreparedStatement mysqlStatement = pstmt                        .unwrap(com.mysql.jdbc.PreparedStatement.class);                result = mysqlStatement.executeUpdate();            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            try {                if(conn != null){                    conn.close();                }                if(pstmt != null){                    pstmt.close();                }            } catch (Exception e2) {                e2.printStackTrace();            }        }

Mybatis方式：

将tableName和filepath作为参数，正常调用Mybatis功能即可，在此将我们的代码片段供大家参考

service中调用：

mapper.insertDataByCSVFile(filepath, tableName);

mapper接口：

    @SelectProvider(type=DataFileSqlProvider.class,method="getInsertDataByCSVFileSql")    public void insertDataByCSVFile(@Param("filepath") String filepath, @Param("tableName") String tableName);

    public String getInsertDataByCSVFileSql(Map<String,Object> param){        String filepath = StringUtils.noNull(param.get("filepath"));        String tableName = StringUtils.noNull(param.get("tableName"));        String sql = "LOAD DATA LOCAL INFILE '" + filepath                + "' INTO TABLE " + tableName + " "                + " FIELDS TERMINATED BY '&%$'";        return sql;    }

说明：如果是本地则

LOAD DATA LOCAL INFILE

服务器则为：

LOAD DATA INFILE