Aggregates are just what they sound like: aggregations of data from multiple instances. Combined with object filtering and grouping, aggregates are powerful tools for summarizing your persistent data.
JDOQL includes the following aggregate functions:
min(expression): Returns the minimum value of the given expression among matching instances.
max(expression): Returns the maximum value of the given expression among matching instances.
sum(expression): Returns the sum of the given expression over all matching instances.
avg(expression): Returns the average of the given expression over all matching instances.
count(expression): Returns the number of matching instances for which the given expression is not null.
![[Note]](img/note.gif) | Note |
|---|---|
Kodo allows you to define your own aggregate functions. See Section 10.7, “Query Extensions” in the Reference Guide for details. | |
The following example counts the number of magazines that cost under 5 dollars:
Example 11.16. Count
Query query = pm.newQuery (Magazine.class, "price < 5");
query.setResult ("count(this)");
Long count = (Long) query.execute ();
You may be thinking that we could have gotten the count just as easily by executing a standard query and calling Collection.size () on the result, and you'd be right. But not all aggregates are so easy to replace. Our next example retrieves the minimum, maximum, and average magazine prices in the database. These values would be a little more difficult to calculate manually. More importantly, iterating over every single persistent magazine in order to factor its price into our calculations would be woefully inefficient.
Example 11.17. Min, Max, Avg
Query query = pm.newQuery (Magazine.class);
query.setResult ("min(price), max(price), avg(price)");
Object[] prices = (Object[]) query.execute ();
Double min = (Double) prices[0];
Double max = (Double) prices[1];
Double avg = (Double) prices[2];
The functionality described above is useful, but aggregates only really shine when you combine them with object grouping.
public void setGrouping (String grouping);
The Query interface's setGrouping method allows you to group query results on field values. The grouping string consists of one or more comma-separated clauses to group on, optionally followed by the having keyword and a boolean expression. The having expression pares down the candidate groups just as the query's filter pares down the candidate objects.
Now your aggregates apply to each group, rather than to all matching objects. Let's see this in action:
Example 11.18. Grouping
The following query returns each publisher and the average price of its magazines, for all publishers that publish no more than 10 magazines.
Query query = pm.newQuery (Magazine.class);
query.setResult ("publisher, avg(price)");
query.setGrouping ("publisher having count(this) <= 10");
Collection results = (Collection) query.execute ();
for (Iterator itr = results.iterator (); itr.hasNext ();)
{
Object[] data = (Object[]) itr.next ();
processData ((Company) data[0], (Double) data[1]);
}
query.close (results);
You probably noticed that in our initial aggregate examples, the queries all returned a single result object, while the query above returned a Collection. Before you get too confused, let's take a brief detour to examine query return types.
If you have called setUnique(true), the query returns a single result object (or null).
Else if you have called setUnique(false), the query returns a collection.
Else if the query result is an aggregate and you have not specified any grouping, the query returns a single result object.
Else the query returns a collection.
In addition to how many results are returned, query configuration can affect the type of each result:
If you have specified a result class, the query returns instances of that class. We cover result classes in the next section.
Else if you have not set a result string, the query returns instances of the candidate class.
Else if you have specified a single projection or aggregate result:
A projection returns instances of the projected field type. When mathematical expressions are involved, the less precise operand is always promoted to the type of the more precise operand.
min(expression) returns an instance of the expression type.
max(expression) returns an instance of the expression type.
sum(expression) returns a long for integral types other than BigInteger, and the expression type for all other types.
avg(expression) returns an instance of the expression type.
count(expression) returns a long.
Else if you have specified multiple projections or aggregates in your result string, the query returns instances of Object[], where the class of each array index value follows the typing rules above.
Casting a projection or aggregate expression in the result string converts that result element to the type specified in the cast.
Don't worry about trying to memorize all of these rules. In practice, they amount to a much simpler rule: queries return what you expect them to. A query for all the magazines that match a filter returns a collection of Magazines. But an aggregate query for the count of all magazines that match a filter just returns a Long. A projection query for the title of all magazines returns a collection of Strings. But a projection for both the title and price of each magazine returns a collection of Object[]s, each consisting of a String and a Double. So although you can always explicitly set the unique flag and result class to obtain a specific result shape, the defaults are usually exactly what you want.