10.7. Query Extensions
Prev | Chapter 10. JDO Runtime Extensions | Next

## 10.7. Query Extensions

JDOQL is a powerful, easy-to-use query language, but you may occasionally find it limiting in some way. To circumvent the limitations of JDOQL, Kodo provides alternatives and extensions to standard JDO queries. This section discusses JDOQL query extensions. See the previous chapter for a discussion of direct SQL queries and custom query execution.

### 10.7.1. JDOQL Extensions

JDOQL extensions are custom methods that you can use in your query filter string. Kodo JDO provides some built-in query extensions, and you can develop your own custom extensions as needed. You preface all JDOQL extensions with `ext:` in your JDOQL query. For example, the following example uses Kodo's built-in `endsWith` extension to search for cities whose name ends with "ford" (e.g. Hartford):

Example 10.1. Basic Query Extension

    Query q = pm.newQuery (City.class);
    q.setFilter ("name.ext:endsWith (\"ford\")");
    Collection c = (Collection) q.execute ();

Note that it is perfectly OK to chain together extensions. For example, let's modify our search above to be case-insensitive using Kodo's built-in `toLowerCase` extension:

Example 10.2. Chaining Query Extensions

    Query q = pm.newQuery (City.class);
    q.setFilter ("name.ext:toLowerCase ().ext:endsWith (\"ford\")");
    Collection c = (Collection) q.execute ();

Finally, when using query extensions you must be aware that some SQL-specific extensions can only execute against the database, and cannot be used for in-memory queries (recall that JDO executes queries in-memory when you supply a candidate collection rather than an extent/class, or when you set the `IgnoreCache` and `FlushBeforeQueries` properties to `false` and you execute a query within a transaction in which you've modified some persistent objects). In the list of built-in query extensions below, you can assume each extension can be executed both in-memory and against the database unless its description says otherwise.

#### 10.7.1.1. Included Query Extensions

Kodo includes several default query extensions to enhance the power of JDOQL. Some of these extensions are being considered for inclusion in the official JDO 2.0 specification, so they may become standard in the future.

- `containsKey`: Tests whether the target map contains the given argument in its key set. The argument may be a JDOQL variable, parameter, or constant.

      query.declareVariables ("Employee emp");
      query.setFilter ("employeeMap.ext:containsKey (emp) "
          + "&& emp.salary > 50000");

  | Note
  This query extension is available free of charge in all editions of Kodo JDO.

- `containsValue`: Tests whether the target map contains the given argument in its value collection. The argument may be a JDOQL variable, parameter, or constant.

      query.declareVariables ("Company comp");
      query.setFilter ("companyMap.ext:containsValue (comp) "
          + "&& comp.address.city == \"Houston\"");

  | Note
  This query extension is available free of charge in all editions of Kodo JDO.

- `toLowerCase`: Switches the target string to lower case. Equivalent to Java's `String.toLowerCase ()` method.

      query.setFilter ("stringField.ext:toLowerCase () == \"foo\"");

- `toUpperCase`: Switches the target string to upper case. Equivalent to Java's `String.toUpperCase ()` method.

      query.setFilter ("stringField.ext:toUpperCase () == \"FOO\"");

- `matches`: Tests if the target string matches the given regular expression. Equivalent to Java 1.4's `String.matches ()` method, though only a subset of the standard regular expression syntax is supported:

  - `.*`: Matches any 0 or more characters.

    `.`: Matches any 1 character.

    `(?i)`: Placing this token in the regular expression string means the expression will ignore case when matching.

  This extension replaces `stringContains` and `wildcardMatch`, which are now deprecated.

      query.setFilter ("stringField.ext:matches (\".*jdo.(?i)\")");

- `getColumn`: Places the proper alias for the given column name into the SELECT statement that is issued. This filter cannot be used for in-memory queries. When traversing relations, the column is assumed to be in the primary table of the related type. To get a column of the candidate class, use `this` as the extension target, as shown in the second example below.

      query.setFilter ("company.address.ext:getColumn (\"JDOIDX\") == 5");
      query.setFilter ("this.ext:getColumn (\"LEGACY_DATA\") == \"foo\"");

- `sqlVal`: Embeds the given SQL argument into the SELECT statement that is issued. This filter cannot be used for in-memory queries. Note that the given SQL should evaluate to some value, not a boolean expression. To embed SQL that evaluates to a boolean expression, use the `sqlExp` extension below.

      query.setFilter ("price < ext:sqlVal (\"(SELECT AVG(PRICE) FROM PRODUCTS)\")");

- `sqlExp`: Embeds the given SQL argument into the SELECT statement that is issued. This filter cannot be used for in-memory queries. Note that the given SQL should evaluate to a boolean expression, not some other value. To embed SQL that evaluates to a value, use the `sqlVal` extension above.

      query.setFilter ("ext:sqlExp (\"PRICE < (SELECT AVG(PRICE) FROM PRODUCTS)\")");

#### 10.7.1.2. Developing Custom Query Extensions

You can write your own extensions by implementing the `kodo.jdbc.query.JDBCFilterListener` interface. View the Javadoc documentation for details. Additionally, the source for all of Kodo's built-in query extensions is included in your Kodo download to get you started. The built-in extensions reside in the `kodo.query` and `kodo.jdbc.query` packages.

#### 10.7.1.3. Configuring JDOQL Extensions

There are two ways to register your custom query extensions with Kodo:

- Registration by properties: You can register custom query extensions by setting the `kodo.FilterListeners` configuration property to a comma-separated list of plugin strings describing your extensions classes. Extensions registered in this fashion must be able to be instantiated via reflection (they must have a public no-args constructor). They must also be thread safe, because they will be shared across all queries.

- Per-query registration: You can register query extensions for an individual query through the `KodoQuery.registerListener` method. You might use per-query registration for very specific extensions that do not apply globally.

Prev | Up | Next
10.6. KodoHelper | Home | 10.8. Query Aggregates and Projections