Skip Headers

# Database Functions and Operators

Previous

Next

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: Using a Database Function Supported by the Expression API 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 Example: Using an Ordering Database Function shows:

Using an Ordering Database Function

    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: Using a Database Function Supported by the Expression API demonstrates the use of the like operator.