JDO : 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.

  • DISTINCT - optional keyword at the start of the results to make them distinct
  • 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.

  • {ResultClass} - this is returned if you have only a single row in the results and you specified a result class.
  • 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<{ResultClass}> - this is returned if you specified a result class.
  • 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)")

Note that if you specify unique you will be returned null or the object in question, and if there are more results than just 1 then it will throw a JDOUserException.


Result Class

If you want results returned of a particular type per result row, you can specify a result class. The Result Class has to meet certain requirements. These are

  • Can be one of Integer, Long, Short, Float, Double, Character, Byte, Boolean, String, java.math.BigInteger, java.math.BigDecimal, java.util.Date, java.sql.Date, java.sql.Time, java.sql.Timestamp, or Object[]
  • Can be a user defined class, that has either a constructor taking arguments of the same type as those returned by the query (in the same order), or has a public put(Object, Object) method, or public setXXX() methods, or public fields.

In terms of how the Result Class looks, you have two options

  • Constructor taking arguments of the same types and the same order as the result clause. An instance of the result class is created using this constructor. For example
    public class Price
    {
        protected double amount = 0.0;
        protected String currency = null;
    
        public Price(double amount, String currency)
        {
            this.amount = amount;
            this.currency = currency;
        }
    
        ...
    }
  • Default constructor, and setters for the different result columns, using the alias name for each column as the property name of the setter. For example
    public class Price
    {
        protected double amount = 0.0;
        protected String currency = null;
    
        public Price()
        {
        }
    
        public void setAmount(double amt) {this.amount = amt;}
        public void setCurrency(String curr) {this.currency = curr;}
    
        ...
    }

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 JDO aggregate functions available. These are

  • avg(val) - returns the average of "val". "val" can be a field, numeric field expression or "distinct field". Returns double, or BigDecimal depending on the type being averaged.
  • sum(val) - returns the sum of "val". "val" can be a field, numeric field expression, or "distinct field". Returns the same type as the type being summed
  • count(val) - returns the count of records of "val". "val" can be a field, or can be "this", or "distinct field". Returns long
  • min(val) - returns the minimum of "val". "val" can be a field. Returns the same type as the type used in "min"
  • max(val) - returns the maximum of "val". "val" can be a field. Returns the same type as the type used in "max"

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.


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