This chapter describes how 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:
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:
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 the EclipseLink
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
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.
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.
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.
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.
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
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 Section 9.4, "About the Criteria API."
EclipseLink supports all of the statements and clauses described in "Query Language" in the JPA Specification, including
SELECT queries, update and delete statements,
WHERE clauses, literal values, and database functions. For more information, see the JPA Specification.
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
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
REGEXP function for regular expression querying
Usage of sub-selects in the
ON clause support for defining
Joins between independent entities
Usage of an alias on a
COLUMN operation to allow querying on nonmapped columns
TABLE operation to allow querying on non mapped tables
Usage of object variables in =, <>,
For descriptions of these extensions, see "EclipseLink Query Language" in Java Persistence API (JPA) Extensions Reference for Oracle TopLink.
EclipseLink defines several special JPQL operators that allow performing database operations that are not possible in basic JPQL. These include:
For descriptions of these operators, see "Special Operators" in Java Persistence API (JPA) Extensions Reference for Oracle TopLink.
Using EclipseLink, you can express a query using the following query languages:
EclipseLink Expressions (see Chapter 10, "Understanding EclipseLink Expressions")
SQL is the most common query language for applications that use a relational database data source. 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
You can execute custom SQL directly using
executeNonSelectingCall, or you can construct a
DatabaseQuery with an appropriate
EclipseLink provides a variety of SQL
Call objects for use with stored procedures and, with Oracle Database, stored functions. EclipseLink also supports PLSQL call for Oracle stored procedures with PLSQL data types.
As described in the "Stored Procedures" section of the JPA specification (
http://jcp.org/en/jsr/detail?id=338), native SQL allows you to use named stored procedures either dynamically or specified by the
NamedStoredProcedureQuery annotation. If you use annotations, the stored procedure must exist in the database. The annotation allows you to specify the types of all parameters to the stored procedure, their corresponding parameter modes, and the mapping of the result sets.
Metadata must be provided for all parameters by using the
StoredProcedureParameter annotation. Parameters must be specified in the order in which they occur in the parameter list of the stored procedure. If parameter names are used, the parameter name is used to bind the parameter value and to extract the output value (if the parameter is an
If the stored procedure is not defined using metadata, then parameter and result set information must be provided dynamically.
EclipseLink defines annotation extensions that allow the use of PLSQL stored procedures (such as
@NamedPLSQLStoredProcedureQuery) and stored functions (such as
@NamedPLSQLStoredFunctionQuery). The PLSQL annotations allow you to use complex PLSQL types such as
TABLE, that are not accessible from JDBC. The annotations contain attributes for specifying the function (or procedure) name, the return value of the stored function, any query hints, the parameters for the stored function, and the name of the
Parameters for the stored function (or procedure) are specified with the
@PLSQLParameter annotation. The
@PLSQLRecord annotation defines a database PLSQL
RECORD type for use within PLSQL procedures.
EclipseLink also defines annotation extensions that allow the use of non-PLSQL stored procedures (such as
@NamedStoredProcedureQuery) and stored functions (such as
For a list of the EclipseLink extensions for stored procedures and links to their descriptions, see "Stored Procedure and Function Annotations" in Java Persistence API (JPA) Extensions Reference for Oracle TopLink.
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 metamodel generated classes to define the query-able attributes of a class. 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 metadata 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.
CriteriaBuilder is the main interface into the Criteria API. A
CriteriaBuilder is obtained from an
EntityManager or an
EntityManagerFactory using the
CriteriaBuilder is used to construct
CriteriaQuery objects and their expressions. The Criteria API currently only supports select queries.
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
CriteriaQuery is used with the
createQuery() API to create a JPA Query.
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
in operations can also be called on
Expression objects. The
not operation can also be called on
Subqueries can be used in the Criteria API in the
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
some operations, or with an
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
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.
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.
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.
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.
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
Embedding of SQL within an
Usage of sub-selects in the from clause
ON clause support
Access to unmapped columns and tables
Expressions can be combined with EclipseLink
DatabaseQuerys to provide additional functionality:
Unions, intersect and except clauses;
Hierarchical connect by clauses;
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,
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.
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.
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.
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.
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.
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:
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.
ExpressionBuilder class is the factory for constructing new expressions.
You can specify a selection criterion as an
setSelectionCriteria, and in a finder that takes an
For more information about using EclipseLink expressions, see Chapter 10, "Understanding EclipseLink Expressions".
You can use a query hint to customize or optimize a JPA query. The
NamedQuery annotation is used to specify a named query in the Java Persistence query language. This annotation contains a
hints element that can be used to specify query properties and hints. For more information on this annotation, see "NamedQuery Annotation" in the JPA Specification.
The definitions of query hints are vendor-specific. The following sections describe JPA query hints and EclipseLink query hints:
The JPA query hints allow for queries or the
find() operation to bypass, or refresh the shared cache. JPA cache query hints can be set on named or dynamic queries, or set in the properties map passed to the
JPA 2.0 defines the following query hint properties to configure a queries interaction with the shared cache:
The EclipseLink cache query hints allow for queries or the
find() operation to interact with the cache is the following ways:
Bypass the cache check and force accessing the database, but still resolve with the cache.
Refresh the cache from the database results.
Bypass the cache and persistence unit and return detached objects.
Bypass the persistence context and return read-only objects.
Allow queries that use Id fields, and other fields to obtain cache hits.
Query the cache first, and only access the database if the object is not found.
Only query the cache, and avoid accessing the database.
Conform a query with non-flushed changes in a persistence context.
Queries that access the cache have the following restrictions:
Sub-selects are not supported.
Certain database functions are not supported.
Queries must return a single set of objects.
Grouping is not supported.
Uninstantiated lazy relationships may not be able to be queried.
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
final field in the appropriate configuration class in
org.eclipse.persistence.config package, including the following:
You can specify EclipseLink query hints (JPA query extensions) either by using the
@QueryHint annotation, by including the hints in the
eclipselink-orm.xml files. or by using the
setHint() method when executing a named or dynamic query (JPQL or Criteria).
Query settings and query hints that affect the generated SQL are not supported with SQL queries. Unsupported query hints include:
join-fetch is supported, but requires that the SQL selects all of the joined columns.
fetch-group is supported, but requires that the SQL selects all of the fetched columns.
pessimistic-lock is supported, but requires that the SQL locks the result rows.
For descriptions of these extensions, see "EclipseLink Query Language" in Java Persistence API (JPA) Extensions Reference for Oracle TopLink.
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.
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
In JPQL, downcasting is accomplished in the
FROM clause, using
AS in the
Criteria API includes the casting operator
Expression.as(type). This expression does a simple cast that allows matching of types within the generics.
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
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.
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
ExpressionBuilder). The parent expression of a cast must be an
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
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.
When you use EclipseLink with Oracle Database, you can make use of the following Oracle-specific query features from within your EclipseLink applications:
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 EclipseLink
For more information, see the performance tuning guide for your database.
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 the
setHierarchicalQueryClause method which appears in the EclipseLink
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. For more information, see "Using Oracle Flashback Technology" in Oracle Database Advanced Application Developer's Guide.
A stored function is an Oracle Database mechanism that provides all the capabilities of a stored procedure in addition to returning a value. provides a number of annotations for working with stored functions as well as stored procedures. For a list of the EclipseLink annotation extensions for stored functions and procedures and links to their descriptions, see "Stored Procedure and Function Annotations" in Java Persistence API (JPA) Extensions Reference for Oracle TopLink.