3.5.10. Large Result Sets

By default, OpenJPA 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 OpenJPA to use scrolling result sets that may not bring all results into memory at once. You can also configure the number of result objects OpenJPA 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 Large Result Set Proxies.

Use the following properties to configure OpenJPA’s handling of result sets:

  • openjpa.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 OpenJPA 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. * openjpa.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. * openjpa.jdbc.FetchDirection: The expected order in which you will access the query results. This property affects the type of data structure OpenJPA 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. * openjpa.jdbc.LRSSize: The strategy OpenJPA 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 OpenJPA 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, OpenJPA 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 OpenJPA will return Integer.MAX_VALUE as the size for any query result that uses on-demand loading.

Example 63. Specifying Result Set Defaults
<property name="openjpa.FetchBatchSize" value="20"/>
<property name="openjpa.jdbc.ResultSetType" value="scroll-insensitive"/>
<property name="openjpa.jdbc.FetchDirection" value="forward"/>
<property name="openjpa.jdbc.LRSSize" value="last"/>

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

Example 64. Specifying Result Set Behavior at Runtime
import java.sql.*;
import org.apache.openjpa.persistence.jdbc.*;

...

Query q = em.createQuery("select m from Magazine m where m.title = 'JDJ'");
OpenJPAQuery kq = OpenJPAPersistence.cast(q);
JDBCFetchPlan fetch = (JDBCFetchPlan) kq.getFetchPlan();
fetch.setFetchBatchSize(20);
fetch.setResultSetType(ResultSetType.SCROLL_INSENSITIVE);
fetch.setFetchDirection(FetchDirection.FORWARD);
fetch.setLRSSizeAlgorithm(LRSSizeAlgorithm.LAST);
List results = q.getResultList();