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 JDO Enterprise Edition allows you to extend queries with new operations. Kodo provides some built-in query extensions extensions, and you can develop your own custom extensions as needed.
First, to enable query extensions, you must set the com.solarmetric.kodo.EnableQueryExtensions configuration property to true.
Next, you must preface all extensions by ext: in your JDOQL filter strings. For example, the following example uses Kodo's built-in stringContains extension to search for cities with the substring "art" in their name (e.g. Hartford):
Query q = pm.newQuery (City.class);
q.setFilter ("name.ext:stringContains (\"art\")");
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:
Query q = pm.newQuery (City.class);
q.setFilter ("name.ext:toLowerCase ().ext:stringContains (\"art\")");
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 obejcts). 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.
Kodo includes several default query extensions to enhance the power of JDOQL:
stringContains: Tests if the target string contains the given argument. The argument must be a string literal or query parameter.
query.setFilter ("stringField.ext:stringContains (\"foo\")");
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\"");
wildcardMatch: Tests if the target string matches the wildcard pattern given in the argument. Wildcard patterns are strings that use ? to represent any single character, and * to represent any series of 0 or more characters. For example, the wildcard pattern "*foo?bar*" matches any string that contains the sequence "foo", then a single character, then "bar". The given wildcard argument must be either a string literal or a query parameter.
query.setFilter ("stringField.ext:wildcardMatch (\"*foo?bar*\")");
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 ("price < 10 || ext:sqlExp (\"(SELECT AVG(PRICE) FROM PRODUCTS)\") > 100");
Several of the query extensions defined in earlier versions of Kodo have been deprecated. These include:
ext:caseInsStarts
Requires DB support for UPPER (). Translates to UPPER (column) LIKE UPPER (argument%)
Use fieldname.toUpperCase ().startsWith ("FOO") instead.
ext:caseInsEnds
Requires DB support for UPPER (). Translates to UPPER (column) LIKE UPPER (%argument)
Use fieldname.toUpperCase ().endsWith ("FOO") instead.
ext:caseInsContains
Requires DB support for UPPER (). Translates to UPPER (column) LIKE UPPER (%argument%)
Use fieldname.toUpperCase ().ext:stringContains ("FOO") instead.
lit:sqlEmbed
Embeds argument right into the where clause as-is
Use ext:sqlExp ("SQL STATEMENT") instead.
You can write your own extensions by implementing the com.solarmetric.kodo.impl.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 com.solarmetric.kodo.query.listen and com.solarmetric.kodo.impl.jdbc.query.listen packages.
There are two ways to register your custom query extensions with Kodo:
Registration by properties: You can register custom query extensions by setting the com.solarmetric.kodo.QueryFilterListeners configuration property to a comma-separated list of the full names of 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 QueryImpl.registerListener method. You might use per-query registration for very specific extensions that do not apply globally.