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 Section 6.4.2, “ Large Result Set Proxies ”.

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

Example 4.16.  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.17.  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();