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

最新推荐文章于 2024-09-25 11:05:50 发布
最新推荐文章于 2024-09-25 11:05:50 发布
阅读量5.6k 收藏 9
点赞数 1
分类专栏： mysql 文章标签： csv mysql

最近工作涉及将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

[java] view plain copy print ?
  1. Workbook workBook = parseExcelGetWorkbook(is,fileName);  
  2. Sheet sheet = workBook.getSheetAt(0);  
  3. int rowsNum = sheet.getPhysicalNumberOfRows();  
  4. List<List<String>> resultList = Lists.newArrayList();  
  5. Row rootRow = sheet.getRow(0);  
  6. int cellsNum = rootRow.getPhysicalNumberOfCells();  
  7. for(int j=1;j<rowsNum;j++){  
  8.     Row row = sheet.getRow(j);  
  9.     List<String> list = new ArrayList<String>();  
  10.     for(int i=0;i<cellsNum;i++){  
  11.         Cell cell = row.getCell(i);  
  12.         if(cell!=null){  
  13.             cell.setCellType(Cell.CELL_TYPE_STRING);  
  14.             list.add(StringUtils.noNull(cell.getStringCellValue()));  
  15.         }else{  
  16.             list.add("");  
  17.         }  
  18.     }  
  19.     resultList.add(list);  
  20. }  
        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进行批量插入数据库处理

[java] view plain copy print ?
  1. StringBuffer sb = new StringBuffer("insert into ").append(tableName).append("(");  
  2. StringBuilder messageFormatStr = new StringBuilder("(");  
  3. for(int i=0;i<columnList.size();i++){  
  4.     if(i!=0){  
  5.         sb.append(",");  
  6.         messageFormatStr.append(",");  
  7.     }  
  8.     sb.append(columnList.get(i));  
  9.     messageFormatStr.append("#'{'dataList[{0,number,#}]."+columnList.get(i)+"}");  
  10. }  
  11. sb.append(") values ");  
  12. messageFormatStr.append(")");  
  13. MessageFormat messageFormat = new MessageFormat(messageFormatStr.toString());  
  14. for(int i=0;i<dataList.size();i++){  
  15.     sb.append(messageFormat.format(new Object[]{i}));  
  16.     if (i < dataList.size() - 1) {  
  17.         sb.append(",");  
  18.     }  
  19. }  
        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对象写入到目标文件中

[java] view plain copy print ?
  1. Workbook workBook = parseExcelGetWorkbook(is,fileName);  
  2. Sheet sheet = workBook.getSheetAt(0);  
  3. int rowsNum = sheet.getPhysicalNumberOfRows();  
  4. Row rootRow = sheet.getRow(0);  
  5. int cellsNum = rootRow.getPhysicalNumberOfCells();  
  6. StringBuilder recordStr = new StringBuilder();  
  7. for(int j=1;j<rowsNum;j++){  
  8.     Row row = sheet.getRow(j);  
  9.     recordStr.append(StringUtils.noNull(j)+"&%$");  
  10.     for(int i=0;i<cellsNum;i++){  
  11.         Cell cell = row.getCell(i);  
  12.         if(cell!=null){  
  13.             cell.setCellType(Cell.CELL_TYPE_STRING);  
  14.             recordStr.append(StringUtils.noNull(cell.getStringCellValue())+"&%$");  
  15.         }else{  
  16.             recordStr.append("");  
  17.         }  
  18.     }  
  19.     recordStr.deleteCharAt(recordStr.length()-1);  
  20.     csvFilePrinter.printRecord(recordStr.toString());  
  21.     recordStr.delete(0, recordStr.length()-1);  
  22. }  
            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方式：

[java] view plain copy print ?
  1. DruidDataSource dataSource = SpringContextHolder.getBean("dataSource");  
  2. Connection conn = null;  
  3. PreparedStatement pstmt = null;  
  4. try {  
  5.     conn = dataSource.getConnection();  
  6.     String sql = "LOAD DATA LOCAL INFILE '" + filepath  
  7.             + "' INTO TABLE " + tableName + " "  
  8.             + " FIELDS TERMINATED BY '&%$'";  
  9.     pstmt = conn.prepareStatement(sql);  
  10.     if (pstmt.isWrapperFor(com.mysql.jdbc.Statement.class)) {  
  11.         com.mysql.jdbc.PreparedStatement mysqlStatement = pstmt  
  12.                 .unwrap(com.mysql.jdbc.PreparedStatement.class);  
  13.         result = mysqlStatement.executeUpdate();  
  14.     }  
  15. } catch (SQLException e) {  
  16.     e.printStackTrace();  
  17. } finally {  
  18.     try {  
  19.         if(conn != null){  
  20.             conn.close();  
  21.         }  
  22.         if(pstmt != null){  
  23.             pstmt.close();  
  24.         }  
  25.     } catch (Exception e2) {  
  26.         e2.printStackTrace();  
  27.     }  
  28. }  
        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接口：

[java] view plain copy print ?
  1. @SelectProvider(type=DataFileSqlProvider.class,method="getInsertDataByCSVFileSql")  
  2. public void insertDataByCSVFile(@Param("filepath") String filepath, @Param("tableName") String tableName);  
    @SelectProvider(type=DataFileSqlProvider.class,method="getInsertDataByCSVFileSql")
    public void insertDataByCSVFile(@Param("filepath") String filepath, @Param("tableName") String tableName);
[java] view plain copy print ?
  1. public String getInsertDataByCSVFileSql(Map<String,Object> param){  
  2.     String filepath = StringUtils.noNull(param.get("filepath"));  
  3.     String tableName = StringUtils.noNull(param.get("tableName"));  
  4.     String sql = "LOAD DATA LOCAL INFILE '" + filepath  
  5.             + "' INTO TABLE " + tableName + " "  
  6.             + " FIELDS TERMINATED BY '&%$'";  
  7.     return sql;  
  8. }  
    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;
    }
说明：如果是本地则
[java] view plain copy print ?
  1. LOAD DATA LOCAL INFILE  
LOAD DATA LOCAL INFILE

服务器则为：

[java] view plain copy print ?
  1. LOAD DATA INFILE  
麦克是个程序员
关注
专栏目录
利用java语言将csv格式数据导入mysql数据库
cqn2bd2b的博客
09-18 1286
");");");}}
Java千万数据导入mysql_java之5分钟插入千万条数据
weixin_42565755的博客
02-16 1535
虽说不一定5分钟就插入完毕，因为取决去所插入的字段，如果字段过多会稍微慢点，但不至于太慢。10分钟内基本能看到结果。之前我尝试用多线程来实现数据插入(百万条数据)，半个多小时才二十多万条数据。线程池数据插入核心代码:ExecutorService executorService = Executors.newFixedThreadPool();executorService.submit(new ...
java实现批量导入.csv文件到mysql数据库
08-25
主要为大家详细介绍了java实现批量导入.csv文件到mysql数据库，具有一定的参考价值，感兴趣的小伙伴们可以参考一下
Java中使用CSVPrinter保存数据到CSV文件
最新发布
Leon_Jinhai_Sun的博客
09-25 409
Java中使用CSVPrinter保存数据到CSV文件
java使用javacsv读取csv文件 导入Mysql数据库
06-08
NULL 博文链接：https://qq-24665727.iteye.com/blog/2339850
利用JAVA程序批量导入csv数据到MySQL数据库
每一点星光，都是一个来自其他世界的问候
07-25 2912
本文概要说明了如何通过Java程序实现对CSV文件向MySQL的批量导入方法，最后有简单介绍了如何利用MySQL提供的语句或函数实现数据的行列转换。
Java向mysql批量存储数据_java大批量数据导入（MySQL）
weixin_39967096的博客
01-27 1197
© 版权声明：本文为博主原创文章，转载请注明出处最近同事碰到大批量数据导入问题，因此也关注了一下。大批量数据导入主要存在两点问题：内存溢出和导入速率慢。内存溢出：将文件中的数据全部取出放在集合中，当数据过多时就出现Java内存溢出，此时可通过调大JVM的最大可用内存(Xmx)解决，但终究不是王道。MySQL支持一条SQL语句插入多条记录的操作，并且效率比单条插入快的不是一点点；但是MySQL一次可...
用java把csv导入mysql_将CSV导入mysql表
weixin_36326008的博客
02-01 1225
回答(12)2 years ago您可以直接将MYSQL链接到它并使用以下SQL语法上载信息，而不是编写脚本以从CSV文件中提取信息 .要将Excel文件导入MySQL，请先将其导出为CSV文件 . 从生成的CSV文件中删除CSV Headers 以及Excel可能放在CSV文件末尾的空数据 .然后，您可以通过运行以下命令将其导入MySQL表：load data local infile 'uni...
将多个csv文件数据导入到数据库对应表中
11-29
将多个csv文件数据导入到数据库对应表中,需要注意各表心得主键获取并插入到关联表中，时间格式，值是null的字符串处理等事项。
Java-Spring-Batch-Job:将CSV文件处理到MySQL数据库中的简单解决方案
05-03
Java-Spring-Batch-Job 它是什么？ “ Spring Batch提供了可重用的功能，这些功能对于处理大量记录至关重要，包括日志记录/跟踪，事务管理，作业处理统计信息，作业重新启动，跳过和资源管理。它还提供了更高级的技术服务和功能，这些功能将极大地支持通过优化和分区技术，可以实现高容量和高性能批处理作业。简单以及复杂的高容量批处理作业都可以以高度可扩展的方式利用框架来处理大量信息。” 我做了什么 读取一组测试分数，将每个分数处理到其分数，然后将更新的数据写入MySQL数据库表。 我使用提供的'scores.txt'测试用例作为程序的输入。 它在csv文件夹下提供。 'Coding_challenge.out'是从MySQL数据库输出表的输出文件。 “ database.sql”是使用mysqldump生成的数据库模式SQL。
java的poi技术读取Excel数据到MySQL
08-13
应用java的poi技术读取Excel数据到MySQL的实例源码，对学习Java编程技术有所帮助,与大家分享。
导入.csv文件到mysql数据库
m0_37561039的博客
10-31 441
首先通过如下命令获取到mysql的secure_file_priv的值: show variables like '%secure%' secure_file_priv 的值是在对mysql导入导出csv文件时的路径，mysql的文件只能导入导出到此路径，我的的mysql数据库的 secure_file_priv的值为C:/ProgramData/MySQL/MySQL Server 5.7...
java将.csv文件导入mysql中，结合springmvc + mybatis
一个农民工的博客
08-01 3720
最近公司有这个需求，将.csv文件导入数据库中，之前是导入mysql，后面由于客户需要用oracle，又将数据导入oracle中（苦笑），所以讲.csv文件导入mysql和oracle都用了。这里主要是说说如何将.csv文件导入mysql中，后面在说说导入oracle中。 这里是结合的springmvc + mybatis的方式进行操作的，下面说说我的步骤： 第一步：建立数据库和对应的表，这里...
记一次从csv文件导入6千万数据到Mysql数据库
hingzq的博客
03-13 884
接到客户方需求，提供了一份2.7G的CSV文件，要求去更新数据库。 第一回合，刚拿到数据的时候，第一想法是直接更新这么多数据肯定不合理的，而且性能非常差，搞不好就卡死数据库。所以想到的是对改数据进行拆分，然后问题来了，由于数据量太大，Excel、txt、Notepad++都无法打开，开始各种查资料，找到了Sublime、和Power BI可以打开，然后开始没千万数据去拆分，后面省略万字。。。。。...
通过java把后缀为.csv的文件解析后传入数据库里
ididididdid的博客
07-21 1872
通过java把后缀为.csv的文件解析后传入数据库里
SpringBoot+MyBatis数据批量导入数据库
weixin_45150104的博客
12-01 2135
    年轻人不讲武德，耗子尾汁。哈哈，来吧，新鲜出炉的数据批量导入数据库，这上周优化代码，采用批量导入数据，速度处理起来就是快很多很多，闲话少说，直接扔干货。     组长让完成csv文件导入数据库，之前的csv文件内容大概也就几千条数据，在运行的时候没有丝毫的感觉慢，但是后期一下子加到了大概三万多条，一下子让我等了大概八分钟作用，乖乖类，没办法，干吧，优化代码。之前我采用的是放到list的集合中，每读取一条数据，然后进行调用
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值