将大型 ResultSet 写入文件 [英] Writing a large ResultSet to a File

问题描述

我正在尝试将大型 ResulSet(约 1 毫米行)写入单个文件.在 Java 1.6 中是否有首选/有效的方法来执行此操作?

I'm tryin to write a large ResulSet (~1mm rows) to a single file. Is there a preferred/efficient way to do this in Java 1.6?

推荐答案

这取决于所使用的 JDBC 驱动程序.您需要指示 JDBC 驱动程序事先不将整个 ResultSet 加载到 Java 的内存中，而是在每个 next() 的每行基础上加载它 调用.然后，在 ResultSet#next() 循环内，您需要立即将数据写入文件，而不是将其保存在 List 或其他东西中.

That depends on the JDBC driver used. You need to instruct the JDBC driver to not load the entire ResultSet into Java's memory beforehand, but instead load it on a per-row basis on every next() call. Then, inside the ResultSet#next() loop, you need to write the data immediately to the file instead of holding it in List or something.

不清楚您使用的是什么 JDBC 驱动程序，但例如，可以指示 MySQL JDBC 驱动程序按照 MySQL JDBC 驱动程序文档:

It's unclear what JDBC driver you're using, but for example the MySQL JDBC driver could be instructed to serve the resultset on a per-row basis the following way as per the MySQL JDBC driver documentation:

默认情况下，ResultSets 被完全检索并存储在内存中.在大多数情况下，这是最有效的操作方式，并且由于 MySQL 网络协议的设计更易于实现.如果您正在处理具有大量行或大值的 ResultSet，并且无法在 JVM 中为所需内存分配堆空间，则可以告诉驱动程序一次将结果流式传输回一行.

ResultSet

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and can not allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

要启用此功能，您需要通过以下方式创建 Statement 实例:

To enable this functionality, you need to create a Statement instance in the following manner:

 stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
 stmt.setFetchSize(Integer.MIN_VALUE);

这是一个具体的启动示例:

Here's a concrete kickoff example:

try (
    PrintWriter writer = new PrintWriter(new OutputStreamWriter(new BufferedOutputStream(new FileOutputStream("/records.txt")), "UTF-8"));
    Connection connection = dataSource.getConnection();
    Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
) {
    statement.setFetchSize(Integer.MIN_VALUE);

    try (ResultSet resultSet = statement.executeQuery("SELECT col1, col2, col3 FROM sometable")) {
        while (resultSet.next()) {
            writer.append(resultSet.getString("col1")).append(",")
                  .append(resultSet.getString("col2")).append(",")
                  .append(resultSet.getString("col3")).println();
        }
    }
}

顺便说一句，我会首先检查数据库是否没有内置的 SQL 支持，这可以更有效地做到这一点.例如，MySQL 有一个 SELECT INTO OUTFILE 构造为此.

By the way, I'd first check if the DB doesn't have builtin SQL support for this which can do this much more efficiently. For example, MySQL has a SELECT INTO OUTFILE construct for this.

SELECT 的 SELECT ... INTO OUTFILE 'file_name' 形式将选定的行写入文件.该文件是在服务器主机上创建的，因此您必须具有 FILE 权限才能使用此语法.file_name 不能是现有文件，这可以防止/etc/passwd 和数据库表等文件被破坏.从 MySQL 5.1.6 开始，character_set_filesystem 系统变量控制文件名的解释.

The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed. As of MySQL 5.1.6, the character_set_filesystem system variable controls the interpretation of the file name.

这篇关于将大型 ResultSet 写入文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！