how to write huge data in text file using JDBC without running out of memory

Question

I am trying to create a dump file from a database using JDBC. The file should be round about 300 mb in size containing 1.2 to 1.5 millions records across ten columns but I run out of memory at about 250 thousand.

My question is does java store the entire recordset in memory? I have set the recordset to be readable only forward in the hope that records already dumped would be cleared from memory but it does not seem to be the case.

Any help would be greatly appreciated.

Answer 1

You should use setFetchSize on the Statement object. The following example will only fetch 1000 records at a time from a ResultSet:

Connection con = DriverManager.getConnection("jdbc:my_subprotocol:my_subname");
Statement stmt = con.createStatement();
stmt.setFetchSize(1000);
ResultSet rs = stmt.executeQuery("SELECT * FROM your_table");

Here's the Javadoc:

http://java.sun.com/javase/6/docs/api/java/sql/Statement.html#setFetchSize(int)

Answer 2

Maybe you could run your batch in a shell and use > operator to redirect output to a file. For example java myProgramm > dump.txt will send the output to dump.txt file, instead of displaying it, into the shell window.

Answer 3

The fetch buffer/release behavior depends on the JDBC driver and also the JVM's garbage collector. Are you doing any buffering yourself before writing to a FileOutputStream (this may prevent your memory from being garbage collected)?

Try adjusting the Java heap size with the -Xms and -Xmx java runtime arguments. Example:

java -Xms1024M -Xmx1024M com.mypkg.MyResultSetReader

The above command will make 1GB of heap space available to your program. If this is a temporary tool or you don't expect the ResultSet size to grow, this may work as a permanent solution.

Answer 4

If you're retrieving all the data and then writing to the file as a second step there'll be some point where all of the db data is in memory. If you stream the data to the file this won't happen. i.e., instead of

data=...
while (rs.next()){
  ... add rs value to data
}
..write data to file

something like

file=...
while (rs.next()){
  write rs to file
}

Answer 5

Setting fetchSize should help. But really it depends on jdbc driver. But you can read data iteratively (by small portions):

stm = conn.prepareStatement("...where id > ? order by id");
stm.setMaxRows(100);

while(true) {
    stm.setInt(1, lastId);
    ResultSet results = stm.executeQuery();

    // process results and assign a new value to lastId 

    rs.close();
}