Cayenne User Documentation
Iterating Through Data Rows

There are cases when the result sets are so large that even when fetching data rows, application can run out of memory. For instance, a user may be creating a report that requires in-memory processing of hundreds of thousands of database rows. In such cases normal Cayenne behavior of reading the whole `java.sql.ResultSet` in the memory before returning it to the user may result in an application exhausing all memory and crashing.

Cayenne solves this by allowing to obtain results in the form of `ResultIterator`. ResultIterator is connected to an open `java.sql.ResultSet`, therefore its methods may throw checked exceptions. ResultIterator returns data rows (not DataObjects) one at a time, reading them on demand from the open ResultSet. Each data row can be converted to a DataObject or accessed directly. Open ResultIterator locks the database connection, therefore ResultIterator always requires explicit closing in the user code.

| In web applications, programmers must ensure that no open ResultIterators are kept between HTTP requests. Failure to do so may result in too many database connections being locked, thus quickly exhausting connection pool.

When working with open ResultIterator, users still can perform any other database operations: select queries, traversing object relationships, etc. Any parallel data operation will internally check out an unused connection from the connection pool as it would normally do, while ResultIterator still locks its own connection.

ResultIterator annotated example:

    import java.util.List;
    import java.util.Map;
    import org.apache.cayenne.access.DataContext;
    import org.apache.cayenne.access.ResultIterator;
    import org.apache.cayenne.query.SelectQuery;
    import org.apache.cayenne.CayenneException;
    import org.apache.art.Artist;
    ...
    DataContext context;

    // create a usual query
    SelectQuery query = new SelectQuery(Artist.class);

    // On most databases it is important to use 'statementFetchSize' together
    // with ResultIterator, to avoid high memory consumption by the JDBC driver
    query.setStatementFetchSize(1000);

    // special "performIteratedQuery" method is used.
    // the method itself throws a CayenneException, so it may need
    // to be wrapped in one more try/catch
    ResultIterator it = context.performIteratedQuery(query);

    try {
       while(it.hasNextRow()) {
          // ResultIterator always returns data rows
          Map row = (Map) it.nextRow();

          // do something with the row...
          ...
       }
    }
    finally {
       try {
          // explicit closing of the iterator is required !!!
          it.close();
       }
       catch(CayenneException closeEx) {
           closeEx.printStackTrace();
       }
    }
    ...

Note that it is a good practice to always call `query.setStatementFetchSize(some_number)` before running an iterated query. Otherwise the underlying JDBC driver may store the entire ResultSet in memory.

.
Copyright ©2001-2010 Apache Software Foundation