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:

Making a User-Defined Function Available to a Specific Platform
Making a User-Defined Function Available to All Platforms

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

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

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

Making a User-Defined Function Available to All Platforms

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

Example 95-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).

Using a User-Defined Function

Regardless of whether you added the function for all platforms or for a specific platform, Example 95-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 95-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);