4 JPA Query Customization Extensions

This chapter describes how to specify EcpliseLink query hints (JPA query extensions). You can specify EclipseLink query hints (JPA query extensions) by:

Using the @QueryHint annotation

Including the hints in the orm.xml or eclipselink-orm.xml file

Using the setHint() method when executing a named or dynamic query (JPQL or Criteria)

EclipseLink supplies the following query hints:

batch

batch.size

batch.type

cache-usage

cache-usage.indirection-policy

cursor

composite-unit.member

cursor.initial-size

cursor.page-size

exclusive-connection

flush

history.as-of

history.as-of.scn

inheritance.outer-join

jdbc.bind-parameters

jdbc.cache-statement

jdbc.fetch-size

jdbc.first-result

jdbc.max-rows

jdbc.native-connection

jdbc.parameter-delimiter

load-group.attribute

maintain-cache

pessimistic-lock

prepare

query-results-cache

query-results-cache.expiry

query-results-cache.expiry-time-of-day

query-results-cache.ignore-null

query-results-cache.randomize-expiry

query-results-cache.size

query-results-cache.type

query-type

read-only

refresh

refresh.cascade

result-collection-type

sql.hint

All EclipseLink query hints are defined in the QueryHints class in the org.eclipse.persistence.config package . When you set a hint, you can set the value using the public static final field in the appropriate configuration class in org.eclipse.persistence.config package, including the following:

HintValues

CacheUsage

PessimisticLock

QueryType

For more information, see Section 10.3.1 "NamedQuery Annotation" in the JPA Specification ( http://jcp.org/en/jsr/detail?id=317 ).

batch

Use eclipselink.batch to supply 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.

Values

This query hint accepts a single-valued, relationship path expression.

Usage

Using the eclipselink.batch hint is more efficient than joining, because EclipseLink avoids reading duplicate data.

You can only batch queries that have a single object in the select clause.

Valid values: a single-valued relationship path expression.

Example 4-1 shows how to use this hint in a JPA query.

Example 4-1 Using batch in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.batch", "e.address");

Example 4-2 shows how to use this hint with the @QueryHint annotation.

Example 4-2 Using batch in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.BATCH, value="e.address");

See Also

For more information, see:

"EclipseLink" JPA Query Hints http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Query_Hints

"join-fetch"

"batch.size"

"batch.type"

"Querying" in Solutions Guide for EclispeLink

batch.size

Use eclipselink.batch.size to configure the batch size when using batch.type set to IN .

Values

Table 4-1 describes this persistence property's values.

Table 4-1 Valid Values for batch.size

Example 4-3 shows how to use this hint in a JPA query.

Example 4-3 Using batch.size in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.BATCH_SIZE", "3");

Example 4-4 shows how to use this hint with the @QueryHint annotation.

Example 4-4 Using batch.size in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.BATCH_SIZE, value="3");

See Also

For more information, see:

"batch"

batch.type

Use eclipselink.batch.type to specify the type of batch fetching the query should use for any batch-fetched relationships.

Values

Table 4-2 describes this query hint's values.

Table 4-2 Valid Values for batch.type

Example 4-5 shows how to use this hint in a JPA query.

Example 4-5 Using batch.type in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.BATCH_TYPE", "EXISTS");

Example 4-6 shows how to use this hint with the @QueryHint annotation.

Example 4-6 Using batch.type in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.BATCH_TYPE, value="EXISTS");

See Also

For more information, see:

"batch"

"@BatchFetch"

cache-usage

Use eclipselink.cache-usage to specify how the query should interact with the EclipseLink cache.

Values

Table 4-3 describes this query hint's valid values.

Table 4-3 Valid Values for org.eclipse.persistence.config.CacheUsage

(Default) Use the cache configuration as specified by the EclipseLink descriptor API for this entity.

Note : The entity default value is to not check the cache ( DoNotCheckCache ). 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.

Usage

EclipseLink JPA uses a shared cache assessed across the entire persistence unit. After completing an operation in a particular persistence context, EclipseLink merges the results into the shared cache, so that other persistence contexts can use the results 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 consistently maintained with the cache.

Examples

Example 4-7 shows how to use this hint in a JPA query.

Example 4-7 Using cache-usage in a JPA Query

import org.eclipse.persistence.config.CacheUsage; import org.eclipse.persistence.config.QueryHints; query.setHint(QueryHints.CACHE_USAGE, CacheUsage.CheckCacheOnly);

Example 4-8 shows how to use this hint with the @QueryHint annotation.

Example 4-8 Using cache-usage in a @QueryHint Annotation

import org.eclipse.persistence.config.CacheUsage; import org.eclipse.persistence.config.TargetDatabase; @QueryHint(name=QueryHints.CACHE_USAGE, value=CacheUsage.CheckCacheOnly);

cache-usage.indirection-policy

Use eclipselink.cache-usage.indirection-policy (with cache-usage ) to configure in-memory querying and conforming's treatment of uninstantiated indirection/lazy relationships.

Values

Table 4-4 describes this query hint's values.

Table 4-4 Valid Values for cache-usage.indirection-policy

This hint applies only when the query traverses a join across a lazy relationship.

Examples

Example 4-9 shows how to use this hint in a JPA query.

Example 4-9 Using cache-usage.indirection-policy in a JPA Query

query.setHint(QueryHints.INDIRECTION_POLICY, CacheUsageIndirectionPolicy.Trigger);

Example 4-10 shows how to use this hint with the @QueryHint annotation.

Example 4-10 Using cache-usage.indirection-policy in a @QueryHint Annotation

@QueryHint(name=QueryHints.INDIRECTION_POLICY, value=CacheUsageIndirectionPolicy.Trigger)

See Also

For more information, see:

"EclipseLink" JPA Query Hints http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Query_Hints

"EclipseLink Caches" in Understanding EclipseLink

"Querying" in Solutions Guide for EclispeLink

"cache-usage"

Usage

A Cursor is a stream of the JDBC ResultSet . Cursors are useful for large results sets, or when you only need the few results of a query.

A cursor implements Enumeration , when the each next() will fetch the next from the JDBC ResultSet , and builds the resulting Object or value. A Cursor requires, and will keep, a live JDBC connection. You must use close() to free the Cursor's resources.

You can access a Cursor from a JPA Query through getSingleResult() , or from JpaQuery using getResultCursor() .

Example 4-11 shows how to use this hint in a JPA query.

Example 4-11 Using cursor in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.cursor", "TRUE");

Example 4-12 shows how to use this hint with the @QueryHint annotation.

Example 4-12 Using cursor in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.CURSOR, value="TRUE");

See Also

For more information, see:

"cursor.initial-size"

"cursor.page-size"

composite-unit.member

The ecliplselink.composite-unit.member query hint specifies the name of the composite member persistence unit on which you want to execute the query. You must use it on a native query executed on a composite persistence unit.

Values

Table 4-6 describes this persistence property's values.

Table 4-6 Valid Values for composite-unit.member

Example 4-13 shows how to use this hint in a JPA query.

Example 4-13 Using composite-unit.member in a JPA query

import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.composite-unit.member", "mypersistentunit");

Example 4-14 shows how to use this hint with the @QueryHint annotation.

Example 4-14 Using composite-unit.member in an @QueryHint annotation

import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.COMPOSITE_UNIT_MEMBER, value="mypersistentunit");

cursor.initial-size

Use eclipselink.cursor.initial-size to configure the query to return a CursoredStream with the specified initial size.

Values

Table 4-7 describes this query hint's values.

Table 4-7 Valid Values for cursor.initial-size

Example 4-15 shows how to use this hint in a JPA query.

Example 4-15 Using cursor.initial-size in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.cursor_initial_size", "10");

Example 4-16 shows how to use this hint with the @QueryHint annotation.

Example 4-16 Using cursor.initial-size in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.CURSOR_INITIAL_SIZE, value="10");

See Also

For more information, see:

"cursor"

cursor.page-size

Use eclipselink.cursor.page-size to configure the query to return a CursoredStream with the specified page size.

Values

Table 4-8 describes this query hint's values.

Table 4-8 Valid Values for cursor.page-size

Example 4-17 shows how to use this hint in a JPA query.

Example 4-17 Using cursor.page-size in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.CURSOR_PAGE_SIZE", "10");

Example 4-18 shows how to use this hint with the @QueryHint annotation.

Example 4-18 Using cursor.page-size in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.CURSOR_PAGE_SIZE, value="10");

See Also

For more information, see:

"cursor"

exclusive-connection

Use eclipselink.exclusive-connection to specify if the query should use the exclusive (transactional/write) connection.

Values

Table 4-9 describes this query hint's values.

Table 4-9 Valid Values for exclusive-connection

Usage

This is valid only when an EXCLUSIVE_CONNECTION_MODE property has been set for the persistence unit (such as VPD). If a jdbc.exclusive-connection.mode has been configured, use this query hint to ensure that the query is executed through the exclusive connection.

This may be required in certain cases, such as when database security prevents a query joining to a secure table from returning the correct results, when executed through the shared connection.

Examples

Example 4-19 shows how to use this hint in a JPA query.

Example 4-19 Using exclusive-connection in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.EXCLUSIVE_CONNECTION", "TRUE");

Example 4-20 shows how to use this hint with the @QueryHint annotation.

Example 4-20 Using exclusive-connection in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.EXCLUSIVE_CONNECTION, value="TRUE");

See Also

For more information, see:

"jdbc.exclusive-connection.mode"

flush

Use eclipselink.flush to specify if the query should flush the persistence context before executing.

Values

Table 4-10 describes this query hint's values.

Table 4-10 Valid Values for flush

Usage

If the query may access objects that have been changed in the persistence context, you must trigger a flush in order for the query to see the changes. If the query does not require seeing the changes, you should avoid the flush in order to improve performance.

You can also configure the flush-mode as a persistence unit property. See "flush-clear.cache" for more information.

You can also use conforming to query changes without requiring a flush. See "cache-usage" for more information.

Examples

Example 4-21 shows how to use this hint in a JPA query.

Example 4-21 Using flush in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.FLUSH", "TRUE");

Example 4-22 shows how to use this hint with the @QueryHint annotation.

Example 4-22 Using flush in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.FLUSH, value="TRUE");

See Also

For more information, see:

"persistence-context.flush-mode"

"flush-clear.cache"

"EclipseLink" JPA Query Hints http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Query_Hints

"EclipseLink Caches" in Understanding EclipseLink

"Querying" in Solutions Guide for EclispeLink

"cache-usage.indirection-policy"

"cache-usage"

history.as-of

Configures the query to query the state of the object as-of a point in time.

Values

Table 4-11 describes this query hint's values.

Table 4-11 Valid Values for history.as-of

Example 4-23 shows how to use this hint in a JPA query.

Example 4-23 Using history.as-of in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.AS_OF", "2012/10/15 11:21:18.2");

Example 4-24 shows how to use this hint with the @QueryHint annotation.

Example 4-24 Using history.as-of in @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.AS_OF, value="2012/10/15 11:21:18.2");

See Also

For more information, see:

"history.as-of.scn"

"Using Oracle Flashback Technology" in Oracle Database Advanced Application Developer's Guide

history.as-of.scn

Use eclipselink.history.as-of.scn to configure the query to query the state of the object as-of a database SCN (System Change Number).

Values

Table 4-12 describes this query hint's values.

Table 4-12 Valid Values for history.as-of.scn

Example 4-25 shows how to use this hint in a JPA query.

Example 4-25 Using history.as-of.scn in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.AS_OF_SCN", "3");

Example 4-26 shows how to use this hint with the @QueryHint annotation.

Example 4-26 Using history.as-of.scn in @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.AS_OF_SCN, value="3");

See Also

For more information, see:

"history.as-of"

"Using Oracle Flashback Technology" in Oracle Database Advanced Application Developer's Guide

inheritance.outer-join

Use eclipselink.inheritance.outer-join to configure the query to use an outer-join for all subclasses.

Values

Table 4-13 describes this query hint's values.

Table 4-13 Valid Values for inheritance.outer-join

Example 4-27 shows how to use this hint in a JPA query.

Example 4-27 Using inheritance.outer-join in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.INHERITANCE_OUTER_JOIN", "TRUE");

Example 4-28 shows how to use this hint with the @QueryHint annotation.

Example 4-28 Using inheritance.outer-join in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.INHERITANCE_OUTER_JOIN, value="TRUE");

See Also

For more information, see:

"Inheritance" in Understanding EclipseLink

"Enhancing Performance" in Solutions Guide for EclispeLink

jdbc.bind-parameters

Use eclipselink.jdbc.bind-parameters to specify if the query uses parameter binding (parameterized SQL).

Values

Table 4-14 describes this query hint's valid values.

Table 4-14 Valid Values for org.eclipse.persistence.config.HintValues

Usage

By default, EclipseLink enables parameter binding and statement caching. This causes EclipseLink to use a prepared statement, binding all SQL parameters and caching the prepared statement. When you re-execute this query, you avoid the SQL preparation, which improves performance.

You can also configure parameter binding for the persistence unit in the persistence.xml file (when used in a Java SE environment).

Examples

Example 4-29 shows how to use this hint in a JPA query.

Example 4-29 Using bind-parameters in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint(QueryHints.BIND_PARAMETERS, HintValues.TRUE);

Example 4-30 shows how to use this hint with the @QueryHint annotation.

Example 4-30 Using bind-parameters in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.TargetDatabase; @QueryHint(name=QueryHints.BIND_PARAMETERS, value=HintValues.TRUE);

Example 4-31 shows how to configure parameter binding in the persistence unit persistence.xml file.

Example 4-31 Specifying Parameter Binding Persistence Unit Property

Or by importing a property map:

import org.eclipse.persistence.config.PersistenceUnitProperties; propertiesMap.put(PersistenceUnitProperties.NATIVE_SQL, "true");

See Also

For more information, see:

"jdbc.cache-statements"

"jdbc.batch-writing.size"

"Parameterized SQL and Statement Caching" in Solutions Guide for EclispeLink

jdbc.allow-partial-bind-parameters

Use eclipselink.jdbc.allow-partial-bind-parameters to specify if parameter binding decisions apply to individual expressions or the whole query.

Values

Table 4-83 describes this persistence property's values.

Table 4-83 Valid Values for jdbc.allow-partial-bind-parameters

Usage

EclipseLink determines binding behavior based on the database's support for binding. If the database does not support binding, for a specific expression, EclipseLink will disable parameter binding for the whole query. Setting this property to 'true' will allow EclipseLink to bind per expression, instead of per query.

Examples

Example 4-83 shows how to configure parameter binding in the persistence unit persistence.xml file.

Example 4-83 Specifying Allow Partial Parameter Binding Persistence Unit Property

Or by importing a property map:

import org.eclipse.persistence.config.PersistenceUnitProperties; propertiesMap.put(PersistenceUnitProperties.JDBC_ALLOW_PARTIAL_PARAMETERS, "true");

See Also

For more information, see:

"jdbc.bind-parameters"

"jdbc.batch-writing.size"

"Parameterized SQL and Statement Caching" in Solutions Guide for EclispeLink

Normally, you should set statement caching for the entire persistence unit (see "jdbc.cache-statements" ) instead of each query.

When using a DataSource , you must set statement caching in the DataSource configuration.

Example 4-32 shows how to use this hint in a JPA query.

Example 4-32 Using jdbc.cache-statement in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.CACHE_STATEMENT", "TRUE");

Example 4-33 shows how to use this hint in the @QueryHint annotation.

Example 4-33 Using jdbc.cache-statement in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.CACHE_STATEMENT, value="TRUE");

See Also

For more information, see:

"jdbc.cache-statements"

"Enhancing Performance" in Solutions Guide for EclispeLink

Usage

For 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.

By default, most JDBC drivers use 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.

If you are unsure of the result set size, incorrectly setting a fetch size too large or too small can decrease performance.

Examples

Example 4-34 shows how to use this hint in a JPA query.

Example 4-34 Using jdbc.fetch-size in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.JDBC_FETCH_SIZE", "100");

Example 4-35 shows how to use this hint with the @QueryHint annotation.

Example 4-35 Using jdbc.fetch-size in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.JDBC_FETCH_SIZE, value="100");

See Also

For more information, see:

"EclipseLink" JPA Query Hints http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Query_Hints

"Querying" and "Enhancing Performance" in Solutions Guide for EclispeLink

"EclipseLink Caches" in Understanding EclipseLink

jdbc.first-result

Use eclipselink.jdbc.first-result to specify if the query should skip the specified number of rows in the result.

Values

Table 4-17 describes this query hint's values.

Table 4-17 Valid Values for jdbc.first-result

Usage

This query hint is similar to JPA Query setFirstResults() , but can be set in metadata for NamedQuerys .

Examples

Example 4-36 shows how to use this hint in a JPA query.

Example 4-36 Using jdbc.first-result in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.JDBC_FIRST_RESULT", "10");

See Also

For more information, see:

"Query Concepts" in Understanding EclipseLink

jdbc.max-rows

Use eclipselink.jdbc.max-rows to specify the maximum number of rows to be returned. If the query returns more rows than specified, the trailing rows will not be returned.

Values

Table 4-18 describes this query hint's valid values.

Table 4-18 Valid Values for eclipselink.jdbc.max-rows

Usage

This hint is similar to JPQL setMaxResults() , but can be specified within the metadata for NamedQueries .

Examples

Example 4-37 shows how to use this hint in a JPA query.

Example 4-37 Using jdbc.max-rows in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.JDBC_MAX_ROWS", "100");

Example 4-38 shows how to use this hint with the @QueryHint annotation.

Example 4-38 Using jdbc.max-rows in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.JDBC_MAX_ROWS, value="100");

See Also

For more information, see:

EclipseLink Pagination Example http://wiki.eclipse.org/EclipseLink/Examples/JPA/Pagination

"Query Concepts" in Understanding EclipseLink

jdbc.native-connection

Use eclipselink.jdbc.native-connection to specify if the query requires a native JDBC connection.

Values

Table 4-19 describes this persistence property's values.

Table 4-19 Valid Values for jdbc.native-connection

Usage

This may be required for some queries on some server platforms that have DataSource implementations that wrap the JDBC connection in their own proxy. If the query requires custom JDBC access, it may require a native connection.

A ServerPlatform is required to be set as a persistence property to be able to use a native connection. For features that EclipseLink already knows require a native connection, eclipselink.jdbc.native-connection will default to true .

Examples

Example 4-39 shows how to use the hint in a JPA Query.

Example 4-39 Using jdbc.native-connection in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.NATIVE_CONNECTION", "TRUE");

See Also

For more information, see:

"target-server"

jdbc.parameter-delimiter

Use eclipselink.jdbc.parameter-delimiter to specify a custom parameter binding character (instead of the default hash # character).

Values

Table 4-20 describes this query hint's values.

Table 4-20 Valid Values for jdbc.parameter-delimiter

Example 4-40 shows how to use this hint in a JPA query.

Example 4-40 Using jdbc.parameter-delimiter in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.PARAMETER_DELIMITER", ",");

Example 4-41 shows how to use this hint with the @QueryHint annotation.

Example 4-41 Using jdbc.parameter-delimiter in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.PARAMETER_DELIMITER, value=",");

See Also

For more information, see:

"jdbc.bind-parameters"

Usage

Some database platforms may not support lock timeouts, so you may consider setting a JDBC_TIMEOUT hint for these platforms.

Examples

Example 4-42 shows how to use this hint in a JPA query.

Example 4-42 Using jdbc.timeout in a JPA Query

import org.eclipse.persistence.config.CacheUsage; import org.eclipse.persistence.config.QueryHints; query.setHint(QueryHints. JDBC_TIMEOUT , "100");

Example 4-43 shows how to use this hint with the @QueryHint annotation.

Example 4-43 Using jdbc.timeout in a @QueryHint Annotation

import org.eclipse.persistence.config.CacheUsage; import org.eclipse.persistence.config.TargetDatabase; @QueryHint(name=QueryHints. JDBC_TIMEOUT , value="100");

See Also

For more information, see:

"query-type"

"About JPA Query Hints" in Understanding EclipseLink

"Enhancing Performance" in Solutions Guide for EclispeLink

Usage

This hint is similar to eclipselink. batch . Subsequent queries of related objects can be optimized in batches instead of being retrieved in one large joined read

The eclipselink.join-fetch hint differs from JPQL joining in that it allows multilevel fetch joins.

Examples

Example 4-44 shows how to use this hint in a JPA query.

Example 4-44 Using join-fetch in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink. join-fetch ", "e.address");

Example 4-45 shows how to use this hint with the @QueryHint annotation.

Example 4-45 Using join-fetch in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.FETCH, value="e.address");

See Also

For more information, see:

"EclipseLink" JPA Query Hints http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Query_Hints

EclipseLink Examples http://wiki.eclipse.org/EclipseLink/Examples/JPA/QueryOptimization

"Optimizing Queries" in Understanding EclipseLink .

"Fetch Joins" in the JPA Specification ( http://jcp.org/en/jsr/detail?id=317 )

"batch"

"left-join-fetch"

"Enhancing Performance" in Solutions Guide for EclispeLink

left-join-fetch

Use eclipselink.left-join-fetch to optimize the query: related objects will be joined into the query instead of being queries separately.

Values

Table 4-23 describes this query hint's values.

Table 4-23 Valid Values for left-join-fetch

Example 4-46 shows how to use this hint in a JPA query.

Example 4-46 Using left-join-fetch in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.LEFT_FETCH", " STRING ");

Example 4-47 shows how to use this hint with the @QueryHint annotation.

Example 4-47 Using left-join-fetch in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.LEFT_FETCH, value=" STRING ");

"Fetch Joins" in the JPA Specification ( http://jcp.org/en/jsr/detail?id=317 )

"batch"

"join-fetch"

"Enhancing Performance" in Solutions Guide for EclispeLink

Usage

With load groups, EclipseLink ensures that all relational attributes for a group are loaded. LoadGroups are only supported for queries returning objects (only a single alias can be the select clause).

Examples

Example 4-48 shows how to use this hint in a JPA query.

Example 4-48 Using load-group in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.LOAD_GROUP", MyLoadGroup);

Example 4-49 shows how to use this hint with the @QueryHint annotation.

Example 4-49 Using load-group in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.LOAD_GROUP, value="lg");

See Also

For more information, see:

"load-group.attribute"

"AttributeGroup Types and Operations" in Understanding EclipseLink

EclipseLink Attribute Group example: http://wiki.eclipse.org/EclipseLink/Examples/JPA/AttributeGroup

"@FetchGroup"

load-group.attribute

Use eclipselink.load-group.attribute to specify if the query uses a load-group that includes a list of attributes.

Usage

You must define each attribute using a separate hint. The query loads all relational attributes defined in the load group.

LoadGroups are only supported for queries returning objects (only a single alias can be the select clause). Both local and nested attributes are supported.

See Also

For more information, see:

"load-group"

maintain-cache

Use eclipselink.maintain-cache to controls whether or not query results are cached in the session cache

Values

Table 4-25 describes this query hint's valid values.

Table 4-25 Valid Values for org.eclipselink.maintain-cache

Usage

The eclipselink.maintain-cache hint provides a way to query the current database contents without affecting the current persistence context . It configures the query to return un-managed instances so any updates to entities queried using this hint would have to be merged into the persistence context.

Examples

Example 4-50 shows how to use this hint in a JPA query.

Example 4-50 Using maintain-cache in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint(QueryHints.MAINTAIN_CACHE, HintValues.FALSE);

Example 4-51 shows how to use this hint with the @QueryHint annotation.

Example 4-51 Using maintain-cache in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.MAINTAIN_CACHE, value=HintValues.FALSE);

See Also

For more information, see:

"Scaling EclipseLink Applications in Clusters" in Solutions Guide for EclispeLink

"Enhancing Performance" in Solutions Guide for EclispeLink

"EclipseLink Caches" in Understanding EclipseLink

pessimistic-lock

Use eclipselink.pessimistic-lock to specify if EclipseLink uses pessimistic locking.

Values

Table 4-26 describes this query hint's valid values.

Table 4-26 Valid Values for org.eclipse.persistence.config.PessimisticLock

Usage

The primary advantage of using pessimistic locking is that you are assured, once the lock is obtained, of a successful edit. This is desirable in highly concurrent applications in which optimistic locking may cause too many optimistic locking errors.

One drawback of pessimistic locking is that it requires additional database resources, requiring the database transaction and connection to be maintained for the duration of the edit. Pessimistic locking may also cause deadlocks and lead to concurrency issues.

Examples

Example 4-52 shows how to use this hint in a JPA query.

Example 4-52 Using pessimistic-lock in a JPA Query

import org.eclipse.persistence.config.PessimisticLock; import org.eclipse.persistence.config.QueryHints; query.setHint(QueryHints.PESSIMISTIC_LOCK, PessimisticLock.LockNoWait);

Example 4-53 shows how to use this hint with the @QueryHint annotation.

Example 4-53 Using pessimistic-lock in a @QueryHint Annotation

import org.eclipse.persistence.config.PessimisticLock; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.PESSIMISTIC_LOCK, value=PessimisticLock.LockNoWait);

See Also

For more information, see:

EclipseLink Examples http://wiki.eclipse.org/EclipseLink/Examples/JPA/PessimisticLocking

"Scaling EclipseLink Applications in Clusters" in Solutions Guide for EclispeLink

"Understanding Queries" in Understanding EclipseLink

"Building Blocks of a EclipseLink Project" in Understanding EclipseLink

prepare

Use eclipselink.prepare to specify if a query prepares (that is, generates) its SQL for each execution.

Values

Table 4-27 describes this query hint's values.

Table 4-27 Valid Values for prepare

Usage

By default, EclipseLink does not re-generate the SQL for each execution. This may improve performance.

For queries that require dynamic SQL (for example, to handle null parameters) set eclipselink.prepare to false .

Examples

Example 4-54 shows how to use this hint in a JPA query.

Example 4-54 Using prepare in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.PREPARE", "TRUE");

Example 4-55 shows how to use this hint with the @QueryHint annotation.

Example 4-55 Using prepare in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.PREPARE, value="TRUE");

See Also

For more information, see:

"Understanding Queries" in Understanding EclipseLink

query-results-cache

Use eclipselink.query-results-cache to specify that the query should use a results cache.

Values

Table 4-28 describes this persistence property's values.

Table 4-28 Valid Values for query-results-cache

Example 4-56 shows how to use this hint in a JPA query.

Example 4-56 Using query-results-cache in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.QUERY_RESULTS_CACHE", "TRUE");

Example 4-57 shows how to use this hint with the @QueryHint annotation.

Example 4-57 Using query-results-cache in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.QUERY_RESULTS_CACHE, value="TRUE");

Example 4-58 shows how to use this hint in an orm.xml file.

Example 4-58 Using query-results-cache in orm.xml File

<?xml version="1.0"?> <entity-mappings xmlns="http://www.eclipse.org/eclipselink/xsds/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.eclipse.org/eclipselink/xsds/persistence/orm http://www.eclipse.org/eclipselink/xsds/eclipselink_orm_2_4.xsd" version="2.4"> <entity name="Employee" class="org.acme.Employee" access="FIELD"> <named-query name="findAllEmployeesInCity" query="Select e from Employee e where e.address.city = :city"> <hint name=" eclipselink.query-results-cache " value="true"/> <hint name="eclipselink.query-results-cache.size" value="500"/> </named-query> </entity> </entity-mappings>

query-results-cache.expiry

Use eclipselink.query-results-cache.expiry to set the time-to-live (that is, expiration time) of the query's results cache.

Values

Table 4-29 describes this query hint's values.

Table 4-29 Valid Values for query-results-cache.expiry

Example 4-59 shows how to use this hint in a JPA query.

Example 4-59 Using query-results-cache.expiry in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.QUERY_RESULTS_CACHE_EXPIRY", "100");

Example 4-60 shows how to use this hint with the @QueryHint annotation.

Example 4-60 Using query-results-cache.expiry in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.QUERY_RESULTS_CACHE_EXPIRY, value="100");

See Also

For more information, see:

"query-results-cache"

query-results-cache.expiry-time-of-day

Use eclipselink.query-results-cache.expiry-time-of-day to set the time of day of the query's results cache expiration.

Values

Table 4-30 describes this persistence property's values.

Table 4-30 Valid Values for query-results-cache.expiry-time-of-day

Example 4-61 shows how to use this hint in a JPA query.

Example 4-61 Using query-results-cache.expiry-time-of-day in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.QUERY_RESULTS_CACHE_EXPIRY_TIME_OF_DAY", "11:15:34");

Example 4-62 shows how to use this hint with the @QueryHint annotation.

Example 4-62 Using query-results-cache.expiry-time-of-day in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.QUERY_RESULTS_CACHE_EXPIRY_TIME_OF_DAY, value="11:15:34");

See Also

For more information, see:

"query-results-cache"

query-results-cache.ignore-null

Use eclipselink.query-results-cache.ignore-null to specify if EclipseLink caches null query results

Values

Table 4-31 describes this query hint's values.

Table 4-31 Valid Values for query-results-cache.ignore-null

Usage

You can use this query hint to use query cache as a secondary key index, and allow inserts of new objects.

Examples

Example 4-63 shows how to use this hint in a JPA query.

Example 4-63 Using query-results-cache.ignore-null in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.QUERY_RESULTS_CACHE_IGNORE_NULL", "TRUE");

Example 4-64 shows how to use this hint with the @QueryHint annotation.

Example 4-64 Using query-results-cache.ignore-null in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.QUERY_RESULTS_CACHE_IGNORE_NULL, value="TRUE");

See Also

For more information, see:

"query-results-cache"

query-results-cache.randomize-expiry

Use eclipselink.query-results-cache.randomize-expiry to specify the expiry time ( query-results-cache.expiry ) should be randomized by 10% of its set value.

Values

Table 4-32 describes this query hint's values.

Table 4-32 Valid Values for query-results-cache.randomize-expiry

Usage

Use this query hint to avoid bottlenecks from multiple cached values expiring at a fixed time.

Examples

Example 4-65 shows how to use this hint in a JPA query.

Example 4-65 Using query-results-cache.randomize-expiry in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.QUERY_RESULTS_CACHE_RANDOMIZE_EXPIRY", "TRUE");

Example 4-66 shows how to use this hint with the @QueryHint annotation.

Example 4-66 Using query-results-cache.randomize-expiry in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.QUERY_RESULTS_CACHE_RANDOMIZE_EXPIRY, value="TRUE");

See Also

For more information, see:

"query-results-cache"

"query-results-cache.expiry"

query-results-cache.size

Use eclipselink.query-results-cache.size to set the fixed size of the query's results cache.

Values

Table 4-33 describes this query hint's values.

Table 4-33 Valid Values for query-results-cache.size

Example 4-67 shows how to use this hint in a JPA query.

Example 4-67 Using query-results-cache.size in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.QUERY_RESULTS_CACHE_SIZE", "150");

Example 4-68 shows how to use this hint with the @QueryHint annotation.

Example 4-68 Using query-results-cache.size in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.QUERY_RESULTS_CACHE_SIZE, value="150");

See Also

For more information, see:

"query-results-cache"

query-results-cache.type

Use eclipselink.query-results-cache.type to set the cache type used for the query's results cache.

Values

Table 4-34 describes this query hint's values.

Table 4-34 Valid Values for query-results-cache.type

Example 4-69 shows how to use this hint in a JPA query.

Example 4-69 Using query-results-cache.type in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.QUERY_RESULTS_CACHE_TYPE", "FULL");

Example 4-70 shows how to use this hint with the @QueryHint annotation.

Example 4-70 Using query-results-cache.type in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.QUERY_RESULTS_CACHE_TYPE, value="FULL");

See Also

For more information, see:

"@Cache"

"Caching Overview" http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic JPA Development/Caching/Caching Overview

"EclipseLink Caches" in the Understanding EclipseLink

"Scaling EclipseLink Applications in Clusters" in Solutions Guide for EclispeLink

query-type

Use eclipselink.query-type to specify which EclipseLink query type to use for the query.

Values

Table 4-35 describes this query hint's valid values.

Table 4-35 Valid Values for org.eclipse.persistence.config.QueryType

Usage

By default, EclipseLink uses org.eclipse.persistence.queries.ReportQuery or org.eclipse.persistence.queries.ReadAllQuery for most JPQL queries. Use the eclipselink.query-type hint lets to specify another query type, such as org.eclipse.persistence.queries.ReadObjectQuery for queries that will return a single object.

Examples

Example 4-71 shows how to use this hint in a JPA query.

Example 4-71 Using query-type in a JPA Query

import org.eclipse.persistence.config.QueryType; import org.eclipse.persistence.config.QueryHints; query.setHint(QueryHints.QUERY_TYPE, QueryType.ReadObject);

Example 4-72 shows how to use this hint with the @QueryHint annotation.

Example 4-72 Using query-type in a @QueryHint Annotation

import org.eclipse.persistence.config.QueryType; import org.eclipse.persistence.config.TargetDatabase; @QueryHint(name=QueryHints.QUERY_TYPE, value=QueryType.ReadObject);

See Also

For more information, see:

"Queries" in Understanding EclipseLink

Example 4-73 shows how to use this hint in a JPA query.

Example 4-73 Using read-only in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint(QueryHints.READ_ONLY, HintValues.TRUE);

Example 4-74 shows how to use this hint with the @QueryHint annotation.

Example 4-74 Using read-only in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.READ_ONLY, value=HintValues.TRUE);

See Also

For more information, see:

"Oracle EclipseLink JPA Performance Tuning" in Oracle Fusion Middleware Performance and Tuning Guide

refresh

Use eclipselink.refresh to specify whether or not to update the EclipseLink session cache with objects returned by the query.

Values

Table 4-37 describes this query hint's valid values.

Table 4-37 Valid Values for eclipselink.refresh

Usage

The eclipselink.refresh query hint configures the query to refresh the resulting objects in the cache and persistence context with the current state of the database. It also refreshes the objects in the shared cache, unless a flush has occurred. Any unflushed changes made to the objects are lost, unless this query triggers a flush before it executes). The refresh will cascade relationships based on the REFRESH_CASCADE hint value.

Examples

Example 4-75 shows how to use this hint in a JPA query.

Example 4-75 Using refresh in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint(QueryHints. REFRESH , HintValues.TRUE);

Example 4-76 shows how to use this hint with the @QueryHint annotation.

Example 4-76 Using refresh in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints. REFRESH , value=HintValues.TRUE);

See Also

For more information, see:

"refresh.cascade"

refresh.cascade

Use eclipselink.refresh.cascade to specify if a refresh query should cascade the refresh to relationships.

Values

Table 4-38 describes this query hint's valid values.

Table 4-38 Valid Values for eclipselink.refresh.cascade

Example 4-77 shows how to use this hint in a JPA query.

Example 4-77 Using refresh.cascade in a JPA Query

import org.eclipse.persistence.config.HintValues import oorg.eclipse.persistence.config.QueryHints; query.setHint(QueryHints. REFRESH_CASCADE , CascadePolicy.CascadeAllParts);

Example 4-78 shows how to use this hint with the @QueryHint annotation.

Example 4-78 Using refresh.cascade in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints. REFRESH_CASCADE , value=CascadePolicy.CascadeAllParts);

See Also

For more information, see:

"refresh"

result-collection-type

Use eclipselink.result-collection-type to configure the collection class implementation for the query's results.

Values

Table 4-39 describes this query hint's values.

Table 4-39 Valid Values for result-collection-type

Usage

If you use a Collection type that is not a List , you must use getResultCollection() or getSingleResult() instead of getResultList() .

Examples

Example 4-79 show how to use this hint in a JPA query.

Example 4-79 Using result-collection-type in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.RESULT_COLLECTION_TYPE", " <CLASS_NAME> ");

Example 4-80 shows how to use this hint with the @QueryHint annotation.

Example 4-80 Using result-collection-type in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.RESULT_COLLECTION_TYPE, value=" <CLASS_NAME> ");

See Also

For more information, see:

"Collection Mappings" in the Understanding EclipseLink

Usage

A SQL hint can be used on certain database platforms to define how the query uses indexes and other such low level usages. The SQL hint will be included in the SQL, after the SELECT / INSERT / UPDATE / DELETE command.

Examples

Example 4-81 shows how to use this hint in a JPA query.

Example 4-81 Using sql.hint in a JPA Query

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; query.setHint("eclipselink.HINT", "/*+ index(scott.emp ix_emp) * /");

Example 4-82 shows how to use this hint with the @QueryHint annotation.

Example 4-82 Using sql.hint in a @QueryHint Annotation

import org.eclipse.persistence.config.HintValues; import org.eclipse.persistence.config.QueryHints; @QueryHint(name=QueryHints.HINT, value="/*+ index(scott.emp ix_emp) * /");

See Also

For more information, see:

"Query Hints" in Understanding EclipseLink

"Query" in Solutions Guide for EclispeLink

Section 10.3.1 "NamedQuery Annotation" in the JPA Specification ( http://jcp.org/en/jsr/detail?id=317 )