9 Understanding Queries

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:

9.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:

9.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 the EclipseLink DatabaseQuery object.

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

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

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

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

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

9.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 Section 9.4, "About the Criteria API."

9.2.1 EclipseLink Extensions to JPQL

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.

http://jcp.org/en/jsr/detail?id=338

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 nonmapped columns

  • TABLE operation to allow querying on non mapped tables

  • UNION, INTERSECT, EXCEPT support

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

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

9.2.2 EclipseLink Special Operators in JPQL

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

  • COLUMN

  • FUNCTION

  • OPERATOR

  • SQL

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

9.3 About SQL Query Language

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

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

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 PLSQL call for Oracle stored procedures with PLSQL data types.

9.3.1 Stored Procedures

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 INOUT or OUT parameter).

If the stored procedure is not defined using metadata, then parameter and result set information must be provided dynamically.

9.3.1.1 EclipseLink Extensions to Stored Procedures

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 RECORD and 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 SQLResultMapping.

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 @NamedStoredFunctionQuery).

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.

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

http://jcp.org/en/jsr/detail?id=338

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.

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.

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.

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.

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.

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.

9.4.1 EclipseLink Extensions to the Criteria API

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.

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

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.

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.

9.5.1 EclipseLink Extensions to Native Query Support

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 10, "Understanding EclipseLink Expressions".

9.6 About Query Hints

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.

http://jcp.org/en/jsr/detail?id=338

The definitions of query hints are vendor-specific. The following sections describe JPA query hints and EclipseLink query hints:

9.6.1 JPA Cache 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 find() operation.

JPA 2.0 defines the following query hint properties to configure a queries interaction with the shared cache:

  • javax.persistence.cache.retrieveMode

  • javax.persistence.cache.storeMode

9.6.2 EclipseLink Extensions to Cache Query Hints

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 public static final field in the appropriate configuration class in org.eclipse.persistence.config package, including the following:

  • HintValues

  • CacheUsage

  • PessimisticLock

  • QueryType

You can specify EclipseLink query hints (JPA query extensions) either by using the @QueryHint annotation, by including the hints in the orm.xml or 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:

  • 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 Java Persistence API (JPA) Extensions Reference for Oracle TopLink.

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

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

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

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.

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.

9.7.1 EclipseLink Expression Support for Downcasting

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.

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.

9.8 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:

9.8.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 EclipseLink DatabaseQuery method setHintString.

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

9.8.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 the setHierarchicalQueryClause method which appears in the EclipseLink DatabaseQuery subclass ReadAllQuery.

9.8.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. For more information, see "Using Oracle Flashback Technology" in Oracle Database Advanced Application Developer's Guide.

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