9.6. Query Language Extensions
Prev | Chapter 9. Runtime Extensions | Next

## 9.6. Query Language Extensions

JPQL and JDOQL are powerful, easy-to-use query languages, but you may occasionally find them limiting in some way. To circumvent the limitations of JDOQL and JPQL, Kodo provides extensions to these languages, and allows you extend them as well.

| Warning
The JPQL parser in this release does not yet allow extensions. They will be made available to JPQL users in a future release.

### 9.6.1. Filter Extensions

Filter extensions are custom methods that you can use in your query filter, having, ordering, and result strings. Kodo provides some built-in filter extensions, and you can develop your own custom extensions as needed. You can optionally preface all filter extensions with `ext:` in your query string. For example, the following example uses a hypothetical `firstThreeChars` extension to search for cities whose name begins with the 3 characters 'H', 'a', 'r'.

Example 9.9. Basic Filter Extension

JPA:

    Query q = em.createQuery ("select c from City c where c.name.ext:firstThreeChars () = 'Har'");
    List results = q.getResultList ();

JDO:

    Query q = pm.newQuery (City.class, "name.ext:firstThreeChars () == 'Har'");
    List results = (List) 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 another hypothetical extension, `equalsIgnoreCase`:

Example 9.10. Chaining Filter Extensions

JPA:

    Query query = em.createQuery ("select c from City c where "
        + "c.name.ext:firstThreeChars ().ext:equalsIgnoreCase ('Har')");
    List results = q.getResultList ();

JDO:

    Query q = pm.newQuery (City.class, "name.ext:firstThreeChars ().ext:equalsIgnoreCase ('Har')");
    List results = (List) q.execute ();

Finally, when using filter extensions you must be aware that any SQL-specific extensions can only execute against the database, and cannot be used for in-memory queries (recall that Kodo executes queries in-memory when you supply a candidate collection rather than a class, or when you set the `IgnoreChanges` and `FlushBeforeQueries` properties to `false` and you execute a query within a transaction in which you've modified data that may affect the results).

#### 9.6.1.1. Included Filter Extensions

Kodo includes two default filter extensions to enhance the power of your queries.

- `getColumn`: Places the proper alias for the given column name into the SELECT statement that is issued. This extension cannot be used for in-memory queries. When traversing relations, the column is assumed to be in the primary table of the related type.

  JPQL:

      select e from Employee e where e.company.address.ext:getColumn ('ID') = 5

  To get a column of the candidate class in JDO, use `this` as the extension target, as shown in the second JDOQL example below.

  JDOQL:

      company.address.ext:getColumn ('ID') == 5
      this.ext:getColumn ('LEGACY_DATA') == 'foo'

- `sql`: Embeds the given SQL argument into the SELECT statement. This extension cannot be used for in-memory queries.

  JPQL:

      select p from Product p where p.price < ext:sql ('(SELECT AVG(PRICE) FROM PRODUCTS)')

  JDOQL:

      price < ext:sql ('(SELECT AVG(PRICE) FROM PRODUCTS)')

#### 9.6.1.2. Developing Custom Filter Extensions

You can write your own extensions by implementing the `kodo.jdbc.kernel.exps.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 `src/kodo/kernel/exps` and `src/kodo/jdbc/kernel/exps` directories of your distribution.

#### 9.6.1.3. Configuring Filter Extensions

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

- Registration by properties: You can register custom filter extensions by setting the `kodo.FilterListeners` configuration property to a comma-separated list of plugin strings (see Section 2.5, “Plugin Configuration”) describing your extensions classes. Extensions registered in this fashion must have a public no-arg constructor. They must also be thread safe, because they will be shared across all queries.

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

  See the the `kodo.persistence.KodoQuery` and `kodo.jdo.KodoQuery` Javadoc for details.

### 9.6.2. Aggregate Extensions

Just as you can write your own filter methods, you can write your own query aggregates by implementing the `kodo.jdbc.kernel.exps.JDBCAggregateListener` interface. View the Javadoc documentation for details. When using your custom aggregates in result or having query clauses, you can optionally prefix the function name with `ext:` to identify it as an extension.

#### 9.6.2.1. Configuring Query Aggregates

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

- Registration by properties: You can register custom query aggregates by setting the `kodo.AggregateListeners` configuration property to a comma-separated list of plugin strings (see Section 2.5, “Plugin Configuration”) describing your aggregate implementation. Aggregates registered in this fashion must have a public no-arg constructor. They must also be thread safe, because they will be shared across all queries.

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

  See the the `kodo.persistence.KodoQuery` and `kodo.jdo.KodoQuery` Javadoc for details.

### 9.6.3. JDOQL Non-Distinct Results

The JDO specification mandates that an implementation must always filter out duplicate query results caused by the semantics of the underlying datastore. When querying a relational database, for example, Kodo must often issue `DISTINCT` SQL queries or even subselects to avoid inlcuding repeated data in your results.

Kodo allows you to explicitly request unfiltered results with the `nondistinct` keyword. If used, `nondistinct` must be the first word of your query's result clause. Like other JDOQL keywords, `nondistinct` can be expressed in either all-lower or all-upper case. Chapter 11, Query covers the standard JDOQL language.

Example 9.11. Nondistinct JDOQL

    Query q = pm.newQuery (Magazine.class, "select nondistinct this "
        + "where articles.contains (art) && art.author.lastName == 'Smith'");

### 9.6.4. JDOQL Subqueries

The JDO specification does not provide a simple way to embed one query within another. The best you can do is perform the "inner" query, and use its result(s) as a parameter to the "outer" query. Kodo JDO corrects this oversight with full support for JDOQL subqueries.

| Important
This feature requires Kodo Enterprise Edition.

Kodo's JDOQL subqueries utilize the single-string JDOQL syntax, explained in Section 11.9, “Single-String JDOQL” of the JDO Overview. Kodo makes one minor addition to the single-string format, requiring a logical alias for the subquery candidates. As you'll see below, this allows you to create correlated subqueries that include values from the parent query. The results of a subquery are supplied to the parent query as either a single value or a collection, depending on usage. Let's examine some examples of subqueries in action.

Example 9.12. Comparison to Subquery

In this example, we find all magazines that are tied for the highest cover price.

    Query q = pm.newQuery (Magazine.class, "price == (select max(m.price) "
        + "from org.mag.Magazine m)");
    List mags = (List) q.execute ();

Notice that the example surrounds its subquery in parentheses. This is required for all subqueries. Also, note the use of the `m` alias for subquery candidates. The candidate alias is also required. Without it, Kodo couldn't support correlated subqueries, like the one in the next example.

Example 9.13. Correlated Subquery

In this example, we find all the magazines tied for the highest price within their publisher. The subquery is correlated because it uses the `publisher` value of the parent query's candidate instance.

    Query q = pm.newQuery (Magazine.class, "price == (select max(m.price) "
        + "from org.mag.Magazine m where m.publisher == publisher)");
    List mags = (List) q.execute ();

The previous example used subqueries that were guaranteed to return exactly one result. What if the subquery might return no results, or multiple results? In these cases, you should treat the subquery as a collection. Use `contains` to test whether a value is included in the subquery results, and `isEmpty` to test whether the subquery has no results.

Example 9.14. Subquery Contains

Find all magazines whose title matches the title of an article.

    Query q = pm.newQuery (Magazine.class,
        "(select a.title from org.mag.Article a).contains (title)");

Filtering on whether a subquery contains a value is the same as using the `IN` operator in SQL.

Example 9.15. Subquery Empty

Find all magazines whose title does not match the title of an article.

    Query q = pm.newQuery (Magazine.class,
        "(select from org.mag.Article a where a.title == title).isEmpty ()");

Testing whether a subquery is empty is equivalent to SQL's `NOT EXISTS` assertion. To perform an `EXISTS` test instead, just negate the expression: `!(<subquery>).isEmpty ()`

#### 9.6.4.1. Subquery Parameters, Variables, and Imports

Subqueries cannot declare their own parameters, variables, or imports. The outermost query must always specify all declarations for any subqueries it has, including nested subqueries. Subqueries can, however, introduce new implicit variables and parameters just by using them. For a refresher on JDOQL declarations, including implicit parameters and variables, see Section 11.3, “Advanced Object Filtering” of the JDO Overview.

### 9.6.5. MethodQL

If JPQL / JDOQL and SQL queries do not match your needs, Kodo also allows you to name a Java method to use to load a set of objects. In a MethodQL query, the query string names a static method to invoke to determine the matching objects:

| Important
This feature requires Kodo Enterprise Edition.

JPA:

    import kodo.persistence.*;

    ...

    // the method query language is 'kodo.MethodQL'.
    // set the query string to the method to execute, including full class name; if
    // the class is in the candidate class' package or in the query imports, you
    // can omit the package; if the method is in the candidate class, you can omit
    // the class name and just specify the method name
    KodoEntityManager kem = KodoPersistence.cast (emf);
    KodoQuery q = kem.createQuery ("kodo.MethodQL", "com.xyz.Finder.getByName");

    // set the type of objects that the method returns
    q.setResultClass (Person.class);

    // parameters are passed the same way as in standard queries
    q.setParameter ("firstName", "Fred").setParameter ("lastName", "Lucas");

    // this executes your method to get the results
    List results = q.getResultList ();

JDO:

    // the method query language is 'kodo.MethodQL'.
    // set the filter to the method to execute, including full class name; if
    // the class is in the candidate class' package or in the query imports, you
    // can omit the package; if the method is in the candidate class, you can omit
    // the class name and just specify the method name
    Query q = pm.newQuery ("kodo.MethodQL", "com.xyz.Finder.getByName");

    // set the type of objects that the method returns
    q.setClass (Person.class);

    // parameters are declared and passed the same way as in standard queries
    q.declareParameters ("String firstName, String lastName");

    // this executes your method to get the results
    List results = (List) q.execute ("Fred", "Lucas");

For datastore queries, the method must have the following signature:

    public static ResultObjectProvider xxx(StoreContext ctx,
        ClassMetaData meta, boolean subclasses, Map params, FetchConfiguration fetch)

The returned result object provider should produce objects of the candidate class that match the method's search criteria. If the returned objects do not have all fields in the given fetch configuration loaded, Kodo will make additional trips to the datastore as necessary to fill in the data for the missing fields.

In-memory execution is slightly different, taking in one object at a time and returning a boolean on whether the object matches the query:

    public static boolean xxx(StoreContext ctx, ClassMetaData meta,
        boolean subclasses, Object obj, Map params, FetchConfiguration fetch)

In both method versions, the given `params` map contains the names and values of all the parameters for the query.

The `StoredProcQueries` class and `StoredProcMain` driver program in Section 1.3.5, “Custom Mappings” demonstrate how you might implement your own custom query method, and how to execute it through the JDO `Query` interface.

Prev | Up | Next
9.5. Savepoints | Home | 9.7. Generators