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]](img/note.gif) | 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 JDBCStatementobjects 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-namedjava.sql.ResultSetconstant:forward-only: This is the default.scroll-sensitivescroll-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-namedjava.sql.ResultSetFETCH constant:forward: This is the default.reverseunknown
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 aSELECT 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 theResultSet.lastmethod 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 likeunknown. The default result set type isforward-only, so you must change the result set type in order for this property to have an effect.unknown: Under this setting Kodo will returnInteger.MAX_VALUEas the size for any query result that uses on-demand loading.
Example 4.15. 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.16. Specifying Result Set Behavior at Runtime
JPA:
import java.sql.*;
import org.apache.openjpa.persistence.*;
import org.apache.openjpa.persistence.jdbc.*;
...
Query q = em.createQuery ("select m from Magazine m where m.title = 'JDJ'");
OpenJPAQuery oq = OpenJPAPersistence.cast (q);
JDBCFetchPlan fetch = (JDBCFetchPlan) oq.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 ();
