JDOQL : Result

As we have seen, a JDOQL query is made up of different parts. In this section we look at the result part of the query. The result is what we want returning. By default (when not specifying the result) the objects returned will be of the candidate class type, where they match the query filter. Firstly let's look at what you can include in the result clause.

  • this - the candidate instance
  • A field name
  • A variable
  • A parameter (though why you would want a parameter returning is hard to see since you input the value in the first place)
  • An aggregate (count(), avg(), sum(), min(), max())
  • An expression involving a field (e.g "field1 + 1")
  • A navigational expression (navigating from one field to another ... e.g "field1.field4")

The result is specified in JDOQL like this 

query.setResult("count(field1), field2");

In Single-String JDOQL you would specify it directly.



Result type

What you specify in the result defines what form of result you get back.

  • Object - this is returned if you have only a single row in the results and a single column. This is achived when you specified either UNIQUE, or just an aggregate (e.g "max(field2)")
  • Object[] - this is returned if you have only a single row in the results, but more than 1 column (e.g "max(field1), avg(field2)")
  • List<Object> - this is returned if you have only a single column in the result, and you don't have only aggregates in the result (e.g "field2")
  • List<Object[]> - this is returned if you have more than 1 column in the result, and you don't have only aggregates in the result (e.g "field2, avg(field3)")
Aggregates

There are situations when you want to return a single number for a column, representing an aggregate of the values of all records. There are 5 standard JDO2 aggregate functions available. These are

  • avg(val) - returns the average of "val". "val" can be a field, numeric field expression or "distinct field".
  • sum(val) - returns the sum of "val". "val" can be a field, numeric field expression, or "distinct field".
  • count(val) - returns the count of records of "val". "val" can be a field, or can be "this", or "distinct field".
  • min(val) - returns the minimum of "val". "val" can be a field
  • max(val) - returns the maximum of "val". "val" can be a field

So to utilise these you could specify something like 

Query q = pm.newQuery("SELECT max(price), min(price) FROM org.datanucleus.samples.store.Product WHERE status == 1");

This will return a single row of results with 2 values, the maximum price and the minimum price of all products that have status code of 1.



Result MetaData

DataNucleus provides a useful extension if you wish to know the types of the result from a JDOQL (or JPQL) query. You do as follows 

Query q = pm.newQuery(...);
q.compile();
QueryResultsMetaData qmd = NucleusJDOHelper.getMetaDataForQueryResults(q);
int num = qmd.getExpressionCount();
for (int i=0;i<num;i++)
{
    Class type = qmd.getExpressionType(i);
}

so we now have the number of expressions returned, and the type of each one.



Example 1 - Use of aggregates

JDO 2 introduces the ability to use aggregates in queries. Here's another example using the same Product class as above, but this time looking for the maximum price of products that are CD Players. Note that the result for this particular query will be of type Double since there is a single double precision value being returned via the "result". 

Declarative JDOQL :
Query query = pm.newQuery(org.datanucleus.samples.store.Product.class);
query.setFilter("name == \"CD Player\"");
query.setResult("max(this.price)");
List results = (List)query.execute();
Iterator iter = c.iterator();
Double max_price = (Double)iter.next();

Single-String JDOQL :
Query query = pm.newQuery("SELECT max(price) FROM org.datanucleus.samples.store.Product WHERE name == \"CD Player\"");
List results = (List)query.execute();
Iterator iter = c.iterator();
Double max_price = (Double)iter.next();