View source for EclipseLink/UserGuide/JPA/Basic JPA Development/Querying/Query Hints

'''[[Image:Elug_draft_icon.png|Warning]] See [http://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/queryhints.htm Java Persistence API (JPA) Extensions Reference for EclipseLink, EclipseLink 2.4]
'''

{{EclipseLink_UserGuide
|info=y
|toc=n
|eclipselink=y
|eclipselinktype=JPA
|api=y
|apis=
*[http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/config/QueryHints.html QueryHints]
}}

=EclipseLink JPA Query Hints=

You can use a query hint to customize or optimize a JPA query. All EclipseLink query hints are defined in the <tt>QueryHints</tt> class in the <tt>org.eclipse.persistence.config</tt> package.

{{EclipseLink_Spec
|section=Section 10.3.1 "NamedQuery Annotation"}}

Use EclipseLink JPA query hints to:
* Construct a JPA query (for example, see [[#Example 19-24|Specifying an EclipseLink JPA Query Hint]], below)
* Specify a JPA query using the <tt>@QueryHint</tt> annotation (for example, see [[#Example 19-25|Specifying an EclipseLink JPA Query Hint with @QueryHint]], below)

The EclipseLink query hints include the following:
* <tt>[[#Cache Usage|eclipselink.cache-usage]]</tt>
* <tt>[[#Query Type|eclipselink.query-type]]</tt>
* <tt>[[#Bind Parameters|eclipselink.jdbc.bind-parameters]]</tt>
* <tt>[[#Pessimistic Lock|eclipselink.pessimistic-lock]]</tt>
* <tt>[[#Refresh|eclipselink.refresh]]</tt>
* <tt>[[#Refresh|eclipselink.refresh.cascade]]</tt>
* <tt>[[#Maintain Cache|eclipselink.maintain-cache]]</tt>
* <tt>[[#Batch|eclipselink.batch]]</tt>
* <tt>[[#Join Fetch|eclipselink.join-fetch]]</tt>
* <tt>[[#Read Only|eclipselink.read-only]]</tt>
* <tt>[[#Timeout|eclipselink.jdbc.timeout]]</tt>
* <tt>[[#Fetch Size|eclipselink.jdbc.fetch-size]]</tt>
* <tt>[[#Max Rows|eclipselink.jdbc.max-rows]]</tt>
* <tt>[[#Result Collection Type|eclipselink.result-collection-type]]</tt>

When you set a hint, you can set the value using the public static final field in the appropriate configuration class in <tt>org.eclipse.persistence.config</tt> package, including the following:
* <tt>HintValues</tt>
* <tt>CacheUsage</tt>
* <tt>PessimisticLock</tt>
* <tt>QueryType</tt>

The following examples show how to set the value of hint <tt>eclipselink.jdbc.bind-parameters</tt> using the <tt>QueryHints</tt> configuration class to set the name of the hint, and the <tt>HintValues</tt> configuration class to set the value.

<span id="Example 19-24"></span>
''''' Specifying an EclipseLink JPA Query Hint'''''
<source lang="java">
 import org.eclipse.persistence.config.HintValues;
 import org.eclipse.persistence.config.QueryHints;
 
 Customer customer = (Customer)entityMgr.createNamedQuery("findCustomerBySSN").
     setParameter("SSN", "123-12-1234").
     setHint(QueryHints.BIND_PARAMETERS, HintValues.PERSISTENCE_UNIT_DEFAULT).
     getSingleResult();
</source>

<span id="Example 19-25"></span>
''''' Specifying an EclipseLink JPA Query Hint with @QueryHint'''''
<source lang="java">
 import org.eclipse.persistence.config.HintValues;
 import org.eclipse.persistence.config.QueryHints;
 
 @Entity
 @NamedQuery(
     name="findEmployeeByDept",
     query="SELECT e FROM Employee e WHERE e.dept=:deptNum",
     hints=@QueryHint(name=QueryHints.BIND_PARAMETERS, value=HintValues.TRUE)
 )
 public class Employee implements Serializable {
     ...
 }
</source>

==Cache Usage==
The <tt>eclipselink.cache-usage</tt> hint specifies how the query should interact with the EclipseLink cache.

EclipseLink JPA uses a shared cache mechanism that is scoped to the entire persistence unit. When operations are completed in a particular persistence context, the results are merged back into the shared cache so that other persistence contexts can use them. This happens regardless of whether the entity manager and persistence context are created in Java SE or Java EE. Any entity persisted or removed using the entity manager will always be kept consistent with the cache.

{{EclipseLink_ValidValuesTable
|caption=Valid values for <tt>org.eclipse.persistence.config.CacheUsage</tt>
|content=<tr>
 <td>'''<tt>DoNotCheckCache</tt>'''</td>
 <td>Always go to the database. </td>
</tr>
<tr>
 <td>'''<tt>CheckCacheByExactPrimaryKey</tt>'''</td>
 <td>If a read-object query contains an expression where the primary key is the only comparison, you can obtain a cache hit if you process the expression against the object in memory</td>
</tr>
<tr>
 <td>'''<tt>CheckCacheByPrimaryKey</tt>'''</td>
 <td>If a read-object query contains an expression that compares at least the primary key, you can obtain a cache hit if you process the expression against the objects in memory.</td>
</tr>
<tr>
 <td>'''<tt>CheckCacheThenDatabase</tt>'''</td>
 <td>You can configure any read-object query to check the cache completely before you resort to accessing the database.</td>
</tr>
<tr>
 <td>'''<tt>CheckCacheOnly</tt>'''</td>
 <td>You can configure any read-all query to check only the parent session cache (shared cache) and return the result from it without accessing the database.</td>
</tr>
<tr>
 <td>'''<tt>ConformResultsInUnitOfWork</tt>'''</td>
 <td>You can configure any read-object or read-all query within the context of a unit of work to conform the results with the changes to the object made within that unit of work. This includes new objects, deleted objects and changed objects.</td>
</tr>
<tr>
 <td>'''<tt>UseEntityDefault</tt>'''</td>
 <td>Use the cache configuration as specified by the EclipseLink descriptor API for this entity. <br />
'''Default'''
{{EclipseLink_Note
|note=The entity default value is to not check the cache (<tt>DoNotCheckCache</tt>). The query will access the database and synchronize with the cache. Unless refresh has been set on the query, the cached objects will be returned without being refreshed from the database. EclipseLink does not support the cache usage for native queries or queries that have complex result sets such as returning data or multiple objects.}}</td>
</tr>}}


'''Example'''<nowiki>: JPA Query API</nowiki> 
<source lang="java">
 import org.eclipse.persistence.config.CacheUsage;
 import org.eclipse.persistence.config.QueryHints;
 query.setHint(QueryHints.CACHE_USAGE, CacheUsage.CheckCacheOnly);
</source>

'''Example'''<nowiki>: </nowiki><tt>@QueryHint</tt> 
<source lang="java">
 import org.eclipse.persistence.config.CacheUsage;
 import org.eclipse.persistence.config.TargetDatabase;
 @QueryHint(name=QueryHints.CACHE_USAGE, value=CacheUsage.CheckCacheOnly);
</source>

==Query Type==
The <tt>eclipselink.query-type</tt> hint specifies the EclipseLink query type to use for the query. 

For most JP QL queries, the <tt>org.eclipse.persistence.queries.ReportQuery</tt> or <tt>org.eclipse.persistence.queries.ReadAllQuery</tt> are used. The <tt>eclipselink.query-type</tt> hint lets you use other query types, such as <tt>org.eclipse.persistence.queries.ReadObjectQuery</tt> for queries that are know to return a single object.

{{EclipseLink_ValidValuesTable
|caption=Valid values for the <tt>org.eclipse.persistence.config.QueryType</tt>
|content=<tr>
 <td>'''<tt>Auto</tt>'''</td>
 <td>EclipseLink chooses the type of query to use. <br />'''Default'''</td>
</tr>
<tr>
 <td>'''<tt>ReadAll</tt>'''</td>
 <td>Use the <tt>ReadAllQuery</tt> type for the query.</td>
</tr>
<tr>
 <td>'''<tt>ReadObject</tt>'''</td>
 <td>Use the <tt>ReadObjectQuery</tt> type for the query.</td>
</tr>
<tr>
 <td>'''<tt>Report</tt>'''</td>
 <td>Use the <tt>Report Query</tt> type for the query.</td>
</tr>}}


'''Example'''<nowiki>: JPA Query API</nowiki> 
<source lang="java">
 import org.eclipse.persistence.config.QueryType;
 import org.eclipse.persistence.config.QueryHints;
 query.setHint(QueryHints.QUERY_TYPE, QueryType.ReadObject);
</source>

'''Example'''<nowiki>: </nowiki><tt>@QueryHint</tt> 
<source lang="java">
 import org.eclipse.persistence.config.QueryType;
 import org.eclipse.persistence.config.TargetDatabase;
 @QueryHint(name=QueryHints.QUERY_TYPE, value=QueryType.ReadObject);
</source>

==Bind Parameters==
The <tt>eclipselink.jdbc.bind-parameters</tt> hint controls whether or not the query uses parameter binding.

{{EclipseLink_ValidValuesTable
|caption=Valid values for the <tt>org.eclipse.persistence.config.HintValues</tt>
|content=<tr>
 <td>'''<tt>TRUE</tt>'''</td>
 <td>Bind all parameters.</td>
</tr>
<tr>
 <td>'''<tt>FALSE</tt>'''</td>
 <td>Do not bind all parameters.</td>
</tr>
<tr>
 <td>'''<tt>PERSISTENCE_UNIT_DEFAULT</tt>'''</td>
 <td>Use the parameter binding setting made in your EclipseLink session's database login, which is <tt>true</tt> by default.
'''Default'''</td>
</tr>}}

'''Example'''<nowiki>: JPA Query API</nowiki> 
<source lang="java">
 import org.eclipse.persistence.config.HintValues;
 import org.eclipse.persistence.config.QueryHints;
 query.setHint(QueryHints.BIND_PARAMETERS, HintValues.TRUE);
</source>

'''Example'''<nowiki>: </nowiki><tt>@QueryHint</tt> 
<source lang="java">
 import org.eclipse.persistence.config.HintValues;
 import org.eclipse.persistence.config.TargetDatabase;
 @QueryHint(name=QueryHints.BIND_PARAMETERS, value=HintValues.TRUE);
</source>

==Fetch Size==
The <tt>eclipselink.jdbc.fetch-size</tt> hint specifies the number of rows that should be fetched from the database when more rows are needed<sup>1</sup>

For large queries that return a large number of objects you can configure the row fetch size used in the query to improve performance by reducing the number database hits required to satisfy the selection criteria. Most JDBC drivers default to a fetch size of 10, so if you are reading 1000 objects, increasing the fetch size to 256 can significantly reduce the time required to fetch the query's results. The optimal fetch size is not always obvious. Usually, a fetch size of one half or one quarter of the total expected result size is optimal. Note that if you are unsure of the result set size, incorrectly setting a fetch size too large or too small can decrease performance.
<br><span style="font-size:0.8em;font-style:italic"><sup>1</sup> This property is dependent on the JDBC driver support.</span>

{{EclipseLink_ValidValuesTable
|caption=
|content=<tr>
<td>0 to <tt>Integer.MAX_VALUE</tt></td>
<td>As a <tt>String</tt>, depending on your JDBC driver. A value of 0 means the JDBC driver default will be used.<br />Default = '''0'''.
}}

==Timeout==
The <tt>eclipselink.jdbc.timeout</tt> hint specifies the number of seconds EclipseLink will wait on a query before throwing a <tt>DatabaseException</tt><sup>1</sup>
<span style="font-size:0.8em;font-style:italic"><br><sup>1</sup> This property is dependent on the JDBC driver support.</span>
{{EclipseLink_ValidValuesTable
|caption=
|content=<tr>
<td>0 to <tt>Integer.MAX_VALUE</tt></td>
<td>As a <tt>String</tt>, depending on your JDBC driver. A value of 0 means EclipseLink will never time-out a query. <br />Default = '''0'''.
}}

==Pessimistic Lock==
The <tt>eclipselink.pessimistic-lock</tt> hint controls whether or not pessimistic locking is used.

{{EclipseLink_ValidValuesTable
|caption=Valid values for the <tt>org.eclipse.persistence.config.PessimisticLock</tt>
|content=<tr>
 <td>'''<tt>NoLock</tt></td>
 <td>Pessimistic locking is not used.<br>
'''Default'''</td>
</tr>
<tr>
 <td>'''<tt>Lock</tt>'''</td>
 <td>EclipseLink issues a <tt>SELECT .... FOR UPDATE</tt>.</td>
</tr>
<tr>
 <td>'''<tt>LockNoWait</tt>'''</td>
 <td>EclipseLink issues a <tt>SELECT .... FOR UPDATE NO WAIT</tt>.</td>
</tr>
}}

'''Example'''<nowiki>: JPA Query API</nowiki> 
<source lang="java">
 import org.eclipse.persistence.config.PessimisticLock;
 import org.eclipse.persistence.config.QueryHints;
 query.setHint(QueryHints.PESSIMISTIC_LOCK, PessimisticLock.LockNoWait);
</source>

'''Example'''<nowiki>: </nowiki><tt>@QueryHint</tt> 
<source lang="java">
 import org.eclipse.persistence.config.PessimisticLock;
 import org.eclipse.persistence.config.QueryHints;
 @QueryHint(name=QueryHints.PESSIMISTIC_LOCK, value=PessimisticLock.LockNoWait);
</source>

==Batch==
The <tt>eclipselink.batch</tt> hint supplies EclipseLink with batching information so subsequent queries of related objects can be optimized in batches instead of being retrieved one-by-one or in one large joined read. Batch reading is more efficient than joining because it avoids reading duplicate data.

Batching is only allowed on queries that have a single object in their select clause.

Valid values: a single-valued relationship path expression.

{{EclipseLink_Note
|note=Use dot notation to access nested attributes. For example, to batch-read an employee's manager's address, specify <tt>e.manager.address</tt>}}


'''Example'''<nowiki>: JPA Query API</nowiki> 
<source lang="java">
 import org.eclipse.persistence.config.HintValues;
 import org.eclipse.persistence.config.QueryHints;
 query.setHint("eclipselink.batch", "e.address");
</source>

'''Example'''<nowiki>: </nowiki><tt>@QueryHint</tt> 
<source lang="java">
 import org.eclipse.persistence.config.HintValues;
 import org.eclipse.persistence.config.QueryHints;
 @QueryHint(name=QueryHints.BATCH, value="e.address");
</source>

==Join Fetch==
The <tt>eclipselink.join-fetch</tt> hint allows joining of the attributes. 
*This is similar to [[#Batch|<tt>eclipselink.batch</tt><nowiki>]], subsequent queries of related objects can be optimized in batches instead of being retrieved in one large joined read.</nowiki>
*This is different from JP QL joining because it allows multilevel fetch joins.
{{EclipseLink_Spec
|section=Section 4.4.5.3 "Fetch Joins"}}

Valid values: a relationship path expression.

{{EclipseLink_Note
|note=Use dot notation to access nested attributes.}}


'''Example'''<nowiki>: JPA Query API</nowiki> 
<source lang="java">
 import org.eclipse.persistence.config.HintValues;
 import org.eclipse.persistence.config.QueryHints;
 query.setHint("eclipselink.join-fetch", "e.address");
</source>

'''Example'''<nowiki>: </nowiki><tt>@QueryHint</tt> 
<source lang="java">
 import org.eclipse.persistence.config.HintValues;
 import org.eclipse.persistence.config.QueryHints;
 @QueryHint(name=QueryHints.FETCH, value="e.address");
</source>

==Refresh==
The <tt>eclipselink.refresh</tt> hint controls whether or not to update the EclipseLink session cache with objects that the query returns.

{{EclipseLink_ValidValuesTable
|caption=Valid values for the <tt>org.eclipse.persistence.config.HintValues</tt>
|content=
<tr>
 <td>'''<tt>TRUE</tt>'''</td>
 <td>Refresh cache.</td>
</tr>
<tr>
 <td>'''<tt>FALSE</tt>'''</td>
 <td>Do not refresh cache. <br>'''Default'''</td>
</tr>
}}


'''Example'''<nowiki>: JPA Query API</nowiki> 
<source lang="java">
 import org.eclipse.persistence.config.HintValues;
 import org.eclipse.persistence.config.QueryHints;
 query.setHint(QueryHints.REFRESH, HintValues.TRUE);
</source>

'''Example'''<nowiki>: </nowiki><tt>@QueryHint</tt> 
<source lang="java">
 import org.eclipse.persistence.config.HintValues;
 import org.eclipse.persistence.config.QueryHints;
 @QueryHint(name=QueryHints.REFRESH, value=HintValues.TRUE);
</source>

==Maintain Cache==
The <tt>eclipselink.maintain-cache</tt> hint controls whether or not query results are cached in the session cache and provides a way to query the current database contents without affecting the current persistence context.  It configures the query to return unmanaged instances so any updates to entities queried using this hint would have to be merged into the persistence context. 
{{EclipseLink_ValidValuesTable
|caption=Valid values for the <tt>org.eclipse.persistence.config.HintValues</tt>
|content=<tr>
 <td>'''<tt>TRUE</tt>'''</td>
 <td>Maintain cache.</td>
</tr>
<tr>
 <td>'''<tt>FALSE</tt>'''</td>
 <td>Do not maintain cache.<br>'''Default'''</td>
</tr>
}}

'''Example'''<nowiki>: JPA Query API</nowiki> 
<source lang="java">
 import org.eclipse.persistence.config.HintValues;
 import org.eclipse.persistence.config.QueryHints;
 query.setHint(QueryHints.MAINTAIN_CACHE, HintValues.FALSE);
</source>

'''Example'''<nowiki>: </nowiki><tt>@QueryHint</tt> 
<source lang="java">
 import org.eclipse.persistence.config.HintValues;
 import org.eclipse.persistence.config.QueryHints;
 @QueryHint(name=QueryHints.MAINTAIN_CACHE, value=HintValues.FALSE);
</source>

==Read Only==
The <tt>eclipselink.read-only</tt> hint retrieves read-only results back from the query: on nontransactional read operations, where the requested entity types are stored in the shared cache, you can request that the shared instance be returned instead of a detached copy.
{{EclipseLink_Note
|note=You should never modify objects returned from the shared cache.}}
{{EclipseLink_ValidValuesTable
|caption=Valid values for the <tt>org.eclipse.persistence.config.HintValues</tt>
|content=<tr>
 <td>'''<tt>TRUE</tt>'''</td>
 <td>Retrieve read-only results back from the query</td>
</tr>
<tr>
 <td>'''<tt>FALSE</tt></td>
 <td>Do not retrieve read-only results back from the query.<br />'''Default'''</td>
</tr>
}}

'''Example'''<nowiki>: JPA Query API</nowiki> 
<source lang="java">
 import org.eclipse.persistence.config.HintValues;
 import org.eclipse.persistence.config.QueryHints;
 query.setHint(QueryHints.READ_ONLY, HintValues.TRUE);
</source>
 
'''Example'''<nowiki>: </nowiki><tt>@QueryHint</tt> 
<source lang="java">
 import org.eclipse.persistence.config.HintValues;
 import org.eclipse.persistence.config.QueryHints;
 @QueryHint(name=QueryHints.READ_ONLY, value=HintValues.TRUE);
</source>

==Result Collection Type==
The <tt>eclipselink.result-collection-type</tt> hint configures the concrete class that EclipseLink should use to return its query result.

This lets you specify the type of collection in which the result will be returned.

Valid values: Java <tt>Class</tt> that implements the <tt>Collection</tt> interface.

{{EclipseLink_Note
|note=Typically, you would execute these queries by calling the <tt>getResultsList</tt> method, which returns the java.util.List, on the <tt>Query</tt>. This means that the class specified in this hint must implement the <tt>List</tt> interface, if you are invoking it using the <tt>getResultsList</tt> method.}}

{{EclipseLink_Note
|note=Specify the class without the <tt>".class"</tt> notation. For example, <tt>java.util.Vector</tt> would work, not <tt>java.util.Vector.class</tt>EclipseLink will throw an exception, if you use this hint with a class that does not implement the <tt>Collection</tt> interface.}}

Default:<tt>java.util.Vector</tt>

'''Example'''<nowiki>: JPA Query API</nowiki> 
<source lang="java">
 import org.eclipse.persistence.config.HintValues;
 import org.eclipse.persistence.config.QueryHints;
 query.setHint("eclipselink.result-collection-type", java.util.ArrayList.class);
</source>

'''Example'''<nowiki>: </nowiki><tt>@QueryHint</tt> 
<source lang="java">
 import org.eclipse.persistence.config.HintValues;
 import org.eclipse.persistence.config.QueryHints;
 @QueryHint(name=QueryHints.RESULT_COLLECTION_TYPE, value="java.util.ArrayList");
</source>


{{EclipseLink_JPA
|previous= [[EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Native|Native SQL Queries]]
|next=     [[EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Query_Casting|Query Casting]]
|up=       [[EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying|Querying]]
|version=2.2.0 DRAFT}}