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 database 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, allowing you to traverse potentially enormous amounts of data without exhausting JVM memory.

| Note
You can also configure on-demand loading for individual collection and map fields via large result set proxies. See Section 5.5.4.2, “Large Result Set Proxies”.

Use the following properties to configure Kodo's handling of result sets:

- `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 database queries. 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. 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.

Example 4.14. Specifying Result Set Defaults

JPA XML format:

    <property name="kodo.FetchBatchSize" value="20"/>
    <property name="kodo.jdbc.ResultSetType" value="scroll-insensitive"/>
    <property name="kodo.jdbc.FetchDirection" value="forward"/>
    <property name="kodo.jdbc.LRSSize" value="last"/>

JDO properties format:

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

Many Kodo runtime components also have methods to configure these properties on a case-by-case basis through their fetch configuration. See Chapter 9, Runtime Extensions.

Example 4.15. Specifying Result Set Behavior at Runtime

JPA:

    import java.sql.*;
    import kodo.persistence.jdbc.*;

    ...

    Query q = em.createQuery ("select m from Magazine m where m.title = 'JDJ'");
    KodoQuery kq = KodoPersistence.cast (q);
    JDBCFetchPlan fetch = (JDBCFetchPlan) kq.getFetchPlan ();
    fetch.setFetchSize (20);
    fetch.setResultSetType (ResultSet.TYPE_SCROLL_INSENSITIVE);
    fetch.setFetchDirection (ResultSet.FETCH_FORWARD);
    fetch.setLRSSize (JDBCFetchPlan.SIZE_LAST);
    List results = (List) q.getResultList ();

JDO:

    import java.sql.*;
    import kodo.jdo.jdbc.*;

    ...

    Query q = pm.newQuery (Magazine.class, "title == 'JDJ'");
    JDBCFetchPlan fetch = (JDBCFetchPlan) q.getFetchPlan ();
    fetch.setFetchSize (20);
    fetch.setResultSetType (ResultSet.TYPE_SCROLL_INSENSITIVE);
    fetch.setFetchDirection (ResultSet.FETCH_FORWARD);
    fetch.setLRSSize (JDBCFetchPlan.SIZE_LAST);
    List results = (List) q.execute ();

Prev | Up | Next
4.9. Statement Batching | Home | 4.11. Default Schema