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. |
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).
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)')
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.
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.
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.
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.
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.
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 ()
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.
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.