A simple expression usually consists of the following three parts:
The attribute, which represents a mapped attribute or query key of the persistent class
The operator, which is an expression method that implements boolean logic, such as
The constant or comparison, which refers to the value used to select the object
In the following code fragment:
The attribute is
The operator is
The constant is the string "
expressionBuilder substitutes for the object or objects to be read from the database. In this example,
expressionBuilder represents employees.
You can use the following components when constructing an
Expressions use standard boolean operators, such as
NOT, and you can combine multiple expressions to form more complex expressions.
EclipseLink supports many database functions using standard operator names that are translated to different databases. EclipseLink operators are supported on any database that has an equivalent function (or set of functions). For more information and a list of all supported functions and operators see "OPERATOR" and "FUNCTION" in Java Persistence API (JPA) Extensions Reference for EclipseLink
EclipseLink expressions support a variety of database functions, including, but not limited to, the following:
Some functions may be database platform specific.
Database functions let you define more flexible queries. You can use these functions in either a report query using a
SELECT clause, or with comparisons in a query's selection criteria using a
You access most functions using
Expression methods such as
Some functions have very specific purpose: you can use
descending functions only within an ordering expression to place the result in ascending or descending order.
Ordering is not supported for in-memory queries.
You can use aggregate functions, such as
sum and so forth, with the
Operators are relation operations that compare two values. EclipseLink expressions support the following operators:
Mathematical functions are available through the
ExpressionMath class. Mathematical function support in expressions is similar to the support provided by the Java class
You can use the following operators when constructing queries against data mapped to Oracle Database
extract—Takes an XPath string and returns an XMLType which corresponds to the part of the original document that matches the XPath.
extractValue—Takes an XPath string and returns either a numerical or string value based on the contents of the node pointed to by the XPath.
existsNode—Takes an XPath expression and returns the number of nodes that match the XPath.
getStringVal—Gets the string representation of an
getNumberVal—Gets the numerical representation of an
isFragment—Evaluates to 0 if the XML is a well formed document. Evaluates to 1 if the document is a fragment.
You can use the
getFunction to access database functions that EclipseLink does not support directly. The
Expression API includes additional forms of the
getFunction method that allow you to specify arguments. You can also create your own custom functions. For more information, see Java API Reference for EclipseLink.
Expressions can include an attribute that has a one-to-one relationship with another persistent class. A one-to-one relationship translates naturally into an SQL join that returns a single row.
You can query against complex relationships, such as one-to-many, many-to-many, direct collection, and aggregate collection relationships. Expressions for these types of relationships are more complex to build, because the relationships do not map directly to joins that yield a single row per object.
This section describes the following:
A join is a relational database query that combines rows from two or more tables. Relational databases perform a join whenever multiple tables appear in the query's
FROM clause. The query's select list can select any columns from any of these tables.
An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition.
An outer join extends the result of an inner join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition. Outer joins can be categorized as left or right:
A query that performs a left outer join of tables A and B returns all rows from A. For all rows in A that have no matching rows in B, the database returns null for any select list expressions containing columns of B.
A query that performs a right outer join of tables A and B returns all rows from B. For all rows in B that have no matching rows in A, the database returns null for any select list expressions containing columns of A.
When you query with a join expression, EclipseLink can use joins to check values from other objects or other tables that represent parts of the same object. Although this works well under most circumstances, it can cause problems when you query against a one-to-one relationship, in which one side of the relationship is not present.
Employee objects may have an
Address object, but if the
Address is unknown, it is
null at the object level and has a null foreign key at the database level. When you attempt a read that traverses the relationship, missing objects cause the query to return unexpected results. Consider the following expression:
In this case, employees with no address do not appear in the result set, regardless of their first name. Although not obvious at the object level, this behavior is fundamental to the nature of relational databases.
Outer joins rectify this problem in the databases that support them. In this example, the use of an outer join provides the expected result: all employees named Steve appear in the result set, even if their address is unknown.
To implement an outer join, use
getAllowingNull, rather than
anyOfAllowingNone, rather than
Support and syntax for outer joins vary widely between databases and database drivers. EclipseLink supports outer joins for most databases.
You can use joins anywhere expressions are used, including: selection-criteria, ordering, report queries, partial objects, one-to-one relational mappings, and join reading.
Use the expression API shown in Table 11-1 to configure inner and outer join expressions.
Table 11-1 Expression API for Joins
|Expression API||Type of Join||Type of Mapping|
To query across a one-to-many or many-to-many relationship, use the
anyOf operation. As its name suggests, this operation supports queries that return all items on the "many" side of the relationship that satisfy the query criteria.