Search code examples
javadesign-patternsjdbcexportdao

Streaming DAO Results in Java (Design Pattern)

Given following situation:

  • DAO which queries all rows from a table and maps them to a list of domain objects (pure JDBC)
  • A service which processes the list resulting from the DAO and renders certain list elements formatted to a file.

The problem here is that all the DAO results are hold in memory which becomes dangerous as the tables grows.

Question: Is there a pattern to read objects from a DAO in a streaming way that the service processes only one element at a time, writes it to the file output stream and then requests the next element from the result set? Is it in general a good idea to use a ResultSet as a return type of a DAO?

Solution

  • Even if you exposed the results set, which you shouldn't, you still don't get streaming semantics by default. For that you need to do something like this:

    stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
              java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

    You should encapsulate this within your DAO, you could return a custom iterator to process the results.

    See the mysql reference on the topic of streaming results.