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.
You can also configure on-demand loading for individual collection and map fields via large result set proxies. See Section 6.4.2, “ 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 datastructure
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 4.15. 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 Chapter 9, Runtime Extensions .
Example 4.16. 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();