4.10. Large Result Sets
Prev | Chapter 4. JDBC | Next

## 4.10. Large Result Sets

By default, Kodo uses standard forward-only JDBC result sets, and completely instantiates the results of queries on execution. When using a JDBC driver that supports version 2.0 or higher of the JDBC specification, however, you can configure Kodo to use scrolling result sets that may not bring all results into memory at once. You can also configure the number of result objects Kodo keeps references to, so that you can traverse potentially enormous amounts of data without exhausting JVM memory. On-demand loading is also applied to extent iterators, and can be configured for individual collection and map fields via large result set proxies .

To configure Kodo's handling of result sets, use the following properties:

- `kodo.FetchBatchSize`: The number of objects to instantiate at once when traversing a result set. This number will be set as the fetch size on JDBC `Statement` objects used to obtain result sets. It also factors in to the number of objects Kodo will maintain a hard reference to when traversing a query result.

  The fetch size defaults to -1, meaning all results will be instantiated immediately on query execution. A value of 0 means to use the JDBC driver's default batch size. Thus to enable large result set handling, you must set this property to 0 or to a positive number.

- `kodo.jdbc.ResultSetType`: The type of result set to use when executing queries and traversing extents. This property accepts the following values, each of which corresponds exactly to the same-named `java.sql.ResultSet` constant:

  - `forward-only`: This is the default.

  - `scroll-sensitive`

  - `scroll-insensitive`

  Different JDBC drivers treat the different result set types differently. Also, not all drivers support all types.

- `kodo.jdbc.FetchDirection`: The expected order in which you will access the query results. This property affects the type of datastructure Kodo will use to hold the results, and is also given to the JDBC driver in case it can optimize for certain access patterns. This property accepts the following values, each of which corresponds exactly to the same-named `java.sql.ResultSet` FETCH constant:

  - `forward`: This is the default.

  - `reverse`

  - `unknown`

  Not all drivers support all fetch directions.

- `kodo.jdbc.LRSSize`: The strategy Kodo will use to determine the size of result sets. This property is only used if you change the fetch batch size from its default of -1, so that Kodo begins to use on-demand result loading. Available values are:

  - `query`: This is the default. The first time you ask for the size of a query result, Kodo will perform a `SELECT COUNT(*)` query to determine the number of expected results. Note that depending on transaction status and settings, this can mean that the reported size is slightly different than the actual number of results available.

  - `last`: If you have chosen a scrollable result set type, this setting will use the `ResultSet.last` method to move to the last element in the result set and get its index. Unfortunately, some JDBC drivers will bring all results into memory in order to access the last one. Note that if you do not choose a scrollable result set type, then this will behave exactly like `unknown`. The default result set type is `forward-only`, so you must change the result set type in order for this property to have an effect.

  - `unknown`: Under this setting Kodo will return `Integer.MAX_VALUE` as the size for any query result that uses on-demand loading. This is allowed by the JDO specification, but clearly isn't helpful if you actually need the number of returned results.

Example 4.11. Specifying Result Set Defaults

    kodo.FetchBatchSize: 20
    kodo.jdbc.ResultSetType: scroll-insensitive
    kodo.jdbc.FetchDirection: forward
    kodo.jdbc.LRSSize: last

Many Kodo JDO runtime components such as the `KodoPersistenceManager`, `KodoQuery`, and `KodoExtent` also have methods to configure these properties on a case-by-case basis through their fetch configuration object.

Example 4.12. Specifying Result Set Behavior at Runtime

    import java.sql.*;
    import kodo.query.*;
    import kodo.jdbc.runtime.*;

    ...

    KodoQuery kq = (KodoQuery) pm.newQuery (MyClass.class, "foo == bar");
    JDBCFetchConfiguration fetch = (JDBCFetchConfiguration)
        kq.getFetchConfiguration ();
    fetch.setFetchBatchSize (20);
    fetch.setResultSetType (ResultSet.TYPE_SCROLL_INSENSITIVE);
    fetch.setFetchDirection (ResultSet.FETCH_FORWARD);
    fetch.setLRSSize (JDBCFetchConfiguration.SIZE_LAST);
    Collection results = (Collection) kq.execute ();

To facilitate users who require random access to query results, Kodo JDO always returns an implementation of `java.util.List` from calls to `Query.execute`. Remember, though, that other JDO implementations might choose to only implement the `java.util.Collection` interface in their query result objects. Also, note that unless ordering is specified in your query, there is no guarantee that multiple executions of the same query will return their results in the same order.

Example 4.13. Using Random Access Query Results in a Portable Fashion

    // we want to ensure that we always order the results in the same way
    Query query = pm.newQuery (Product.class, "productName == \"Stereo\"");
    query.setOrdering ("productCode ascending");

    Collection results = (Collection) query.execute ();
    List resultList;
    if (results instanceof List)      // always the case with Kodo JDO
        resultList = (List) results;
    else
        // portable, but it will wind up instantiating all elements in
        // collection, which might be a huge number of objects
        resultList = new ArrayList (results);

    // get start and end indexes of results to display from web request
    int start = Integer.parseInt (jspRequest.getParameter ("start"));
    int end = Integer.parseInt (jspRequest.getParameter ("end"));

    // print info about each product in list range from "start" to "end"
    for (int i = start; i < end && i < resultList.size (); i++)
        out.print ("Stereo #" + i + ": "
            + ((Product) resultList.get (i)).getDescription ());

    query.close (results);

Prev | Up | Next
4.9. Runtime Access to JDBC Connections | Home | 4.11. SQL Statement Ordering & Foreign Keys