使用流读取数据量大的文件并存到mysql数据库中

最新推荐文章于 2024-04-26 20:22:41 发布

小码农ovo

最新推荐文章于 2024-04-26 20:22:41 发布

阅读量1.3k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： java 文章标签：超大文件读取

本文链接：https://blog.youkuaiyun.com/qq_33643072/article/details/80115887

java 专栏收录该内容

11 篇文章

订阅专栏

本文介绍了一种使用Java程序批量将CSV文件中的数据导入到MySQL数据库的方法。通过调整批次大小和数据库连接设置来提高导入效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.concurrent.atomic.AtomicLong;

public class MozillaCellImporter {
    protected File file;
    protected String sqlTemplate = "INSERT user (name, age) VALUES (?, ?)";
    protected PreparedStatement statement;

    protected int batchSize = 100000;//默认一次存入数据库数量这里是10W条
    protected String url = "jdbc:mysql://localhost:3306/DBName?serverTimezone=UTC&useSSL=true&rewriteBatchedStatements=true";
    protected String username = "root";
    protected String password = "123456";

    protected Logger logger = LoggerFactory.getLogger(getClass());

    public MozillaCellImporter(String filepath, int batchSize){
        this.file = new File(filepath);
        this.batchSize = batchSize;
        if(!this.file.exists())
            throw new RuntimeException("file not exists: "+filepath);
    }

    public MozillaCellImporter dbSetting(String url, String username, String password){
        this.url = url;
        this.username = username;
        this.password = password;

        return this;
    }

    public String run() throws Exception {
        Connection connection = connection();
        statement = connection.prepareStatement(sqlTemplate);

        AtomicLong lineCount = new AtomicLong(0);
        AtomicLong count = new AtomicLong(0);

        try(BufferedReader reader = Files.newBufferedReader(file.toPath(), StandardCharsets.UTF_8)){
            reader.lines().forEach(line->{
                lineCount.addAndGet(1);
                if(lineCount.longValue() > 1){去掉第一条数据，根据自己的情况而定
                    try {
                        String temp[] = line.split(",");
                        statement.setInt(1, Integer.valueOf(temp[0]));
                        statement.setInt(2, Integer.valueOf(temp[1]));
                        statement.addBatch();
                        if(lineCount.longValue() % batchSize == 0){
                            count.addAndGet(statement.executeBatch().length);
                            logger.info(String.format("[%15d] insert done, batch size=%d....", lineCount.longValue(), batchSize));
                            statement.clearBatch();
                        }
                    } catch (SQLException e) {
                        logger.error(String.format("error on parse line : %s : %s", line, e.getMessage()));
                    }
                }
            });
        } catch (IOException e) {
            e.printStackTrace();
        }
        count.addAndGet(statement.executeBatch().length);
        statement.close();
        connection.close();

        return String.format("%d,%d", lineCount.longValue(), count.longValue());
    }

    private Connection connection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        return DriverManager.getConnection(url, username, password);
    }
}

s是文件的路径

100000是一次存入数据库的数量

返回result格式是文件数据量,成功存入数据库的数量

String result = null;
try {
     result = new MozillaCellImporter(s, 100000).dbSetting(jdbcUrl, jdbcUsername, jdbcPassword).run();
} catch (Exception e) {
     e.printStackTrace();
}
String[] strings = result.split(",");