10.8. Query Aggregates and Projections
Prev | Chapter 10. JDO Runtime Extensions | Next

## 10.8. Query Aggregates and Projections

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.

### 10.8.1. Query Aggregates

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.

#### 10.8.1.1. Using Query Aggregates

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.

Example 10.5. Casting Query Aggregates

    KodoQuery q = (KodoQuery) pm.newQuery (Student.class);
    q.setResult ("(int) avg(height)");
    int roundedAverageHeight = ((Integer) q.execute ()).intValue ();

#### 10.8.1.2. Included Query Aggregates

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”.

##### 10.8.1.2.1. count

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 ();

##### 10.8.1.2.2. sum

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 ();

##### 10.8.1.2.3. min

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 ();

##### 10.8.1.2.4. max

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 ();

##### 10.8.1.2.5. avg

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 ();

#### 10.8.1.3. Developing Custom Query Aggregates

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.

#### 10.8.1.4. Configuring Query 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.

### 10.8.2. Query Projections

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.

#### 10.8.2.1. Using Query Projections

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);

### 10.8.3. JDOQL Variables in Aggregates and Projections

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 ();

### 10.8.4. Custom Query Result Class

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.

#### 10.8.4.1. Using a Bean Query Result Class

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 ());
    }

#### 10.8.4.2. Using a Generic Query Result Class

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"));
    }

#### 10.8.4.3. Using a Result Alias

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 ());

Prev | Up | Next
10.7. Query Extensions | Home | 10.9. Object Locking