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?
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 ofSELECT
writes the selected rows to a file. The file is created on the server host, so you must have theFILE
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, thecharacter_set_filesystem
system variable controls the interpretation of the file name.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With