| <html><head> |
| <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> |
| <title>10. Large Result Sets</title><base href="display"><link rel="stylesheet" type="text/css" href="css/docbook.css"><meta name="generator" content="DocBook XSL Stylesheets V1.79.1"><link rel="home" href="manual.html" title="Apache OpenJPA 3.0 User's Guide"><link rel="up" href="ref_guide_dbsetup.html" title="Chapter 4. JDBC"><link rel="prev" href="ref_guide_dbsetup_stmtbatch.html" title="9. Statement Batching"><link rel="next" href="ref_guide_schema_def.html" title="11. 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">10. |
| Large Result Sets |
| </th></tr><tr><td width="20%" align="left"><a accesskey="p" href="ref_guide_dbsetup_stmtbatch.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" id="ref_guide_dbsetup_lrs"><div class="titlepage"><div><div><h2 class="title" style="clear: both">10. |
| Large Result Sets |
| </h2></div></div></div> |
| |
| <a class="indexterm" name="d5e11742"></a> |
| <a class="indexterm" name="d5e11744"></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 class="xref" href="ref_guide_pc_scos.html#ref_guide_pc_scos_proxy_lrs" title="6.4.2. Large Result Set Proxies">Section 6.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 class="itemizedlist" style="list-style-type: disc; "><li class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e11756"></a> |
| <a class="link" href="ref_guide_conf_openjpa.html#openjpa.FetchBatchSize" title="5.33. 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 class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e11764"></a> |
| <a class="link" href="ref_guide_conf_jdbc.html#openjpa.jdbc.ResultSetType" title="6.11. 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 class="itemizedlist" style="list-style-type: circle; "><li class="listitem"> |
| <p> |
| <code class="literal">forward-only</code>: This is the default. |
| </p> |
| </li><li class="listitem"> |
| <p> |
| <code class="literal">scroll-sensitive</code> |
| </p> |
| </li><li class="listitem"> |
| <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 class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e11782"></a> |
| <a class="link" 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 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 |
| <code class="classname">java.sql.ResultSet</code> FETCH constant: |
| </p> |
| <div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"> |
| <p> |
| <code class="literal">forward</code>: This is the default. |
| </p> |
| </li><li class="listitem"> |
| <p> |
| <code class="literal">reverse</code> |
| </p> |
| </li><li class="listitem"> |
| <p> |
| <code class="literal">unknown</code> |
| </p> |
| </li></ul></div> |
| <p> |
| Not all drivers support all fetch directions. |
| </p> |
| </li><li class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e11800"></a> |
| <a class="link" 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 class="itemizedlist" style="list-style-type: circle; "><li class="listitem"> |
| <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 class="listitem"> |
| <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 class="listitem"> |
| <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" id="ref_guide_dbsetup_lrs_def"><p class="title"><b>Example 4.16. |
| 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 class="link" 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 class="xref" href="ref_guide_runtime.html" title="Chapter 9. Runtime Extensions">Chapter 9, <i> |
| Runtime Extensions |
| </i></a>. |
| </p> |
| <div class="example" id="ref_guide_dbsetup_lrs_runtime"><p class="title"><b>Example 4.17. |
| 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(ResultSetType.SCROLL_INSENSITIVE); |
| fetch.setFetchDirection(FetchDirection.FORWARD); |
| fetch.setLRSSizeAlgorithm(LRSSizeAlgorithm.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_stmtbatch.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">9. |
| Statement Batching |
| </td><td width="20%" align="center"><a accesskey="h" href="manual.html">Home</a></td><td width="40%" align="right" valign="top"> 11. |
| Default Schema |
| </td></tr></table></div></body></html> |