Writing a large ResultSet to a File

Question

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?

Answer 1

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.

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

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.

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();
        }
    }
}

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.

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.

Answer 2

From GitHub: https://github.com/OhadR/ohadr.common/blob/master/src/main/java/com/ohadr/common/utils/resultset/ResultSetConverters.java

public static void writeResultSetToWriter(ResultSet resultSet, PrintWriter writer) throws SQLException
{
ResultSetMetaData metadata = resultSet.getMetaData();
int numColumns = metadata.getColumnCount();
int numRows = 0;

while(resultSet.next())             //iterate rows
{
    ++numRows;
    JSONObject obj = new JSONObject();      //extends HashMap
    for (int i = 1; i <= numColumns; ++i)           //iterate columns
    {
        String column_name = metadata.getColumnName(i);
        obj.put(column_name, resultSet.getObject(column_name));
    }
    writer.println(obj.toJSONString());

    if(numRows % 1000 == 0)
        writer.flush();
}