Understanding EclipseLink, 2.5
  Go To Table Of Contents
 Search
 PDF

About Expression Components

A simple expression usually consists of the following three parts:

In the following code fragment:

expressionBuilder.get("lastName").equal("Smith"); 

The 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 Expression:

Boolean Logic

Expressions use standard boolean operators, such as AND, OR, and NOT, and you can combine multiple expressions to form more complex expressions.

Database Functions and Operators

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

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


NoteNote:

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


NoteNote:

Ordering is not supported for in-memory queries.


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.

Platform and User-Defined Functions

You can use the Expression method 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 for One-to-One and Aggregate Object Relationships

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.

Expressions for Joining and Complex Relationships

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:

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.

Table 11-1 Expression API for Joins

Expression API Type of Join Type of Mapping

get

inner

one-to-one

getAllowingNull

outer

one-to-one

anyOf

inner

one-to-many, many-to-many

anyOfAllowingNone

outer

one-to-many, many-to-many


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.