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 All Platforms

• Making a User-Defined Function Available to a Specific Platform

• Using a User-Defined Function

Making a User-Defined Function Available to All Platforms

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

Example 97-28 Adding a toUpper Function for All Platforms

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

ExpressionOperator.addOperator(toUpper);

Making a User-Defined Function Available to a Specific Platform

To make the function available only to a specific 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:

   ...
   import oracle.toplink.platform.database.oracle.Oracle9Platform;
   
   public class MyOraclePlatform extends Oracle9Platform
   {
       public void addToUpperOperator()
       {
           // Create user-defined function
           ExpressionOperator toUpper = new ExpressionOperator();
           toUpper.setSelector();
           Vector v = new Vector();
           v.addElement("UPPER(");
           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 "Configuring Relational Database Platform at the Project Level" or "Configuring a Relational Database Platform at the Session Level").

3. Call the platform subclass method to add your operator:

   MyOraclePlatform platform = (MyOraclePlatform)session.getLogin().getPlatform();
   platform.addToUpperOperator();
   
   

Using a User-Defined Function

Regardless of whether you added the function for all platforms or for a specific platform, Example 97-29 illustrates how to use the Expression method getFunction to access the user-defined expression operator named toUpper.

Example 97-29 Accessing a User-Defined Function

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