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

问题描述

我正在尝试将大型ResulSet（~1mm行）写入单个文件。在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（）循环内，您需要将数据立即写入文件，而不是将其保存在<$ c $中c>列表或者什么。

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:

ResultSet

默认情况下，ResultSet完全检索并存储在内存中。在大多数情况下，这是最有效的操作方式，并且由于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 ... INTO OUTFILE'file_name '形式的 SELECT 将选定的行写入文件。该文件是在服务器主机上创建的，因此您必须具有 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屋！