openCsv解析csv文件创建表并导入数据

最新推荐文章于 2023-03-02 11:36:09 发布

winsanity

最新推荐文章于 2023-03-02 11:36:09 发布

阅读量442

点赞数

文章标签： spring boot

本文链接：https://blog.csdn.net/winsanity/article/details/123356328

版权

openCsv解析csv文件创建表并导入数据

1.添加maven依赖

        <dependency>
            <groupId>com.opencsv</groupId>
            <artifactId>opencsv</artifactId>
            <version>4.6</version>
        </dependency>

2.实现代码

/**
     * csv文件解析并入库
     *
     * @param file
     * @param datasourceId
     * @return
     */
    @Override
    public void addCsvDataSet(MultipartFile file, String datasourceId) throws IOException {


        //获取数据源
        DatabaseInfo databaseInfo = sjztBaseDatasourceDao.getSjztBaseDatasourceById(datasourceId);
        JdbcTemplate jdbcTemplate = DataSourceUtils.getJdbcTemplate(
                sjztBaseDatasourceService.databaseInfoPwdDecode(databaseInfo));


        int batch_count = 5000;
        // opencsv读取csv文件
        InputStreamReader is = new InputStreamReader(file.getInputStream(), "utf-8");
        CSVParser csvParser = new CSVParserBuilder().withSeparator(',').build();
        CSVReader csvReader = new CSVReaderBuilder(is).withCSVParser(csvParser).build();
        String[] headStrs;

        // 读取第一行
        headStrs = csvReader.readNext();

        StringBuilder sb = new StringBuilder();
        sb.append("create table ").append(dataSet.getTableName()).append(" ( ");

        // 拼接数据表字段
        for(int i = 0;i<headStrs.length;i++){
            sb.append(headStrs[i]).append(" ").append("TEXT,");
        }

        // 去除最后一个逗号
        sb.deleteCharAt(sb.length() - 1);
        sb.append(" )");
        log.info(sb.toString());

        // 创建csv表
        jdbcTemplate.execute(sb.toString());

        String[] dataStr;
        List<Object[]> csvList = new ArrayList<>();

        // 拼接insert语句
        StringBuilder insertSql = new StringBuilder();
        insertSql.append(" INSERT INTO ");
        insertSql.append(dataSet.getTableName());
        insertSql.append(" (");
        StringBuilder columnSql = new StringBuilder();
        StringBuilder paramSql = new StringBuilder();
        for(int i = 0 ; i< headStrs.length ; i ++){
            columnSql.append(",");
            columnSql.append(headStrs[i]);
            paramSql.append(",");
            paramSql.append("?");
        }
        insertSql.append(columnSql.substring(1));
        insertSql.append(") VALUES (");
        insertSql.append(paramSql.substring(1));
        insertSql.append(")");

        while((dataStr = csvReader.readNext())!=null){
            csvList.add(dataStr);
            // 5000条批量插入数据库
            if (csvList.size() >= batch_count) {

                log.info("{}条数据，开始存储数据库！", csvList.size());

                // 数据入库
                jdbcTemplate.batchUpdate(insertSql.toString(),csvList);

                log.info("存储数据库成功！");
				// 清理csvList
                csvList.clear();
            }
        }

        // 最后剩余数据入库
        if(csvList.size()>0) jdbcTemplate.batchUpdate(insertSql.toString(),csvList);

    }