This division of functionality enables EclipseLink expressions to provide similar mathematical functionality to the <tt>java.lang.Math</tt> class, but keeps both the <tt>Expression</tt> and <tt>ExpressionMath</tt> classes from becoming unnecessarily complex.

This division of functionality enables EclipseLink expressions to provide similar mathematical functionality to the <tt>java.lang.Math</tt> class, but keeps both the <tt>Expression</tt> and <tt>ExpressionMath</tt> classes from becoming unnecessarily complex.

−

Line 43:

Line 44:

emp.getAddress().getStreet().equals("Meadowlands");

emp.getAddress().getStreet().equals("Meadowlands");

The expression to get the same information is similar:

The expression to get the same information is similar:

−

emp.get("address").get("street").equal("Meadowlands");

emp.get("address").get("street").equal("Meadowlands");

* Expressions allow read queries to transparently query between two classes that share a relationship. If these classes are stored in multiple tables in the database, EclipseLink automatically generates the appropriate join statements to return information from both tables.

* Expressions allow read queries to transparently query between two classes that share a relationship. If these classes are stored in multiple tables in the database, EclipseLink automatically generates the appropriate join statements to return information from both tables.

Database functions let you define more flexible queries. You can use these functions in either a report query items using a <tt>SELECT</tt> clause, or with comparisons in a query's selection criteria using a <tt>WHERE</tt> clause. The following example illustrates a code fragment that matches several last names, including "SMART", "Smith", and "Smothers":

Database functions let you define more flexible queries. You can use these functions in either a report query items using a <tt>SELECT</tt> clause, or with comparisons in a query's selection criteria using a <tt>WHERE</tt> clause. The following example illustrates a code fragment that matches several last names, including "SMART", "Smith", and "Smothers":

Line 133:

Line 132:

''''' Using a Database Function Supported by the Expression API'''''

''''' Using a Database Function Supported by the Expression API'''''

emp.get("lastName").toUpperCase().like("SM%")

emp.get("lastName").toUpperCase().like("SM%")

−

−

You access most functions using <tt>Expression</tt> methods such as <tt>toUpperCase</tt>.

You access most functions using <tt>Expression</tt> methods such as <tt>toUpperCase</tt>.

You can use aggregate functions, such as <tt>average</tt>, <tt>minimum</tt>, <tt>maximum</tt>, <tt>sum</tt> and so forth, with the <tt>ReportQuery</tt> (see [[Introduction%20to%20EclipseLink%20Queries%20(ELUG)|Report Query]]).

You can use aggregate functions, such as <tt>average</tt>, <tt>minimum</tt>, <tt>maximum</tt>, <tt>sum</tt> and so forth, with the <tt>ReportQuery</tt> (see [[Introduction%20to%20EclipseLink%20Queries%20(ELUG)|Report Query]]).

+

'''Operators'''

'''Operators'''

Line 175:

Line 171:

The [[#Example 106-4|Using a Database Function Supported by the Expression API']] example demonstrates the use of the like operator.

The [[#Example 106-4|Using a Database Function Supported by the Expression API']] example demonstrates the use of the like operator.

''''' Using a Database Function Not Supported by the Expression API'''''

''''' Using a Database Function Not Supported by the Expression API'''''

emp.get("lastName").getFunction("VacationCredit").greaterThan(42)

emp.get("lastName").getFunction("VacationCredit").greaterThan(42)

−

Line 226:

Line 220:

You can also access a custom function that you create. For more information on creating a custom function in EclipseLink, see [[#Creating and Using a User-Defined Function|Creating and Using a User-Defined Function]].

You can also access a custom function that you create. For more information on creating a custom function in EclipseLink, see [[#Creating and Using a User-Defined Function|Creating and Using a User-Defined Function]].

+

===Expressions for One-to-One and Aggregate Object Relationships===

===Expressions for One-to-One and Aggregate Object Relationships===

Line 237:

Line 232:

''''' Using an Expression with a One-to-One Relationship'''''

''''' Using an Expression with a One-to-One Relationship'''''

emp.get("address").get("country").like("S%")

emp.get("address").get("country").like("S%")

−

Line 244:

Line 238:

You can nest these relationships infinitely, so it is possible to ask for complex information, as follows:

You can nest these relationships infinitely, so it is possible to ask for complex information, as follows:

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

====Using EclipseLink Expression API for Joins====

Line 330:

Line 322:

|}

|}

−

<br>

To query across a one-to-many or many-to-many relationship, use the <tt>anyOf</tt> 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.

To query across a one-to-many or many-to-many relationship, use the <tt>anyOf</tt> 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.

Line 340:

Line 331:

''''' Using an Expression with a One-to-Many Relationship'''''

''''' Using an Expression with a One-to-Many Relationship'''''

emp.anyOf("managedEmployees").get("salary").lessThan(10000);

emp.anyOf("managedEmployees").get("salary").lessThan(10000);

−

Line 349:

Line 339:

''''' Using an Expression with a Many-to-Many Relationship'''''

''''' Using an Expression with a Many-to-Many Relationship'''''

emp.anyOf("projects").equal(someProject)

emp.anyOf("projects").equal(someProject)

−

Line 360:

Line 349:

You can also configure joins at the mapping level (see [[Configuring%20a%20Relational%20Mapping%20(ELUG)|Configuring Joining at the Mapping Level]]).

You can also configure joins at the mapping level (see [[Configuring%20a%20Relational%20Mapping%20(ELUG)|Configuring Joining at the Mapping Level]]).

'''Note:''' Calling <tt>anyOf</tt> once would result in a different outcome than if you call it twice. For example, if you query for an employee with a telephone area code of 613 and a number of 123-4599, you would use a single <tt>anyOf</tt> and a temporary variable. If you query for an employee, whose telephone has an area code of 613, and whose telephone has a number of 123-4599, you would call <tt>anyOf</tt> twice.

+

'''Note:''' Calling <tt>anyOf</tt> once would result in a different outcome than if you call it twice. For example, if you query for an employee with a telephone area code of 613 and a number of 123-4599, you would use a single <tt>anyOf</tt> and a temporary variable. If you query for an employee, who has a telephone with an area code of 613, and who has a telephone with a number of 123-4599, you would call <tt>anyOf</tt> twice.

|}

|}

−

−

<br>

==Parameterized Expressions==

==Parameterized Expressions==

Line 378:

Line 364:

You can use parameterized expressions to create reusable queries (see [[Introduction%20to%20EclipseLink%20Queries%20(ELUG)|Named Queries]]).

You can use parameterized expressions to create reusable queries (see [[Introduction%20to%20EclipseLink%20Queries%20(ELUG)|Named Queries]]).

To access one-to-many and many-to-many query keys that define a distinct join across a collection relationship, use <tt>Expression</tt> method <tt>anyOf</tt>.

To access one-to-many and many-to-many query keys that define a distinct join across a collection relationship, use <tt>Expression</tt> method <tt>anyOf</tt>.

+

+

==Multiple Expressions==

==Multiple Expressions==

Expressions support subqueries (SQL subselects) and parallel selects. To create a subquery, use a single expression builder. With parallel selects, use multiple expression builders when you define a single query. This lets you specify joins for unrelated objects at the object level.

Expressions support subqueries (SQL subselects) and parallel selects. To create a subquery, use a single expression builder. With parallel selects, use multiple expression builders when you define a single query. This lets you specify joins for unrelated objects at the object level.

Expressions that query for objects generally refer to object attributes, which may in turn refer to other objects. Data expressions refer to tables and their fields. You can combine data expressions and object expressions within a single query. EclipseLink provides two main methods for expressions that query for data: <tt>getField</tt> and <tt>getTable</tt>.

Expressions that query for objects generally refer to object attributes, which may in turn refer to other objects. Data expressions refer to tables and their fields. You can combine data expressions and object expressions within a single query. EclipseLink provides two main methods for expressions that query for data: <tt>getField</tt> and <tt>getTable</tt>.

Use Java code to create more complex expressions and to take full advantage of the features in the expressions API (see [[#How to Create an Expression Using Java|How to Create an Expression Using Java]]).

Use Java code to create more complex expressions and to take full advantage of the features in the expressions API (see [[#How to Create an Expression Using Java|How to Create an Expression Using Java]]).

−

Line 625:

Line 606:

To create EclipseLink expressions for named queries, use this procedure:

To create EclipseLink expressions for named queries, use this procedure:

# Select the attribute, specify if the query allows a null value, and click '''OK'''.Use the '''Allows Null''' and '''Allows None''' options to define an expression with an outer join.Check the '''Allows Null''' option to use the <tt>ExpressionBuilder</tt> method <tt>getAllowingNull</tt>.Check the '''Allows None''' option to use the <tt>ExpressionBuilder</tt> method <tt>anyOfAllowingNone</tt>. For more information, see [[#Using EclipseLink Expression API for Joins|Using EclipseLink Expression API for Joins]].

+

# Select the attribute, specify if the query allows a null value, and click '''OK'''.<br>Use the '''Allows Null''' and '''Allows None''' options to define an expression with an outer join.<br>Check the '''Allows Null''' option to use the <tt>ExpressionBuilder</tt> method <tt>getAllowingNull</tt>.<br>Check the '''Allows None''' option to use the <tt>ExpressionBuilder</tt> method <tt>anyOfAllowingNone</tt>. <br>For more information, see [[#Using EclipseLink Expression API for Joins|Using EclipseLink Expression API for Joins]].

# Use the '''Operator''' list to specify how EclipseLink should evaluate the expression.

# Use the '''Operator''' list to specify how EclipseLink should evaluate the expression.

The <tt>ExpressionBuilder</tt> acts as a substitute for the objects that you query. To construct a query, call methods on the <tt>ExpressionBuilder</tt> that correspond to the attributes of the objects. We recommend that you name <tt>ExpressionBuilder</tt> objects according to the type of objects against which you do a query.

The <tt>ExpressionBuilder</tt> acts as a substitute for the objects that you query. To construct a query, call methods on the <tt>ExpressionBuilder</tt> that correspond to the attributes of the objects. We recommend that you name <tt>ExpressionBuilder</tt> objects according to the type of objects against which you do a query.

* [[#How to Make a User-Defined Function Available to a Specific Platform]]

+

* [[#How to Make a User-Defined Function Available to a Specific Platform|How to Make a User-Defined Function Available to a Specific Platform]]

−

* [[#How to Make a User-Defined Function Available to All Platforms]]

+

* [[#How to Make a User-Defined Function Available to All Platforms|How to Make a User-Defined Function Available to All Platforms]]

−

+

Line 782:

Line 759:

To make the function that overrides a specific operation on your own platform, use the following procedure:

To make the function that overrides a specific operation on your own platform, use the following procedure:

<ol>

<ol>

−

<li> Create a subclass of the desired <tt>DatabasePlatform</tt> (from <tt>oracle.toplink.platform.database</tt> or <tt>oracle.toplink.platform.database.oracle</tt> package) that provides a public method that calls the protected superclass method <tt>addOperator</tt><nowiki>:</nowiki>

+

<li> Create a subclass of the desired <tt>org.eclipse.persistence.platform.database.DatabasePlatform</tt> that provides a public method that calls the protected superclass method <tt>addOperator</tt><nowiki>:</nowiki>

Regardless of whether you added the function for all platforms or for a specific platform, [[#Example 106-30|Accessing a User-Defined Function]] illustrates how to use the <tt>Expression</tt> method <tt>getFunction</tt> to access the user-defined expression operator represented by a constant with the value 600.

+

Regardless of whether you added the function for all platforms or for a specific platform, the following example illustrates how to use the <tt>Expression</tt> method <tt>getFunction</tt> to access the user-defined expression operator represented by a constant with the value 600.

This division of functionality enables EclipseLink expressions to provide similar mathematical functionality to the java.lang.Math class, but keeps both the Expression and ExpressionMath classes from becoming unnecessarily complex.

Expressions Compared to SQL

Expressions offer the following advantages over SQL when you access a database:

Expressions are easier to maintain because the database is abstracted.

Changes to descriptors or database tables do not affect the querying structures in the application.

Expressions enhance readability by standardizing the Query interface so that it looks similar to traditional Java calling conventions. For example, the Java code required to get the street name from the Address object of the Employee class looks like this:

emp.getAddress().getStreet().equals("Meadowlands");

The expression to get the same information is similar:

emp.get("address").get("street").equal("Meadowlands");

Expressions allow read queries to transparently query between two classes that share a relationship. If these classes are stored in multiple tables in the database, EclipseLink automatically generates the appropriate join statements to return information from both tables.

Expressions simplify complex operations. For example, the following Java code retrieves all employees that live on "Meadowlands" whose salary is greater than 10,000:

Boolean Logic

Expressions use standard boolean operators, such as AND, OR, and NOT, and you can combine multiple expressions to form more complex expressions. The Using Boolean Logic in an Expression example illustrates a code fragment that queries for projects managed by a selected person, and that have a budget greater than or equal to 1,000,000.

Database Functions and Operators

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

Note: 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 items using a SELECT clause, or with comparisons in a query's selection criteria using a WHERE clause. The following example illustrates a code fragment that matches several last names, including "SMART", "Smith", and "Smothers":

Using a Database Function Supported by the Expression API

emp.get("lastName").toUpperCase().like("SM%")

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, as this example shows:

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. This example illustrates using the abs and subtract methods.

Platform and User-Defined Functions

You can use the Expression method getFunction to access database functions that EclipseLink does not support directly. The following example illustrates how to access a database function named VacationCredit from within an expression, even though there is no support for such a function in the Expression API.

Using a Database Function Not Supported by the Expression API

emp.get("lastName").getFunction("VacationCredit").greaterThan(42)

This expression produces the following SQL:

SELECT . . . WHERE VacationCredit(EMP.LASTNAME) > 42

The Expression API includes additional forms of the getFunction method that allow you to specify arguments. For more information, see EclipseLink API Reference.

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.

What You May Need to Know 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:

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.

Use the expression API shown in this table to configure inner and outer join expressions.

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.

The following example illustrates an expression that returns employees who manage at least one employee (through a one-to-many relationship) with a salary less than $10,000.

Using an Expression with a One-to-Many Relationship

emp.anyOf("managedEmployees").get("salary").lessThan(10000);

The following example illustrates how to query across a many-to-many relationship using a similar strategy:

Note: Calling anyOf once would result in a different outcome than if you call it twice. For example, if you query for an employee with a telephone area code of 613 and a number of 123-4599, you would use a single anyOf and a temporary variable. If you query for an employee, who has a telephone with an area code of 613, and who has a telephone with a number of 123-4599, you would call anyOf twice.

Parameterized Expressions

A relationship mapping differs from a regular query because it retrieves data for many different objects. To be able to specify these queries, supply arguments when you execute the query. Use the getParameter and getField methods to acquire values for the arguments.

A parameterized expression executes searches and comparisons based on variables instead of constants. This approach lets you build expressions that retrieve context-sensitive information. This technique is useful when you define EJB finders (see EJB Finders).

Parameterized expressions require that the relationship mapping know how to retrieve an object or collection of objects based on its current context. For example, a one-to-one mapping from Employee to Address must query the database for an address based on foreign key information from the Employee table. Each mapping contains a query that EclipseLink constructs automatically based on the information provided in the mapping. To specify expressions yourself, use the mapping customization mechanisms.

You can use parameterized expressions to create reusable queries (see Named Queries).

Expression Method getParameter

The Expression method getParameter returns an expression that becomes a parameter in the query. This lets you create a query that includes user input in the search criteria. The parameter must be either the fully qualified name of the field from a descriptor's row, or a generic name for the argument.

Parameters you construct this way are global to the current query, so you can send this message to any expression object.

The following example illustrates how to use a custom query to find an employee by first name.

The following example illustrates how to obtain a simple one-to-many mapping from class PolicyHolder to Policy using a nondefault selection criteria. The SSN field of the POLICY table is a foreign key to the SSN field of the HOLDER table.

Expression Method getField

The Expression method getField returns an expression that represents a database field with the given name. Use this method to construct the selection criteria for a mapping. The argument is the fully qualified name of the required field. Because fields are not global to the current query, you must send this method to an expression that represents the table from which this field is derived. See also Data Queries and Expressions.

The following example illustrates how to use the Expression method getField.

To access one-to-many and many-to-many query keys that define a distinct join across a collection relationship, use Expression method anyOf.

Multiple Expressions

Expressions support subqueries (SQL subselects) and parallel selects. To create a subquery, use a single expression builder. With parallel selects, use multiple expression builders when you define a single query. This lets you specify joins for unrelated objects at the object level.

How to Use Subselects and Subqueries

Subqueries lets you define complex expressions that query on aggregated values (counts, min, max) and unrelated objects (exists, in, comparisons). To obtain a subquery, pass an instance of a report query to any expression comparison operation, or use the subQuery operation on an expression builder. The subquery is not required to have the same reference class as the parent query, and it must use its own expression builder.

You can nest subqueries, or use them in parallel. Subqueries can also make use of custom SQL.

For expression comparison operations that accept a single value (equal, greaterThan, lessThan), the subquery result must return a single value. For expression comparison operations that accept a set of values (in, exists), the subquery result must return a set of values.

The following example illustrates how to create an expression that matches all employees with more than five managed employees.

How to Use Parallel Expressions

Parallel expressions enable you to compare unrelated objects. Parallel expressions require multiple expression builders, but do not require the use of report queries. Each expression must have its own expression builder, and you must use the constructor for expression builder that takes a class as an argument. The class does not have to be the same for the parallel expressions, and you can create multiple parallel expressions in a single query.

Only one of the expression builders is considered the primary expression builder for the query. This primary builder makes use of the zero argument expression constructor, and EclipseLink obtains its class from the query.

The following example illustrates how to create an expression that matches all employees with the same last name as another employee of different gender, and accounts for the possibility that returned results could be a spouse.

Data Queries and Expressions

You can use expressions to retrieve data rather than objects. This is a common approach when you work with unmapped information in the database, such as foreign keys and version fields.

Expressions that query for objects generally refer to object attributes, which may in turn refer to other objects. Data expressions refer to tables and their fields. You can combine data expressions and object expressions within a single query. EclipseLink provides two main methods for expressions that query for data: getField and getTable.

How to Use the getField Method

The getField method lets you retrieve data from either an unmapped table or an unmapped field from an object. In either case, the field must be part of a table represented by that object's class; otherwise, EclipseLink raises an exception when you execute the query.

You can also use the getField method to retrieve the foreign key information for an object.

This example illustrates how to use the data expression method (operator) getField with an object.

Using getField with an Object

builder.getField("[FIELD_NAME]").greaterThan("[ARGUMENT]");

How to Use the getTable Method

The getTable method returns an expression that represents an unmapped table in the database. This expression provides a context from which to retrieve an unmapped field when you use the getField method.

The following example illustrates how to combine both getField and getTable in the same expression.

A common use for the getTable and getField methods is to retrieve information from a link table (or reference table) that supports a many-to-many relationship.

The following example reads a many-to-many relationship that uses a link table and also checks an additional field in the link table. This code combines an object query with a data query, using the employee's manager as the basis for the data query. It also features parameterization for the project ID.

Select the attribute, specify if the query allows a null value, and click OK.Use the Allows Null and Allows None options to define an expression with an outer join.Check the Allows Null option to use the ExpressionBuilder method getAllowingNull.Check the Allows None option to use the ExpressionBuilder method anyOfAllowingNone. For more information, see Using EclipseLink Expression API for Joins.

Use the Operator list to specify how EclipseLink should evaluate the expression.

For the Second Argument, select Literal, Query Key, or Parameter:

For Literal arguments, choose the literal type (such as String or Integer) and enter the literal value.

How to Create an Expression Using Java

To create an expression in Java code, use the Expression class or ExpressionBuilder method get.

The ExpressionBuilder acts as a substitute for the objects that you query. To construct a query, call methods on the ExpressionBuilder that correspond to the attributes of the objects. We recommend that you name ExpressionBuilder objects according to the type of objects against which you do a query.

Note: An instance of ExpressionBuilder is specific to a particular query. Do not attempt to build another query using an existing builder, because it still contains information related to the first query.

This example illustrates how to use the query key lastName to reference the field name L_NAME.

Creating and Using a User-Defined Function

Different databases sometimes implement the same functions in different ways. For example, an argument that specifies that data returns in ascending order might be ASC or ASCENDING. To manage differences, EclipseLink recognizes functions and other operators that vary according to the relational database.

Although most platform-specific operators exist in EclipseLink, if necessary, you can create your own operators.

To create a user-defined function, use the ExpressionOperator class.

An ExpressionOperator has a selector and a Vector of strings:

The selector is the identifier (id) by which users refer to the function.

The strings are the constant strings used in printing the function. When printed, the strings alternate with the function arguments.

You can also specify whether the operator is prefix or postfix. In a prefix operator, the first constant string prints before the first argument; in a postfix, it prints afterwards.

Where you create a user-defined function and how you add it to the EclipseLink expression framework depends on whether you want the new function available to all database platforms or to only a specific database platform.

Note: Represent the number in the setSelector method by a constant value. Ensure that this number is greater than 500 (numbers below 500 are reserved in EclipseLink).

Using a User-Defined Function

Regardless of whether you added the function for all platforms or for a specific platform, the following example illustrates how to use the Expression method getFunction to access the user-defined expression operator represented by a constant with the value 600.