Aggregates and projections allow a JDO application to efficiently work with an aggregation or subset of the data that would be returned from a standard JDO query. The APIs for manipulating aggregations and projections are not found in the standard javax.jdo.Query interface, so you must cast your queries to to kodo.query.KodoQuery in order to use them. These APIs are under consideration for version 2.0 of the JDO specification, however, so they may become standard in the future.
Aggregates perform simple statistical functions on the set of values returned by a query. While aggregate functions can always be computed by performing the calculations in memory against the results of a query, it is often inefficient to do so, since it can potentially draw down large amounts of data from the datastore. For example, datastore aggregate functions can be used to quickly calculate the average of millions of fields without having to instantiate each of the objects in the JVM.
Use the KodoQuery.setResult method to ask for aggregate data. The value passed in to the setResult method will be one or more aggregate functions of the form "function-name(parameter)". The given parameter can be any field traversal path or mathematical expression that would be legal as a value in a JDOQL filter. Multiple aggregates are separated by commas. See the section on built-in aggregate functions for details and examples.
When using aggregates, the return value of the Query.execute method will be either Object (if only a single aggregate is requested), or Object[] (if multiple aggregates are requested). The types of the returned objects depend on the aggregate functions used. The discussion of built-in aggregates below includes the return type for each aggregate. You can also override the default return type using a custom result class, which we describe later .
Example 10.3. Basic Query Aggregate
KodoQuery q = (KodoQuery) pm.newQuery (Product.class); q.setResult ("avg(cost)"); Number averageCost = (Number) q.execute (); System.out.println ("Average product cost: " + averageCost.doubleValue ());
Example 10.4. Multiple Query Aggregates
KodoQuery q = (KodoQuery) pm.newQuery (HomeOwner.class); q.setResult ("count(this), sum(income), min(income), max(income)"); Object[] results = (Object[]) q.execute (); long totalResidents = ((Number) results[0]).longValue (); double incomeSum = ((Number) results[1]).doubleValue (); double minIncome = ((Number) results[2]).doubleValue (); double maxIncome = ((Number) results[3]).doubleValue (); System.out.println ("Total residents: " + totalResidents); System.out.println ("Minimum income: " + minIncome); System.out.println ("Maximum income: " + maxIncome); System.out.println ("Average income: " + (incomeSum / totalResidents));
If the default return type of an aggregate does not match your needs, you can cast the return type to a specific class in the result string. When casting to a primitive type, the appropriate primitive wrapper class will be returned from the Query.execute method.
Kodo includes several default query aggregates that replicate the common aggregate functions of various data stores. If a data store supports additional desired aggregate functions, you can implement them with custom aggregate plugins, described in Section 10.8.1.3, “Developing Custom Query Aggregates”.
The count function returns the total number of objects returned by a certain query. The default return type of this function is Long.
// determine the total employees based in new york KodoQuery q = (KodoQuery) pm.newQuery (Company.class); query.setFilter ("state == \"New York\""); query.setResult ("count(this)"); long newYorkCompanies = ((Long) q.execute ()).longValue ();
The sum function returns the sum total of the parameter. The default return type of the function depends on the field type. If the field is an exact numeric type, the default return type is Long. If it is a floating point type T, the default return type is T. If you are taking the sum of a mathematical expression, the return type is Number.
// determine the sum total of all sales made in 2003 KodoQuery q = (KodoQuery) pm.newQuery (Sales.class); query.setFilter ("year == 2003"); query.setResult ("sum(amount)")); double yearSales = ((Double) q.execute ()).doubleValue ();
The min function returns the minimum value of the parameter. The default return type of this function is the type of the field it is applied to. If you apply this function to a mathematical expression, the default return type is Number.
// determine the shortest of all employees KodoQuery q = (KodoQuery) pm.newQuery (Employee.class); query.setResult ("min(height)"); float shortest = ((Float) q.execute ()).floatValue ();
The max function returns the maximum value of the parameter. The default return type of this function is the type of the field it is applied to. If you apply this function to a mathematical expression, the default return type is Number.
// determine the maximum salary of all the employees KodoQuery q = (KodoQuery) pm.newQuery (Employee.class); query.setResult ("max(salary)"); double richest = ((Double) q.execute ()).doubleValue ();
The avg function returns the average value of the parameter. The default return type of this function is the type of the field it is applied to. If you apply this function to a mathematical expression, the default return type is Number.
// calculate the average sales for all employees based in california KodoQuery q = (KodoQuery) pm.newQuery (Employee.class); query.setFilter ("company.state == \"California\""); query.setResult ("avg(sales.amount)"); double avgSale = ((Double) q.execute ()).doubleValue ();
You can write your own query aggregates by implementing the kodo.jdbc.query.JDBCAggregateListener interface. View the Javadoc documentation for details. When using your custom aggregates in result strings, prefix the function name with ext: to identify it as an extension to the standard set of 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 describing your aggregate implementation. Aggregates 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 aggregates for an individual query through the KodoQuery.registerListener method. You might use per-query registration for very specific aggregates that do not apply globally.
A projection is a set of field values that are components of the object graph that would result from a query. Projections can be useful for efficiently obtaining simple field values or relations from the candidate class without having to instantiate each instance.
Use the KodoQuery.setResult method to ask for projection data. The string you pass to the method will be one or more field names or relation traversal strings separated by commas.
When using projections, the execute method will return a collection whose elements are either instances of the value of the field being projected (or the wrapper class for primitive types), or arrays of type java.lang.Object whose elements will be instances of the field's class. You can also use a custom class to encapsulate the result data, as described in Section 10.8.4, “Custom Query Result Class” on Custom Result Classes.
Example 10.6. Basic Query Projections
KodoQuery q = (KodoQuery) pm.newQuery (Employee.class); q.setResult ("company.name"); Collection results = (Collection) q.execute (); for (Iterator itr = results.iterator (); itr.hasNext ();) System.out.println ("Company name: " + itr.next ()); q.close (results);
Example 10.7. Multiple Query Projections
KodoQuery q = (KodoQuery) pm.newQuery (Employee.class); q.setResult ("company.name, dateOfBirth, address"); Collection results = (Collection) q.execute (); for (Iterator itr = results.iterator (); itr.hasNext ();) { Object[] result = (Object[]) itr.next (); String companyName = (String) result[0]; Date dateOfBirth = (Date) result[1]; Address address = (Address) result[2]; // process this data... } q.close (results);
In JDOQL filter strings, you use variables to traverse collection and map fields. Each variable is typically constrained by including it in a contains clause of the filter string:
Query q = pm.newQuery (Company.class); q.declareVariables ("Product prod"); q.setFilter ("products.contains (prod) && prod.price < 10.0");
You can also use variables in the result string in your aggregates and projections, though the contains clause stays in the filter portion of the query. For example, the query below finds minimum price of any product in the Acme company.
KodoQuery q = (KodoQuery) pm.newQuery (Company.class); q.declareVariables ("Product prod"); q.setResult ("min(prod.price)"); q.setFilter ("name == \"Acme\" && products.contains (prod)"); double price = ((Number) q.execute ()).doubleValue ();
Similarly, to retrieve all the products of the Acme company without having to instantiate the company and traverse the relation, you can use a variable projection:
KodoQuery q = (KodoQuery) pm.newQuery (Company.class); q.declareVariables ("Product prod"); q.setResult ("prod"); q.setFilter ("name == \"Acme\" && products.contains (prod)"); Collection products = (Collection) q.execute ();
When using aggregate or projection queries, it is possible to specify that the resulting value (or collection of values) will be held in a specific custom class. Use the KodoQuery.setResultClass method to declare the class that will be instantiated to hold the results of the query.
The class that is the parameter to setResultClass can define JavaBean setter methods for field values, and these setters will be invoked for each field that results from the aggregate or projection query. The custom result class must be public (or otherwise configured to be able to be instantiated via reflection), and must have a no-args constructor.
Example 10.8. Using a Result Class Bean
public static class SalesData { private Date salesDate; private double amount; public void setSalesDate (Date salesDate) { this.salesDate = salesDate; } public Date getSalesDate () { return this.salesDate; } public void setAmount (double amount) { this.amount = amount; } public double getAmount () { return this.amount; } } KodoQuery q = (KodoQuery) pm.newQuery (Sales.class); q.setResult ("amount, salesDate"); q.setResultClass (SalesData.class); Collection data = (Collection) q.execute (); for (Iterator itr = data.iterator (); itr.hasNext ();) { SalesData item = (SalesData) itr.next (); System.out.println ("Date: " + item.getSalesDate () + " amount: " + item.getAmount ()); }
Whenever the specified result class does not contain a setter method for the aggregate or projection name, Kodo will try to detect a custom method named put that takes two java.lang.Object arguments. If found, Kodo will invoke that method with the aggregate or projection name as the first argument, and the value as the second argument. A custom result class can thus define a generic put(Object, Object) for use with any aggregate or projection queries. Also, this contract is suitable for use with any implementation of the java.util.Map interface which defines a Map.put method.
Example 10.9. Using a Generic Result Class
KodoQuery q = (KodoQuery) pm.newQuery (Employee.class); q.setResult ("firstName, lastName, company.name"); q.setResultClass (HashMap.class); Collection data = (Collection) q.execute (); for (Iterator itr = data.iterator (); itr.hasNext ();) { HashMap item = (HashMap) itr.next (); System.out.println ("Name: " + item.get ("firstName") + " " + item.get ("lastName") + " Company: " + item.get ("company.name")); }
It is possible to declare aliases in the setResult invocation for both aggregates and projections. These aliases are of the form "field or aggregate function as alias". The alias will be used for the result class' bean setter, or for the name of the put key in generic result class handling.
Example 10.10. Using a Result Alias
public static class SalesStatistics { private int totalSales; private double averageSales; public void setTotalSales (int totalSales) { this.totalSales = totalSales; } public int getTotalSales () { return this.totalSales; } public void setAverageSales (double averageSales) { this.averageSales = averageSales; } public double getAverageSales () { return this.averageSales; } } KodoQuery q = (KodoQuery) pm.newQuery (Sales.class); q.setResult ("count(this) as totalSales, avg(amount) as averageSales"); q.setResultClass (SalesStatistics.class); SalesStatistics stats = (SalesStatistics) q.execute (); System.out.println ("# of sales: " + stats.getTotalSales ()); System.out.println ("Average sale: " + stats.getAverageSales ());