Creating an Expression

You can create an expression using TopLink Workbench or Java code.

Use TopLink Workbench for creating basic expressions for use in named queries (see "Using TopLink Workbench").

Use Java code to create more complex expressions and to take full advantage of the features in the expressions API (see "Using Java").

Using TopLink Workbench

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

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

See "Named Queries" for more information.

Figure 97-1 Expression Builder

Description of "Figure 97-1 Expression Builder"

Figure 97-1 numbered callouts identify the following user-interface components:
1. Expression tree
2. Arguments
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.
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 "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 "Adding Arguments"

Field

Description

First Argument

Click Edit and select the query key for the first argument. The Choose Query Key dialog box appears.

Continue with "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 "Adding Arguments"

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

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:

Select an existing expression or click Add (or Add Nested) to add a new expression to the named query.
For the First Argument, click Edit. The Choose Query Key dialog box appears.

Figure 97-2 Choose Query Key
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 TopLink Expression API For Joins".
Use the Operator list to specify how TopLink 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.
- For Query Key arguments, click Edit. The Choose Query Key dialog box appears (see step 3 and Figure 97-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 97-24 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"

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 97-25 illustrates how to use the query key lastName to reference the field name L_NAME.

Example 97-25 Using ExpressionBuilder to Build a Simple Expression

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

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

Example 97-26 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 97-27 illustrates how to create an expression using the notLike operator.

Example 97-27 Using Database Function notLike in an Expression

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