9.6. Query Language Extensions

9.6.1. Filter Extensions
9.6.1.1. Included Filter Extensions
9.6.1.2. Developing Custom Filter Extensions
9.6.1.3. Configuring Filter Extensions
9.6.2. Aggregate Extensions
9.6.2.1. Configuring Query Aggregates
9.6.3. JDOQL Non-Distinct Results
9.6.4. JDOQL Subqueries
9.6.4.1. Subquery Parameters, Variables, and Imports
9.6.5. MethodQL

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]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.4, “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 OpenJPAQuery.addFilterListener and KodoQuery.addFilterListener methods. You might use per-query registration for very specific extensions that do not apply globally. See the the org.apache.openjpa.persistence.OpenJPAQuery 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.4, “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 OpenJPAQuery.addAggregateListener and KodoJPAQuery.addAggregateListener methods. You might use per-query registration for very specific aggregates that do not apply globally. See the the org.apache.openjpa.persistence.OpenJPAQuery 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.

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:

JPA:

import org.apache.openjpa.persistence.*;

...

// the method query language is 'openjpa.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
OpenJPAEntityManager oem = OpenJPAPersistence.cast (emf);
OpenJPAQuery q = oem.createQuery ("openjpa.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 'openjpa.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 ("openjpa.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(StoreContextctx, 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.

 

Skip navigation bar   Back to Top