| <html><head> |
| <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> |
| <title>9. Large Result Sets</title><link rel="stylesheet" href="css/docbook.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.72.0"><link rel="start" href="manual.html" title="Apache OpenJPA User's Guide"><link rel="up" href="ref_guide_dbsetup.html" title="Chapter 4. JDBC"><link rel="prev" href="ref_guide_dbsetup_retain.html" title="8. Configuring the Use of JDBC Connections"><link rel="next" href="ref_guide_schema_def.html" title="10. Default Schema"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">9. |
| Large Result Sets |
| </th></tr><tr><td width="20%" align="left"><a accesskey="p" href="ref_guide_dbsetup_retain.html">Prev</a> </td><th width="60%" align="center">Chapter 4. |
| JDBC |
| </th><td width="20%" align="right"> <a accesskey="n" href="ref_guide_schema_def.html">Next</a></td></tr></table><hr></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="ref_guide_dbsetup_lrs"></a>9. |
| Large Result Sets |
| </h2></div></div></div><a class="indexterm" name="d0e20042"></a><a class="indexterm" name="d0e20045"></a><p> |
| 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. |
| </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> |
| You can also configure on-demand loading for individual collection and map |
| fields via large result set proxies. See |
| <a href="ref_guide_pc_scos.html#ref_guide_pc_scos_proxy_lrs" title="5.4.2. Large Result Set Proxies">Section 5.4.2, “ |
| Large Result Set Proxies |
| ”</a>. |
| </p></div><p> |
| Use the following properties to configure OpenJPA's handling of result sets: |
| </p><div class="itemizedlist"><ul type="disc"><li><p> |
| <a class="indexterm" name="d0e20065"></a> |
| <a href="ref_guide_conf_openjpa.html#openjpa.FetchBatchSize" title="5.30. openjpa.FetchBatchSize"><code class="literal">openjpa.FetchBatchSize</code> |
| </a>: The number of objects to instantiate at once when traversing a result |
| set. This number will be set as the fetch size on JDBC <code class="classname">Statement |
| </code> 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. |
| </p><p> |
| 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. |
| </p></li><li><p> |
| <a class="indexterm" name="d0e20082"></a> |
| <a href="ref_guide_conf_jdbc.html#openjpa.jdbc.ResultSetType" title="6.10. openjpa.jdbc.ResultSetType"><code class="literal"> openjpa.jdbc.ResultSetType |
| </code></a>: 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 <code class="classname">java.sql.ResultSet</code> constant: |
| </p><div class="itemizedlist"><ul type="circle"><li><p> |
| <code class="literal">forward-only</code>: This is the default. |
| </p></li><li><p> |
| <code class="literal">scroll-sensitive</code> |
| </p></li><li><p> |
| <code class="literal">scroll-insensitive</code> |
| </p></li></ul></div><p> |
| Different JDBC drivers treat the different result set types differently. Not all |
| drivers support all types. |
| </p></li><li><p> |
| <a class="indexterm" name="d0e20117"></a> |
| <a href="ref_guide_conf_jdbc.html#openjpa.jdbc.FetchDirection" title="6.5. openjpa.jdbc.FetchDirection"><code class="literal"> |
| openjpa.jdbc.FetchDirection</code></a>: 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 |
| <code class="classname">java.sql.ResultSet</code> FETCH constant: |
| </p><div class="itemizedlist"><ul type="circle"><li><p> |
| <code class="literal">forward</code>: This is the default. |
| </p></li><li><p> |
| <code class="literal">reverse</code> |
| </p></li><li><p> |
| <code class="literal">unknown</code> |
| </p></li></ul></div><p> |
| Not all drivers support all fetch directions. |
| </p></li><li><p> |
| <a class="indexterm" name="d0e20152"></a> |
| <a href="ref_guide_conf_jdbc.html#openjpa.jdbc.LRSSize" title="6.7. openjpa.jdbc.LRSSize"><code class="literal"> openjpa.jdbc.LRSSize</code> |
| </a>: The strategy OpenJPA will use to determine the size of result sets. |
| This property is <span class="bold"><strong>only</strong></span> 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: |
| </p><div class="itemizedlist"><ul type="circle"><li><p> |
| <code class="literal">query</code>: This is the default. The first time you ask for the |
| size of a query result, OpenJPA will perform a <code class="literal">SELECT COUNT(*) |
| </code> 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. |
| </p></li><li><p> |
| <code class="literal">last</code>: If you have chosen a scrollable result set type, this |
| setting will use the <code class="methodname">ResultSet.last</code> 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 <code class="literal">unknown</code>. The default result set type is |
| <code class="literal">forward-only</code>, so you must change the result set type in |
| order for this property to have an effect. |
| </p></li><li><p> |
| <code class="literal">unknown</code>: Under this setting OpenJPA will return <code class="literal"> |
| Integer.MAX_VALUE</code> as the size for any query result that uses on-demand |
| loading. |
| </p></li></ul></div></li></ul></div><div class="example"><a name="ref_guide_dbsetup_lrs_def"></a><p class="title"><b>Example 4.12. |
| Specifying Result Set Defaults |
| </b></p><div class="example-contents"><pre class="programlisting"> |
| <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"/> |
| </pre></div></div><br class="example-break"><p> |
| Many <a href="ref_guide_runtime.html" title="Chapter 9. Runtime Extensions">OpenJPA runtime components</a> also |
| have methods to configure these properties on a case-by-case basis through their |
| fetch configuration. See <a href="ref_guide_runtime.html" title="Chapter 9. Runtime Extensions">Chapter 9, <i xmlns:xlink="http://www.w3.org/1999/xlink"> |
| Runtime Extensions |
| </i></a>. |
| </p><div class="example"><a name="ref_guide_dbsetup_lrs_runtime"></a><p class="title"><b>Example 4.13. |
| Specifying Result Set Behavior at Runtime |
| </b></p><div class="example-contents"><pre class="programlisting"> |
| 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(ResultSet.TYPE_SCROLL_INSENSITIVE); |
| fetch.setFetchDirection(ResultSet.FETCH_FORWARD); |
| fetch.setLRSSize(JDBCFetchPlan.SIZE_LAST); |
| List results = q.getResultList(); |
| </pre></div></div><br class="example-break"></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ref_guide_dbsetup_retain.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ref_guide_dbsetup.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="ref_guide_schema_def.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">8. |
| Configuring the Use of JDBC Connections |
| </td><td width="20%" align="center"><a accesskey="h" href="manual.html">Home</a></td><td width="40%" align="right" valign="top"> 10. |
| Default Schema |
| </td></tr></table></div></body></html> |