Expression Components

Boolean Logic

Expressions use standard boolean operators, such as AND, OR, and NOT, and you can combine multiple expressions to form more complex expressions. Example 95-3 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.

ExpressionBuilder project = new ExpressionBuilder();
Expression hasRightLeader, bigBudget, complex;
Employee selectedEmp = someWindow.getSelectedEmployee();
hasRightLeader = project.get("teamLeader").equal(selectedEmp);
bigBudget = project.get("budget").greaterThanEqual(1000000);
complex = hasRightLeader.and(bigBudget);
Vector projects = session.readAllObjects(Project.class, complex);

Database Functions and Operators

Functions

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

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 Example 95-5 shows:

readAllQuery.addOrderBy(expBuilder.get("address").get("city").ascending())

You can use aggregate functions, such as average, minimum, maximum, sum and so forth, with ReportQuery (see "Report Query").

Operators

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

• like

• notLike

• equal

• notEqual

• lessThan

• lessThanEqual

• equalsIgnoreCase

• greaterThan

• greaterThanEqual

• in

• notIn

• between

• notBetween

Example 95-4 demonstrates the use of the like operator.

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. Example 95-6 illustrates using the abs and subtract methods.

ExpressionMath.abs(ExpressionMath.subtract(emp.get("salary"),emp.get("spouse")
  .get("salary")).greaterThan(10000)

XMLType Functions

You can use the following operators when constructing queries against data mapped to an 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.

Example 95-7 illustrates how to use the extract operator in a query:

Expression criteria = builder.get("resume").extract("//education/degree/text()").getStringVal().equal("BCS");
Vector employees = session.readAllObject(Employee.class, criteria); 

Platform and User-Defined Functions

You can use the Expression method getFunction to access database functions that TopLink does not support directly. Example 95-8 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.

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 Oracle TopLink API Reference.

You can also access a custom function that you create. For more information on creating a custom function in TopLink, see "Creating and Using a User-Defined Function".

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 a SQL join that returns a single row.

Example 95-9 illustrates a code fragment that accesses fields from an employee's address.

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

Example 95-9 corresponds to joining the EMPLOYEE table to the ADDRESS table, based on the address foreign key, and checking for the country name.

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

project.get("teamLeader").get("manager").get("manager").get("address").get("street")

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:

• Understanding Joins

• Using TopLink Expression API For Joins

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

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

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

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

SELECT DISTINCT . . . FROM EMP t1, EMP t2 WHERE
t2.MANAGER_ID = t1.EMP_ID AND t2.SALARY < 10000