9.7 Ordering Query Results

An application can specify an order for the query result by providing an ordering statement, specified by a String that contains one or more ordering declarations, separated by commas. Each ordering declaration is a Java expression of an orderable type, followed by either ascending or descending. Your ordering expression may use the . operator to navigate references.

Each ordering expression must be one of the following types:

• Any primitive type except boolean

• Any wrapper type except Boolean

• BigDecimal

• BigInteger

• String

• Date

We mentioned earlier that JDO does not define the ordering of Strings when you use the comparison operators (<, <=, >, and >=). This also applies for the ordering of query results.

The following Query method binds the ordering statement to the Query instance:

void setOrdering(String ordering);

The ordering statement may include multiple ordering expressions. The result of the leftmost expression is used first to order the results. If the leftmost expression evaluates to the same value for two or more elements, then the second expression is used to order those elements. If the second expression also evaluates to the same value, then the third expression is used, and so on, until the last expression is evaluated. If the values of all of the ordering expressions are equal for two or more elements, then the ordering of those elements is unspecified.

The following example demonstrates the use of ordering:

public static void queryTransactionsInCity(PersistenceManager pm,
                               String city, String state, Date acquired) {
    Extent transactionExtent =
        pm.getExtent(com.mediamania.store.Transaction.class, true);
    Query query = pm.newQuery(transactionExtent);
    query.declareParameters("String thecity, String thestate, Date date");     [1]
    query.declareImports("import java.util.Date");     [2]
    String filter = "customer.address.city == thecity && " +     [3]
            "customer.address.state == thestate && acquisitionDate >= date";
    query.setFilter(filter);
    String order =  "customer.address.zipcode descending, " +     [4]
                    "customer.lastName ascending, " +
                    "customer.firstName ascending, acquisitionDate ascending";
    query.setOrdering(order);     [5]
    Collection result = (Collection) query.execute(city, state, acquired);
    Iterator iter = result.iterator(  );
    while (iter.hasNext(  )) {
        com.mediamania.store.Transaction tx =
            (com.mediamania.store.Transaction) iter.next(  );
        
        // process Transactions
    }
    query.close(result);
}

The query returns all Transaction instances that occurred on or after a specified date for customers in a given city and state. Line [1] declares these necessary parameters. We also need to import the Date class for the date parameter on line [2]. The filter declared on line [3] uses these parameters to limit the Transaction instances returned by the query. We specify the ordering expression on line [4] and set it on line [5]. The Transaction instances are ordered first in descending order, based on the customer's ZIP code. All instances in the same ZIP code are placed in ascending order, based on the customer's last and first name. Transaction instances for specific customers with unique last and first names are placed in ascending order, based on the date they acquired the media content. The ordering declarations are separated by a comma in the ordering expression.

The ordering of instances is not specified when the fields used in the ordering expression have null values. Implementations may differ in how they perform the ordering; they may place the instances containing null-valued fields either before or after instances whose fields contain non-null values.