110 Introduction to TopLink Expressions

Using the TopLink expressions framework, you can specify query search criteria based on your domain object model.

This chapter includes the following sections:

• Expression Framework

• Expression Components

• Parameterized Expressions

• Query Keys and Expressions

• Multiple Expressions

• Data Queries and Expressions

• Creating an Expression

• Creating and Using a User-Defined Function

110.1 Expression Framework

The TopLink expression framework provides methods through the following classes:

• The Expression class provides most general functions, such as toUpperCase.

• The ExpressionMath class supplies mathematical methods.

Example 110-1 illustrates how to use the Expression class.

Example 110-1 Using the Expression Class

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

Example 110-2 illustrates how to use the ExpressionMath class.

Example 110-2 Using the ExpressionMath Class

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

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

110.1.1 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, TopLink 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:

  ExpressionBuilder emp = new ExpressionBuilder();
  Expression exp = emp.get("address").get("street").equal("Meadowlands");
  Vector employees = session.readAllObjects(Employee.class,
    exp.and(emp.get("salary").greaterThan(10000)));
  

  TopLink automatically generates the appropriate SQL from that code:

  SELECT t0.VERSION, t0.ADDR_ID, t0.F_NAME, t0.EMP_ID, t0.L_NAME, t0.MANAGER_ID, t0.END_DATE, t0.START_DATE, t0.GENDER, t0.START_TIME, t0.END_TIME,t0.SALARY FROM EMPLOYEE t0, ADDRESS t1 WHERE (((t1.STREET = 'Meadowlands')AND (t0.SALARY > 10000)) AND (t1.ADDRESS_ID = t0.ADDR_ID))
  

110.2 Expression Components

A simple expression usually consists of the following three parts:

1. The attribute, which represents a mapped attribute or query key of the persistent class

2. The operator, which is an expression method that implements boolean logic, such as GreaterThan, Equal, or Like

3. The constant or comparison, which refers to the value used to select the object

In the following code fragment:

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

• The attribute is lastName.

• The operator is equal.

• The constant is the string "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

• Database Functions and Operators

• Mathematical Functions

• XMLType Functions

• Platform and User-Defined Functions

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

• Expressions for Joining and Complex Relationships

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

Example 110-3 Using Boolean Logic in an Expression

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);

110.2.2 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

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. Example 110-4 illustrates a code fragment that matches several last names, including "SMART", "Smith", and "Smothers":

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

Example 110-5 Using an Ordering Database Function

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

Note:

Ordering is not supported for in-memory queries (see Section 108.16.2, "How to Use In-Memory Queries").

You can use aggregate functions, such as average, minimum, maximum, sum and so forth, with the ReportQuery (see Section 108.7.5, "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 110-4 demonstrates the use of the like operator.

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

Example 110-6 Using Mathematical Functions in an Expression

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

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

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

Example 110-7 Using the XMLType Extract Operator

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

110.2.5 Platform and User-Defined Functions

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

Example 110-8 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 Oracle Fusion Middleware Java API Reference for Oracle TopLink.

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

110.2.6 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 110-9 illustrates a code fragment that accesses fields from an employee's address.

Example 110-9 Using an Expression with a One-to-One Relationship

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

Example 110-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")

110.2.7 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:

• What You May Need to Know About Joins

• Using TopLink Expression API for Joins

110.2.7.1 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, TopLink 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. TopLink supports outer joins for most databases.

110.2.7.2 Using TopLink Expression API for Joins

You can use joins anywhere expressions are used, including: selection-criteria, ordering (see Section 109.2.1.5, "Specifying Read Ordering"), report queries (see Section 108.7.5, "Report Query"), partial objects (see Section 108.7.1.3, "Partial Object Queries"), one-to-one relational mappings (see Section 28.8, "Configuring Joining at the Mapping Level"), and join reading (see Section 108.7.1.5, "Join Reading and Object-Level Read Queries").

Use the expression API shown in Table 110-1 to configure inner and outer join expressions.

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

Example 110-10 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.

Example 110-10 Using an Expression with a One-to-Many Relationship

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

Example 110-11 illustrates how to query across a many-to-many relationship using a similar strategy:

Example 110-11 Using an Expression with a Many-to-Many Relationship

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

TopLink translates these queries into SQL that joins the relevant tables using a DISTINCT clause to remove duplicates. TopLink translates Example 110-10 into the following SQL:

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

You can use one-to-one and one-to-many join expressions in an ObjectLevelReadyQuery to configure joins on a per-query basis (see Section 108.7.1.5, "Join Reading and Object-Level Read Queries").

You can also configure joins at the mapping level (see Section 28.8, "Configuring Joining at the Mapping Level").

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.

110.3 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 Section 108.15, "EJB 2.n CMP 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 TopLink 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 Section 108.8, "Named Queries").

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

Example 110-12 illustrates how to use a custom query to find an employee by first name.

Example 110-12 Using a Parameterized Expression in a Custom Query

Expression firstNameExpression;

ReadObjectQuery query = new ReadObjectQuery(Employee.class);
ExpressionBuilder emp = query.getExpressionBuilder();
firstNameExpression = emp.get("firstName").equal(emp.getParameter("firstName"));
query.setSelectionCriteria(firstNameExpression);
query.addArgument("firstName");
Vector v = new Vector();
v.addElement("Sarah");
Employee e = (Employee) session.executeQuery(query, v);

Example 110-13 illustrates how to use a custom query to find all employees that live in the same city as a given employee.

Example 110-13 Using Nested Parameterized Expressions

Expression addressExpression;
ReadObjectQuery query = new ReadObjectQuery(Employee.class);
ExpressionBuilder emp = query.getExpressionBuilder();
addressExpression = 
    emp.get("address").get("city").equal(
    emp.getParameter("employee").get("address").get("city"));
query.setName("findByCity");
query.setSelectionCriteria(addressExpression);
query.addArgument("employee");
Vector v = new Vector();
v.addElement(employee);
Employee e = (Employee) session.executeQuery(query, v);

Example 110-14 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.

Example 110-14 Using a Parameterized Expression in a Mapping

OneToManyMapping mapping = new OneToManyMapping();
mapping.setAttributeName("policies");
mapping.setGetMethodName("getPolicies");
mapping.setSetMethodName("setPolicies");
mapping.setReferenceClass(Policy.class);

// Build a custom expression here rather than using the defaults
ExpressionBuilder policy = new ExpressionBuilder();
mapping.setSelectionCriteria(policy.getField("POLICY.SSN")).equal(policy.
  getParameter("HOLDER.SSN")));

110.3.2 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 Section 110.6, "Data Queries and Expressions".

Example 110-15 illustrates how to use the Expression method getField.

Example 110-15 Using Expression Method getParameter

ExpressionBuilder address = new ExpressionBuilder();
Expression exp = address.getField("ADDRESS.EMP_ID").equal(
    address.getParameter("EMPLOYEE.EMP_ID")
);
exp = exp.and(address.getField("ADDRESS.TYPE").equal(null));

110.4 Query Keys and Expressions

A query key is a schema-independent alias for a database field name.

Query keys are supported in relational database projects only.

Query keys are generated automatically for all direct and relationship mappings. The name of the query key is the class attribute name.

For more information on how query keys are created and modified, see Section 119.10, "Configuring Query Keys".

Example 110-16 illustrates how to use the query key firstName for the corresponding directly mapped Employee attribute.

Example 110-16 Using an Automatically Generated Query Key in an Expression

Vector employees = session.readAllObjects(Employee.class,
  new ExpressionBuilder().get("firstName").equal("Bob"));

Example 110-17 illustrates how to use a one-to-one query key within the TopLink expression framework.

Example 110-17 Using a One-to-One Query Key in an Expression

ExpressionBuilder employee = new ExpressionBuilder();
Vector employees = session.readAllObjects(Employee.class,
  employee.get("address").get("city").equal("Ottawa"));

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

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

110.5.1 How to Use Subselects and Subqueries

Some queries compare the results of other, contained queries (or subqueries). SQL supports this comparison through subselects. TopLink expressions provide subqueries to support subselects.

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.

Example 110-18 illustrates how to create an expression that matches all employees with more than five managed employees.

Example 110-18 A Subquery Expression Using a Comparison and Count Operation

ExpressionBuilder emp = new ExpressionBuilder();
ExpressionBuilder managedEmp = new ExpressionBuilder();
ReportQuery subQuery = new ReportQuery(Employee.class, managedEmp);
subQuery.addCount();
subQuery.setSelectionCriteria(managedEmp.get("manager") .equal(emp));
Expression exp = emp.subQuery(subQuery).greaterThan(5);

Example 110-19 illustrates how to create an expression that matches the employee with the highest salary in the city of Ottawa.

Example 110-19 A Subquery Expression Using a Comparison and Max Operation

ExpressionBuilder emp = new ExpressionBuilder();
ExpressionBuilder ottawaEmp = new ExpressionBuilder();
ReportQuery subQuery = new ReportQuery(Employee.class, ottawaEmp);
subQuery.addMax("salary");
subQuery.setSelectionCriteria(ottawaEmp.get("address").get("city").equal("Ottawa"));
Expression exp =   emp.get("salary").equal(subQuery).and(emp.get("address").get("city").equal("Ottawa"));

Example 110-20 illustrates how to create an expression that matches all employees that have no projects.

Example 110-20 A Subquery Expression Using a Not Exists Operation

ExpressionBuilder emp = new ExpressionBuilder();
ExpressionBuilder proj = new ExpressionBuilder();
ReportQuery subQuery = new ReportQuery(Project.class, proj);
subQuery.addAttribute("id");
subQuery.setSelectionCriteria(proj.equal(emp.anyOf("projects"));
Expression exp = emp.notExists(subQuery);

110.5.2 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 TopLink obtains its class from the query.

Example 110-21 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.

Example 110-21 A Parallel Expression on Two Independent Employees

ExpressionBuilder emp = new ExpressionBuilder();
ExpressionBuilder spouse = new ExpressionBuilder(Employee.class);
Expression exp = emp.get("lastName").equal(spouse.get("lastName"))
  .and(emp.get("gender").notEqual(spouse.get("gender"));

110.6 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. TopLink provides two main methods for expressions that query for data: getField and getTable.

110.6.1 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, TopLink raises an exception when you execute the query.

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

Example 110-22 illustrates how to use the data expression method (operator) getField with an object.

Example 110-22 Using getField with an Object

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

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

Example 110-23 illustrates how to combine both getField and getTable in the same expression.

Example 110-23 Using getTable and getField Together

builder.getTable("[TABLE_NAME]").getField("[FIELD_NAME]").equal("[ARGUMENT]");

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

Example 110-24 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.

Example 110-24 Using a Data Query Against a Link Table

ExpressionBuilder emp = new ExpressionBuilder();
Expression manager = emp.get("manager"); 
Expression linkTable = manager.getTable("PROJ_EMP");
Expression empToLink = emp.getField("EMPLOYEE   .EMP_ID").equal(linkTable.getField("PROJ_EMP.EMP_ID");
Expression projToLink = linkTable.getField("PROJ_EMP
  .PROJ_ID").equal(emp.getParameter("PROJECT.PROJ_ID"));
Expression extra = linkTable.getField("PROJ_EMP.TYPE").equal("W");
query.setSelectionCriteria((empToLink.and(projToLink)).and(extra));

110.7 Creating an Expression

You can create an expression using Oracle JDeveloper, TopLink Workbench, or Java.

Use either Oracle JDeveloper or TopLink Workbench for creating basic expressions for use in named queries (see Section 110.7.1, "How to Create an Expression Using TopLink Workbench").

Use Java code to create more complex expressions and to take full advantage of the features in the expressions API (see Section 110.7.2, "How to Create an Expression Using Java").

110.7.1 How to Create an Expression Using TopLink Workbench

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

1. From the Named Queries Format tab, click Edit (or double-click a query string). The Expression Builder dialog box appears.

   See Section 108.8, "Named Queries" for more information.

   Figure 110-1 Expression Builder Dialog Box

   
   Description of "Figure 110-1 Expression Builder Dialog Box"
   
   

   Figure 110-1 numbered callouts identify the following user-interface components:

   1. Expression tree

   2. Arguments

2. Click Add or Add Nested to create a new expression. TopLink assigns a sequence number to each node and nested node.

   Click Remove to remove an existing expression.

3. Select the node and use the Logical Operator list to specify the operator for the node (AND, OR, Not AND, or Not OR).

Use this table to complete the argument fields for each expression:

Field	Description
First Argument	Click Edit and select the query key for the first argument. The Choose Query Key dialog box appears. Continue with Section 110.7.1.1, "Adding Arguments".
Operator	Specify how TopLink should evaluate the expression. Valid operators include: Equal, Not Equal, Equal Ignore Case, Greater Than, Greater Than Equal, Less Than, Less Than Equal, Like, Not Like, Like Ignore Case, Is Null, and Not Null.
Second Argument	Specify the second argument: Literal–Select the Type and enter a literal value for Value. Query Key–Click Edit and select the query key. Parameter–Click Add to add a new parameter and then select from the list. Continue with Section 110.7.1.1, "Adding Arguments"

Click OK. TopLink Workbench adds the expression to the Named Queries tab.

110.7.1.1 Adding Arguments

Each expression contains elements (arguments) to evaluate. Expressions using the Is Null or Not Null operators require only a single argument.

To add new arguments, use this procedure:

1. Select an existing expression or click Add (or Add Nested) to add a new expression to the named query.

2. For the First Argument, click Edit. The Choose Query Key dialog box appears.

   Figure 110-2 Choose Query Key

   
   Description of "Figure 110-2 Choose Query Key"
   
   

3. 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 Section 110.2.7.2, "Using TopLink Expression API for Joins".

4. Use the Operator list to specify how TopLink should evaluate the expression.

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

   • For Query Key arguments, click Edit. The Choose Query Key dialog box appears (see step 3 and Figure 110-2).

   • For Parameter arguments, click Add to add a parameter and then use the list to select it.

Repeat this procedure for each expression or subexpression.

Example 110-25 Sample Expression

The following expression will find employees who:

• have a manager with the last name Jones or have no manager, and

• work on projects with the name Beta or project ID 4, and

• live in Canada and have a salary of more than 25,000, or

  live in the United States and have a salary of more than 37,500

AND
  1.manager(Allows Null).lastName EQUAL "Jones"
  2.OR
    2.1.projects.name LIKE "BETA"
    2.2.projects.id EQUAL "4"
  3.OR
    3.1.AND
      3.1.1.address.country EQUAL "Canada"
      3.1.2.salary GREATER THAN "25000"
    3.2.AND
      3.1.1.address.country EQUAL "United States"
      3.1.2.salary GREATER THAN "37500"

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

Example 110-26 illustrates how to use the query key lastName to reference the field name L_NAME.

Example 110-26 Using ExpressionBuilder to Build a Simple Expression

Expression expression = new ExpressionBuilder().get("lastName").equal("Young");

Example 110-27 illustrates how to create a complex expression by combining two smaller expressions with a logical and operator.

Example 110-27 Combining Two Expressions with a Logical AND Operator

ExpressionBuilder emp = new ExpressionBuilder();
    Expression exp1, exp2;
    exp1 = emp.get("firstName").equal("Ken");
    exp2 = emp.get("lastName").equal("Young");
    return exp1.and(exp2);

Example 110-28 illustrates how to create an expression using the notLike operator.

Example 110-28 Using Database Function notLike in an Expression

Expression expression = new ExpressionBuilder().get("lastName").notLike("%ung");

110.8 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, TopLink recognizes functions and other operators that vary according to the relational database.

Although most platform-specific operators exist in TopLink, 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 TopLink expression framework depends on whether you want the new function available to all database platforms or to only a specific database platform.

This section describes the following:

• How to Make a User-Defined Function Available to a Specific Platform

• How to Make a User-Defined Function Available to All Platforms

110.8.1 How to Make a User-Defined Function Available to a Specific Platform

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

1. Create a subclass of the desired DatabasePlatform (from oracle.toplink.platform.database or oracle.toplink.platform.database.oracle package) that provides a public method that calls the protected superclass method addOperator:

   
   ...
   public class MyDatabasePlatform extends DatabasePlatform {
       protected void initializePlatformOperators() {
           super.initializePlatformOperators();
           // Create user-defined function
           ExpressionOperator toUpper = new ExpressionOperator();
           toUpper.setSelector(ExpressionOperator.ToUpperCase);
           Vector v = new Vector();
           v.addElement("UPPERCASE(");
           v.addElement(")");
           toUpper.printAs(v);
           toUpper.bePrefix();
           toUpper.setNodeClass(FunctionExpression.class);
   
           // Make it available to this platform only
           addOperator(toUpper);
       }
   }
   

2. Configure your session to use your platform subclass (see Section 20.2, "Configuring Relational Database Platform at the Project Level" or Section 98.2, "Configuring a Relational Database Platform at the Session Level").

110.8.2 How to Make a User-Defined Function Available to All Platforms

To make the function available to all platforms, use ExpressionOperator method addOperator, as Example 110-29 shows.

Example 110-29 Adding a toUpper Function for All Platforms

ExpressionOperator toUpper = new ExpressionOperator();
toUpper.setSelector(600);
Vector v = new Vector();
v.addElement("NUPPER(");
v.addElement(")");
toUpper.printAs(v);
toUpper.bePrefix();
toUpper.setNodeClass(FunctionExpression.class);

ExpressionOperator.addOperator(toUpper);

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

110.8.2.1 Using a User-Defined Function

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

Example 110-30 Accessing a User-Defined Function

ReadObjectQuery query = new ReadObjectQuery(Employee.class);
ExpressionBuilder builder = query.getExpressionBuilder();
Expression functionExpression = builder.get("firstName").
    getFunction(600).equal("BOB");
query.setSelectionCriteria(functionExpression);
session.executeQuery(query);