About Expression Components

Database Functions

EclipseLink expressions support a variety of database functions, including, but not limited to, the following:

• toUpperCase

• toLowerCase

• toDate

• decode

• locate

• monthsBetween

• nextDay

• replace

• reverse

• substring

• translate

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 WHERE clause.

You access most functions using Expression methods such as toUpperCase.

Some functions have very specific purpose: you can use ascending and descending functions only within an ordering expression to place the result in ascending or descending order.

You can use aggregate functions, such as average, minimum, maximum, sum and so forth, with the ReportQuery.

Database Operators

Operators are relation operations that compare two values. EclipseLink expressions support the following operators:

• like

• notLike

• equal

• notEqual

• lessThan

• lessThanEqual

• equalsIgnoreCase

• greaterThan

• greaterThanEqual

• in

• notIn

• between

• notBetween

Mathematical Functions

Mathematical functions are available through the ExpressionMath class. Mathematical function support in expressions is similar to the support provided by the Java class java.lang.Math.

XMLType Functions

You can use the following operators when constructing queries against data mapped to Oracle Database XMLType column:

• 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 XMLType object.

• getNumberVal—Gets the numerical representation of an XMLType object.

• isFragment—Evaluates to 0 if the XML is a well formed document. Evaluates to 1 if the document is a fragment.

About Joins

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.

For example, 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:

(emp.get("firstName").equal("Steve")).or(emp.get("address"). get("city").equal("Ottawa"))

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 Expression method getAllowingNull, rather than get, and Expression method anyOfAllowingNone, rather than anyOf.

For example:

(emp.get("firstName").equal("Steve")).or(
emp.getAllowingNull("address").get("city").equal("Ottawa"))

Support and syntax for outer joins vary widely between databases and database drivers. EclipseLink supports outer joins for most databases.

Using EclipseLink Expression API for Joins

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.

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.