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 | |
---|---|
Kodo allows you to define your own aggregate functions. See Section 9.6, “Query Language Extensions” in the Reference Guide for details. |
The following example counts the number of magazines that cost under 5 dollars:
Example 11.17. 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.18. 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.19. 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"); List results = (List) 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 List
. 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 List
.
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 List
.
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.
sum(expression)
returns a
long
for integral types other
than BigInteger
, and the
expression type for all other types.
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
list of Magazine
s. 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 list of String
s. But
a projection for both the title and price of each magazine returns a
list 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.