Problem exporting a lot of data from database to .csv with java

I, thank for your attention.

I want to export a lot of data, really a lot of data (6 million of rows) to a .csv file using java. The app is a swing application, with JPA, using toplink (ojdbc14).

I have tried to use:

BufferedWriter RandomAccessFile FileChannel

etc etc, but the consumption of memory remains very high, causing a Java Heap Out of Memory Exception, although I set the maximun heap size in 800m (-Xmx800m).

My last version of the souce code:

...(more lines of code)

FileChannel channel = getRandomAccessFile(tempFile).getChannel();
Object[][] data = pag.getRawData(); //Database data in a multidimentional array

            for (int j = 0; j < data.length; j++) {
                write(data[j], channel); //write data[j] (an array) into the channel
                freeStringLine(data[j]); //data[j] is an array, this method sets all positions =null
                data[j] = nul开发者_StackOverflow社区l;//sets reference in null
            }

            channel.force(false); //force writing in file system (HD)
            channel.close(); //Close the channel
            pag = null; 

...(more lines of code)

 private void write(Object[] row, FileChannel channel) throws DatabaseException {
    if (byteBuff == null) {
        byteBuff = ByteBuffer.allocateDirect(1024 * 1024);
    }
    for (int j = 0; j < row.length; j++) {
        if (j < row.length - 1) {
            if (row[j] != null) {
                byteBuff.put(row[j].toString().getBytes());
            }
            byteBuff.put(SPLITER_BYTES);
        } else {
            if (row[j] != null) {
                byteBuff.put(row[j].toString().getBytes());
            }
        }
    }
    byteBuff.put("\n".toString().getBytes());        
    byteBuff.flip();
    try {
        channel.write(byteBuff);
    } catch (IOException ex) {
        throw new DatabaseException("Imposible escribir en archivo temporal de exportación : " + ex.getMessage(), ex.getCause());
    }
    byteBuff.clear();
}

Being 6 millions of rows, I don't want to store that data in memory while the file is created. I made many temp files (wtih 5000 rows each one), and at the final of the process, append all those temp files in a single one, using two FileChannel. However, the exception for lack of memory is launched before the joining.

Do you now another strategy for export a lot of data?

Thanks a lot for any ansmwer. Sorry for my English, I'm improving xD

The answer is to use a "stream" approach - ie read one row, write one row as you scroll through the dataset. You'll need to get the query result as a cursor and iterate through it, not get the whole result set.

In JPA, use code something like this:

ScrollableResults cursor = session.createQuery("from SomeEntity x").scroll();

while (cursor.next()) {
    writeToFile(cursor);
}

This means you only have one row in memory at a time, which is totally scalable to any number of rows and uses minimal memory (it's faster anyway).

Getting all rows at once in a result set is a convenience approach which works for small result set (which is most of the time), but as usual, convenience comes at a cost and it doesn't work in all situations.