Fusion Middleware Documentation
Advanced Search

Understanding Oracle TopLink

10 Understanding Queries

EclipseLink enables you to create, read, update, and delete persistent objects or data using queries in both Java EE and non-Java EE applications for both relational and nonrelational data sources.

This chapter includes the following sections:

10.1 Query Concepts

In general, querying a data source means performing an action on or interacting with the contents of the data source. To do this, you must be able to perform the following:

  • Define an action in a syntax native to the data source being queried.

  • Apply the action in a controlled fashion.

  • Manage the results returned by the action (if any).

You must also consider how the query affects the EclipseLink cache.

This section introduces query concepts unique to EclipseLink, including the following:

10.1.1 Call Objects

The Call object encapsulates an operation or action on a data source. The EclipseLink API provides a variety of Call types such as structured query language (SQL), Java Persistence Query Language (JPQL), and Extensible Markup Language (XML).

You can execute a Call directly or in the context of a DatabaseQuery.

10.1.2 DatabaseQuery Objects

A DatabaseQuery object is an abstraction that associates additional customization and optimization options with the action encapsulated by a Call. By separating these options from the Call, EclipseLink can provide sophisticated query capabilities across all Call types.

10.1.3 Data-Level and Object-Level Queries

Queries can be defined for objects or data, as follows:

  • Object-level queries are object-specific and return data as objects in your domain model. They are the preferred type of query for mapped data. By far, object-level DatabaseQuery queries are the most common query used in EclipseLink.

  • Data-level queries are used to query database tables directly, and are an appropriate way to work with unmapped data.

10.1.4 Summary Queries

While data-level queries return raw data and object-level queries return objects in your domain model, summary queries return data about objects. EclipseLink provides partial object queries to return a set of objects with only specific attributes populated, and report queries to return summarized (or rolled-up) data for specific attributes of a set of objects.

10.1.5 Descriptor Query Manager

In addition to storing named queries applicable to a particular class, you can also use the DescriptorQueryManager to override the default action that EclipseLink defines for common data source operations.

10.1.6 EclipseLink Expressions

EclipseLink expressions let you specify query search criteria based on your domain object model. When you execute the query, EclipseLink translates these search criteria into the appropriate query language for your platform.

The EclipseLink API provides the following two public classes to support expressions:

  • The Expression class represents an expression that can be anything from a simple constant to a complex clause with boolean logic. You can manipulate, group, and integrate expressions.

  • The ExpressionBuilder class is the factory for constructing new expressions.

You can specify a selection criterion as an Expression with DatabaseQuery method setSelectionCriteria, and in a finder that takes an Expression.

For more information about using EclipseLink expressions, see Chapter 11, "Understanding EclipseLink Expressions".

10.1.7 Query Keys

A query key is a schema-independent alias for a database field name. Using a query key, you can refer to a field using a schema-independent alias. In relational projects only, EclipseLink automatically creates query keys for all mapped attributes. The name of the query key is the name of the class attribute specified in your object model.

You can configure query keys in a class descriptor or interface descriptor. You can use query keys in expressions and to query variable one-to-one mappings.

By default, EclipseLink creates query keys for all mapped attributes, but in some scenarios you may find it beneficial to add your own.

10.2 About JPQL Queries

The Java Persistence Query Language (JPQL) is the query language defined by JPA. JPQL is similar to SQL, but operates on objects, attributes and relationships instead of tables and columns. JPQL can be used for reading (SELECT), as well as bulk updates (UPDATE) and deletes (DELETE). JPQL can be used in a NamedQuery (through annotations or XML) or in dynamic queries using the EntityManager createQuery() API.

The disadvantage of JPQL is that dynamic queries require performing string concatenations to build queries dynamically from web forms or dynamic content. JPQL is also not checked until runtime, making typographical errors more common. These disadvantages are reduced by using the query criteria API, described in the next section.

For more information, see Chapter 4 "Query Language" in the JPA Specification.


10.2.1 Select Queries

Select queries can be used to read objects from the database. Select queries can return a single object or data element, a list of objects or data elements, or an object array of multiple objects and data. SELECT Clause

The SELECT clause can contain object expressions, attribute expressions, functions, sub-selects, constructors and aggregation functions. Aggregation functions

Aggregation functions can include summary information on a set of objects. These include MIN, MAX, AVG, SUM, COUNT. These functions can be used to return a single result, or can be used with a GROUP BY to return multiple results. Constructors

The NEW operator can be used with the fully-qualified class name to return data objects from a JPQL query. These will not be managed objects, and the class must define a constructor that matches the arguments of the constructor and their types. Constructor queries can be used to select partial data or reporting data on objects, and get back a class instance instead of an object array. FROM Clause

The FROM clause defines what is being queried. A typical FROM clause will contain the entity name being queried and assign it an alias.

JPQL allows for multiple root level objects to be queried. Caution should be used when doing this, as it can result in Cartesian products of the two tables. The WHERE or ON clause should ensure the two objects are joined in some way.

The entity name used in JPQL comes from the name attribute of the @Entity annotation or XML. It defaults to the simple entity class name. EclipseLink also allows for the fully-qualified class name of the entity to be used. JOIN

A JOIN clause can also be used in the FROM clause. The JOIN clause allows any of the object's relationships to be joined into the query so they can be used in the WHERE clause. JOIN does not mean the relationships will be fetched, unless the FETCH option is included.

JOIN can be used with OneToOne, ManyToOne, OneToMany, ManyToMany and ElementColleciton mappings. When used with a collection relationship you can join the same relationship multiple times to query multiple independent values. JOIN FETCH

The FETCH option can be used on a JOIN to fetch the related objects in a single query. This avoids additional queries for each of the object's relationships, and ensures that the relationships have been fetched if they were LAZY. EclipseLink also supports batch fetching through query hints.

JOIN FETCH normally allows an alias. The alias should be used with caution, as it can affect how the resulting objects are built. Objects should normally always have the same data, no matter how they were queried, this is important for caching and consistency. This is only an issue if the alias is used in the WHERE clause on a collection relationship to filter the related objects that will be fetched. This should not be done, but is sometimes desirable, in which case the query should ensure it has been set to BYPASS the cache. LEFT JOIN

By default all joins in JPQL are INNER joins. This means that results that do not have the relationship will be filtered from the query results. To avoid this, a join can be defined as an OUTER join using the LEFT options. ON

The JOIN condition used for a join comes from the mapping's join columns. This means that the JPQL user is normally free from having to know how every relationship is joined. In some cases it is desirable to append additional conditions to the join condition, normally in the case of outer joins. This can be done through the ON clause. EclipseLink also supports usage of the ON clause between two root level objects.

For INNER joins, EclipseLink will normally append the join condition to the WHERE clause, but this can be configured in the DatabasePlatform. Sub-selects in FROM clause

Sub-selects are supported in the FROM clause. This requires that the database supports this functionality. ORDER BY clause

ORDER BY allows the ordering of the results to be specified. Multiple values can be ordered, either ascending (ASC) or descending (DESC). EclipseLink allows functions, sub-selects and other operations in the ORDER BY clause. EclipseLink allows objects expressions to be used in the ORDER BY. In the case of entity objects, they are ordered by their Id, in case of embeddable objects, they are ordered by all of their fields. EclipseLink also allows for NULL ordering to be specified (either FIRST or LAST). GROUP BY Clause

GROUP BY allows for summary information to be computed on a set of objects. GROUP BY is normally used in conjunction with aggregation functions. EclipseLink supports using objects, functions and sub-selects in the GROUP BY clause. HAVING Clause

The HAVING clause allows for the results of a GROUP BY to be filtered. EclipseLink supports using comparisons, objects, functions and sub-selects in the HAVING clause. UNION

EclipseLink supports UNION, INTERSECT and EXCEPT operations. UNION allows the results of two queries with equivalent result structures to be combined into a single query. The unique results from both queries will be returned. If the ALL option is used, then results found in both queries will be duplicated.

INTERSECT returns only the results that are found in both queries. EXCEPT removes the results from the second query from the results from the first query.

The JPA spec does not support union operations.

10.2.2 WHERE Clause

The WHERE clause is normally the main part of the query as it defines the conditions that filter what is returned. The WHERE clause can use any comparison operation, logical operation, functions, attributes, objects, and sub-selects. The comparison operations include =, <, >, <=, >=, <>, LIKE, BETWEEN, IS NULL, and IN. NOT can also be used with any comparison operation (NOT LIKE, NOT BETWEEN, IS NOT NULL, NOT IN). The logical operations include AND, OR, and NOT.

EclipseLink also supports the REGEXP operation to perform regular expression comparisons (requires database to support regular expressions). EclipseLink allows for functions and sub-selects to be used with any operation.

The IN operation allows for a list of values or parameters, a single list parameter, or a sub-select.

A sub-select can be used with any operation provided it returns a single value, or if the ALL or ANY options are used. ALL indicates the operation must be true for all elements returned by the sub-select, ANY indicates the operation must be true for any of the elements returned by the sub-select.

EclipseLink allows the =, <>, IS NULL, IS NOT NULL, IN and NOT IN operations on objects. If IN is used on an object and the object has a composite Id, this requires the database to support nested IN lists.

10.2.3 Update Queries

You can perform bulk update of entities with the UPDATE statement. This statement operates on a single entity type and sets one or more single-valued properties of the entity subject to the condition in the WHERE clause. Update queries provide an equivalent to the SQL UPDATE statement, but with JPQL conditional expressions.

Update queries do not allow joins, but do support sub-selects. OneToOne and ManyToOne relationships can be traversed in the WHERE clause. Collection relationships can still be queried through using an EXISTS in the WHERE clause with a sub-select. Update queries can only update attributes of the object or its embeddables, its relationships cannot be updated. Complex update queries are dependent on the database's update support, and may make use of temp tables on some databases.

Update queries should only be used for bulk updates, regular updates to objects should be made by using the object's set methods within a transaction and committing the changes.

Update queries return the number of modified rows on the database (row count).

The persistence context is not updated to reflect results of update operations. If you use a transaction-scoped persistence context, you should either execute the bulk operation in a transaction all by itself, or be the first operation in the transaction. That is because any entity actively managed by the persistence context will remain unaware of the actual changes occurring at the database level.

The objects in the shared cache that match the update query will be invalidated to ensure subsequent persistence contexts see the updated data.

10.2.4 Delete Queries

You can perform bulk removal of entities with the DELETE statement. Delete queries provide an equivalent to the SQL DELETE statement, but with JPQL conditional expressions.

Delete queries do not allow joins, but do support sub-selects. OneToOne and ManyToOne relationships can be traversed in the WHERE clause. Collection relationships can still be queried through using an EXISTS in the WHERE clause with a sub-select. Complex delete queries are dependent on the database's delete support, and may make use of temp tables on some databases.

Delete queries should only be used for bulk deletes, regular deletes to objects should be performed through calling the EntityManager remove() API.

Delete queries return the number of deleted rows on the database (row count).


Delete queries are polymorphic: any entity subclass instances that meet the criteria of the delete query will be deleted. However, delete queries do not honor cascade rules: no entities other than the type referenced in the query and its subclasses will be removed, even if the entity has relationships to other entities with cascade removes enabled. Delete queries will delete the rows from join and collection tables.

The persistence context is not updated to reflect results of delete operations. If you use a transaction-scoped persistence context, you should either execute the bulk operation in a transaction all by itself, or be the first operation in the transaction. That is because any entity actively managed by the persistence context will remain unaware of the actual changes occurring at the database level.

The objects in the shared cache that match the delete query will be invalidated to ensure subsequent persistence contexts do not see the removed objects.

10.2.5 Parameters

JPA defines named parameters, and positional parameters. Named parameters can be specified in JPQL using the syntax :<name>. Positional parameters can be specified in JPQL using the syntax ? or ?<position>. Positional parameters start at position 1 not 0. Literals

Literal values can be in-lined in JPQL for standard Java types. In general it is normally better to use parameters instead of in-lining values. In-lined arguments will prevent the JPQL from benefiting from the JPQL parser cache, and can potentially make the application vulnerable to JPQL injections attacks.

Each Java type defines its own in-lining syntax:

  • String - '<string>'

    To define a ' (quote) character in a string, the quote is double quoted, i.e. 'Baie-D''Urfé'.

  • Integer - +|-<digits>

  • Long - +|-<digits>L

  • Float - +|-<digits>.<decimal><exponent>F

  • Double - +|-<digits>.<decimal><exponent>D

  • Boolean - TRUE | FALSE

  • Date - {d'yyyy-mm-dd'}

  • Time - {t'hh:mm:ss'}

  • Timestamp - {ts'yyyy-mm-dd hh:mm:ss.nnnnnnnnn'} -

  • Enum - package.class.enum

  • null - NULL

10.2.6 Functions

JPQL supports several database functions. These functions are database independent in name and syntax, but require database support. If the database supports an equivalent function, then the standard JPQL function is supported. If the database does not provide any way to perform the function, then it is not supported. For mathematical functions (+, -, /, *) BEDMAS rules apply.

In JPQL, support functions can be used in the SELECT, WHERE, ORDER BY, GROUP BY and HAVING clauses, as well as inside other functions, with comparison operators, and in constructors.

EclipseLink provides support for several functions beyond the JPA spec. EclipseLink also supports calling specific database functions through FUNCTION, FUNC, and OPERATOR.

10.2.7 EclipseLink Special Operators

EclipseLink defines several special JPQL operators that allow performing database operations that are not possible in basic JPQL. These include:



  • SQL


For descriptions of these operators, see "Special Operators" in Oracle Fusion Middleware Java Persistence API (JPA) Extensions Reference for Oracle TopLink.

10.2.8 EclipseLink Extensions

EclipseLink provides many extensions to the standard JPA JPQL. These extensions provide access to additional database features many of which are part of the SQL standard, provide access to native database features and functions, and provide access to EclipseLink specific features.

EclipseLink's JPQL extensions include:

  • Less restrictions than JPQL, allows sub-selects and functions within operations such as LIKE, IN, ORDER BY, constructors, functions etc.

  • Allow != in place of <>

  • FUNCTION operation to call database specific functions

  • TREAT operation to downcast related entities with inheritance

  • OPERATOR operation to call EclipseLink database independent functions

  • SQL operation to mix SQL with JPQL

  • CAST and EXTRACT functions

  • REGEXP function for regular expression querying

  • Usage of sub-selects in the SELECT and FROM clause

  • ON clause support for defining JOIN and LEFT JOIN conditions

  • Joins between independent entities

  • Usage of an alias on a JOIN FETCH

  • COLUMN operation to allow querying on non mapped columns

  • TABLE operation to allow querying on non mapped tables


  • Usage of object variables in =, <>, IN, IS NULL, and ORDER BY

For descriptions of these extensions, see "EclipseLink Query Language" in Oracle Fusion Middleware Java Persistence API (JPA) Extensions Reference for Oracle TopLink.

10.3 About SQL Query Language

Using EclipseLink, you can express a query using the following query languages:

In most cases, you can compose a query directly in a given query language or, preferably, you can construct a DatabaseQuery with an appropriate Call and specify selection criteria using an Expression object. Although composing a query directly in SQL appears to be the simplest approach (and for simple operations or operations on unmapped data, it is), using the DatabaseQuery approach offers the compelling advantage of confining your query to your domain object model and avoiding dependence on data source schema implementation details.

Oracle recommends that you compose your queries using Expression.

SQL is the most common query language for applications that use a relational database data source.

You can execute custom SQL directly using Session methods executeSelectingCall and executeNonSelectingCall, or you can construct a DatabaseQuery with an appropriate Call.

EclipseLink provides a variety of SQL Call objects for use with stored procedures and, with Oracle Database, stored functions.

EclipseLink also supports PL/SQL call for Oracle stored procedures with PL/SQL data types.

10.4 About the Criteria API

The Java Persistence Criteria API is used to define dynamic queries through the construction of object-based query definition objects, rather than use of the string-based approach of JPQL. The criteria API allows dynamic queries to be built programmatically offering better integration with the Java language than a string-based 4th GL approach.

The Criteria API has two modes, the type-restricted mode, and the non-typed mode. The type-restricted mode uses a set of JPA meta-model generated classes to define the query-able attributes of a class, see Section 10.4.8, "Metamodel." The non-typed mode uses strings to reference attributes of a class.

The criteria API is only for dynamic queries, and cannot be used in meta-data or named queries. Criteria queries are dynamic queries and do not perform as well as static named queries, or even dynamic parametrized JPQL which benefit from EclipseLink's parse cache.

For more information, see Chapter 6 "Criteria API" in the JPA Specification.


10.4.1 CriteriaBuilder

CriteriaBuilder is the main interface into the Criteria API. A CriteriaBuilder is obtained from an EntityManager or an EntityManagerFactory using the getCriteriaBuilder() API. CriteriaBuilder is used to construct CriteriaQuery objects and their expressions. The Criteria API currently only supports select queries.

10.4.2 CriteriaQuery

CriteriaQuery defines a database select query. A CriteriaQuery models all of the clauses of a JPQL select query. Elements from one CriteriaQuery cannot be used in other CriteriaQuerys. A CriteriaQuery is used with the EntityManager createQuery() API to create a JPA Query.

10.4.3 Where

The where clause is normally the main part of the query as it defines the conditions (predicates) that filter what is returned. The where clause is defined using the where API on CriteriaQuery with any Predicate objects. A Predicate is obtained using a comparison operation, or a logical operation on CriteriaBuilder. The isNull, isNotNull, and in operations can also be called on Expression objects. The not operation can also be called on Predicate objects.

10.4.4 Subquery

Subqueries can be used in the Criteria API in the select, where, order, group by, or having clauses. A subquery is created from a CriteriaQuery using the subquery operation. Most subquery usage restricts the subquery to returning a single result and value, unless used with the CriteriaBuilder exists, all, any, or some operations, or with an in operation.

10.4.5 Parameters

Parameters can be defined using the parameter API on CriteriaBuilder. JPA defines named parameters, and positional parameters. For named parameters the parameter type and name are specified. For positional parameters only the parameter type is specified. Positional parameters start at position 1 not 0.

10.4.6 Functions

Several database functions are supported by the Criteria API. All supported functions are defined on CriteriaBuilder. Some functions may not be supported by some databases, if they are not SQL compliant, and offer no equivalent function.

10.4.7 Special Operations

The Criteria API defines several special operations that are not database functions, but have special meaning in JPA. Some of these operations are defined on CriteriaBuilder and some are on specific Expression interfaces.

10.4.8 Metamodel

JPA defines a meta-model that can be used at runtime to query information about the ORM mapping metadata. The meta-model includes the list of mapped attributes for a class, and their mapping types and cardinality. The meta-model can be used with the Criteria API in place of using strings to reference the class attributes.

JPA defines a set of "_" classes ("_MyEntity.java", for example) that are to be generated by the JPA provider, or IDE, that give compile time access to the meta-model. This allows typed static variables to be used in the Criteria API. This can reduce the occurrence of typos, or invalid queries in application code, by catching query issues at compile time, instead of during testing. It does however add complexity to the development process, as the meta-model static class needs to be generated, and be part of the development cycle.

10.4.9 Tuple Queries

A Tuple defines a multi-select query result. Normally an object array is returned by JPA multi-select queries, but an object array is not a very useful data structure. A Tuple is a map-like structure that allows the results to be retrieved by name or index.

10.4.10 JpaCriteriaBuilder and EclipseLink Extensions

EclipseLink's Criteria API support has fewer restrictions than specified by JPA. In general, sub-queries and object path expressions are allowed in most places, including:

  • Sub-queries in the select, group by, and order clauses;

  • Sub-query usage with functions;

  • in usage with object path expressions;

  • Order by usage with object path expressions.

EclipseLink's Criteria API support is built on top of EclipseLink native Expression API. EclipseLink provides the JpaCriteriaBuilder interface to allow the conversion of native Expression objects to and from JPA Expression objects. This allows the EclipseLink native Expression API to be mixed with the JPA Criteria API.

The EclipseLink native Expression API provides the following additional functionality:

  • Additional database functions (over 80 database functions are supported)

  • Usage of custom ExpressionOperators

  • Embedding of SQL within an Expression query

  • Usage of sub-selects in the from clause

  • ON clause support

  • Access to unmapped columns and tables

  • Historical querying

EclipseLink Expressions can be combined with EclipseLink DatabaseQuerys to provide additional functionality:

  • Unions, intersect and except clauses;

  • Hierarchical connect by clauses;

  • Batch fetching.

10.5 About Native SQL Queries

JPA allows SQL to be used for querying entity objects, or data. SQL queries are not translated, and passed directly to the database. SQL queries can be used for advanced queries that require database specific syntax, or by users who are more comfortable in the SQL language than JPQL or Java.

SQL queries are created from the EntityManager using the createNativeQuery API or via named queries. A Query object is returned and executed the same as any other JPA query. An SQL query can be created for an entity class, or return an object array of data. If returning entities, the SQL query must return the column names that the entity's mappings expect, or an SqlResultSetMapping can be used. An SqlResultSetMapping allows the SQL result set to be mapped to an entity, or set of entities and data.

SQL queries can be used to execute SQL or DML (Data Manipulation Language) statements. For SQL queries that return results, getSingleResult or getResultList can be used. For SQL queries that do not return results, executeUpdate must be used. executeUpdate can only be used within a transaction. SQL queries can be used to execute database operations and some stored procedures and functions. Stored procedures that return output parameters, or certain complex stored procedures, cannot be executed with SQL queries. EclipseLink supports stored procedures through stored procedure queries.

Query settings and query hints that affect the generated SQL are not supported with SQL queries. Unsupported query hints include:

  • batch

  • history.as-of

  • inheritance.outer-join

  • sql.hint

  • join-fetchjoin-fetch is supported, but requires that the SQL selects all of the joined columns.

  • fetch-groupfetch-group is supported, but requires that the SQL selects all of the fetched columns.

  • pessimistic-lockpessimistic-lock is supported, but requires that the SQL locks the result rows.

For descriptions of these extensions, see "EclipseLink Query Language" in Oracle Fusion Middleware Java Persistence API (JPA) Extensions Reference for Oracle TopLink.

10.5.1 Parameters

Parameters to SQL queries are delimited using the question mark (?) character. Only indexed parameters are supported, named parameters are not supported. The index can be used in the delimiter, such as ?1. Parameter values are set on the Query using the setParameter API. Indexed parameters start at the index 1 not 0.

10.5.2 Named Native SQL Queries

Native SQL queries can be defined as named queries in annotations or XML using the NamedNativeQuery annotation or <named-native-query> XML element. Named native SQL queries are executed the same as any named query.

10.5.3 SQL Result Set Mapping

An SqlResultSetMapping can be used to map the results of an SQL query to an entity if the result column names do not match what the entity mappings expect. It can also be used to return multiple entities, or entities and data from a single SQL query. EntityResult and FieldResult are used to map the SQL query result column to the entity attribute. ColumnResult can be used to add a data element to the result.

SqlResultSetMappings are defined through annotations or XML using the @SqlResultSetMapping annotation or <sql-result-set-mapping> XML element. They are referenced from native SQL queries by name.

10.6 About Advanced Native Query Support

EclipseLink provides an expression framework (also known as EclipseLink Native Query Support) with which you can express queries in a database-neutral fashion as an alternative to SQL when writing queries not supported by JPQL. EclipseLink expressions offer the following advantages over SQL when you access a database:

  • Expressions are easier to maintain because the database is abstracted.

  • Changes to descriptors or database tables do not affect the querying structures in the application.

  • Expressions enhance readability by standardizing the Query interface so that it looks similar to traditional Java calling conventions.

  • Expressions enable read queries to transparently query between two classes that share a relationship. If these classes are stored in multiple tables in the database, EclipseLink automatically generates the appropriate join statements to return information from both tables.

  • Expressions simplify complex operations.

EclipseLink automatically generates the appropriate SQL from the specified expression.

The expression framework lets you work with expressions, database queries, call objects, and native queries.

  • JPA Query Using a EclipseLink DatabaseQuery

    EclipseLink DatabaseQuery is a query object that provides a robust API for handling a variety of database query requirements, including reading and writing at the object level and at the data level.

  • JPA Query Using a EclipseLink Call Object

    Using the DatabaseQuery method setCall, you can define your own EclipseLink Call to accommodate a variety of data source options, such as SQL stored procedures and stored functions.

  • Named Parameters in a Native Query

    Using EclipseLink, you can specify a named parameter in a native query using the EclipseLink # convention.

  • JPQL Positional Parameters in a Native Query

    Using EclipseLink, you can specify positional parameters in a native query using the Java Persistence Query Language (JPQL) positional parameter ?n convention to specify a parameter by number.

  • JDBC-Style Positional Parameters in a Native Query

    Using EclipseLink, you can specify positional parameters in a native query using the JDBC-style positional parameter ? convention.

10.7 About JPA Query Hints

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

For descriptions of the query hints available in EclipseLink, see "JPA Query Customization Extensions" in Oracle Fusion Middleware Java Persistence API (JPA) Extensions Reference for Oracle TopLink. See also Section 10.3.1 "NamedQuery Annotation" in the JPA Specification.


Use EclipseLink JPA query hints to:

  • Construct a JPA query

  • Specify a JPA query using the @QueryHint annotation

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

10.8 About Query Casting

Use query casting to query across attributes in subclasses when using JPA or ORM. This feature is available in JPQL, EclipseLink Expressions, and Criteria API.

10.8.1 JPA 2.0 Type

Starting with JPA 2.0, it is possible to limit the results or a query to those of a specific subclass. For example, the expression framework provides Expression.type(Class).

10.8.2 Downcasting in JPQL

In JPQL, downcasting is accomplished in the FROM clause, using TREAT...AS in the JOIN clause.

10.8.3 JPA Criteria API

Starting with JPA 2.0, the JPA Criteria API includes the casting operator Expression.as(type). This expression does a simple cast that allows matching of types within the generics.

EclipseLink extends the Criteria API to allow a cast using Expression.as(type). The as method checks the hierarchy; and if type is a subclass of the type for the expression that is being called on, a cast is implemented.

Calling a cast on a JOIN node permanently alters that node. For example, in the example above, after calling join.as(LargeProject.class), the join refers to a LargeProject.

10.8.4 EclipseLink Expression Support for Downcast

The Expression.as(Class) can also be used for downcasting. The behavior of using Expression.as(Class) is as follows:

  • An exception is thrown at query execution time if the class that is cast to is not a subclass of the class of the query key being cast.

  • Casts are only allowed on ObjectExpressions (QueryKeyExpression and ExpressionBuilder). The parent expression of a cast must be an ObjectExpression.

  • Casts use the same outer join settings as the ObjectExpression they modify

  • Casts modify their parent expression. As a result, when using a cast with a parallel expression, you must use a new instance of the parent expression.

  • Casting is not supported for TablePerClass inheritance

  • It is prudent to do a check for type in a query that does a cast.

  • EclipseLink automatically appends type information for cases where the cast results in a single type; but for classes in the middle of a hierarchy, no type information is appended to the SQL

10.9 About Oracle Extensions for Queries

When you use EclipseLink with Oracle Database, you can make use of the following Oracle-specific query features from within your EclipseLink applications:

10.9.1 Query Hints

Oracle lets you specify SQL query additions called hints that can influence how the database server SQL optimizer works. This lets you influence decisions usually reserved for the optimizer. You use hints to specify things such as join order for a join statement, or the optimization approach for a SQL call.

You specify hints using the DatabaseQuery method setHintString.

For more information, see the performance tuning guide for your database.

10.9.2 Hierarchical Queries

Oracle Database Hierarchical Queries mechanism lets you select database rows based on hierarchical order. For example, you can design a query that reads the row of a given employee, followed by the rows of people the employee manages, followed by their managed employees, and so on.

You specify a hierarchical query clause using DatabaseQuery subclass ReadAllQuery method setHierarchicalQueryClause.

10.9.3 Flashback Queries

When using EclipseLink with Oracle9i Database (or later), you can acquire a special historical session where all objects are read as of a past time, and then you can express read queries depending on how your objects are changing over time.

10.9.4 Stored Functions

A stored function is an Oracle Database mechanism that provides all the capabilities of a stored procedure in addition to returning a value.

Close Window

Table of Contents

Understanding Oracle TopLink

Expand | Collapse