Datastore Queries in JDO

This document focuses on the use of the Java Data Objects (JDO) persistence framework for App Engine Datastore queries. For more general information about queries, see the main Datastore Queries page.

A query retrieves entities from Cloud Datastore that meet a specified set of conditions. The query operates on entities of a given kind; it can specify filters on the entities' property values, keys, and ancestors, and can return zero or more entities as results. A query can also specify sort orders to sequence the results by their property values. The results include all entities that have at least one (possibly null) value for every property named in the filters and sort orders, and whose property values meet all the specified filter criteria. The query can return entire entities, projected entities, or just entity keys.

A typical query includes the following:

  • An entity kind to which the query applies
  • Zero or more filters based on the entities' property values, keys, and ancestors
  • Zero or more sort orders to sequence the results
When executed, the query retrieves all entities of the given kind that satisfy all of the given filters, sorted in the specified order. Queries execute as read-only.

Note: To conserve memory and improve performance, a query should, whenever possible, specify a limit on the number of results returned.

Note: The index-based query mechanism supports a wide range of queries and is suitable for most applications. However, it does not support some kinds of query common in other database technologies: in particular, joins and aggregate queries aren't supported within the Cloud Datastore query engine. See Datastore Queries page for limitations on Cloud Datastore queries.

Queries with JDOQL

JDO includes a query language for retrieving objects that meet a set of criteria. This language, called JDOQL, refers to JDO data classes and fields directly, and includes type checking for query parameters and results. JDOQL is similar to SQL, but is more appropriate for object-oriented databases like the App Engine Datastore. (App Engine's implementation of the JDO API doesn't support SQL queries directly.)

The JDO Query interface supports several calling styles: you can specify a complete query in a string, using the JDOQL string syntax, or you can specify some or all parts of the query by calling methods on the Query object. The following example shows the method style of calling, with one filter and one sort order, using parameter substitution for the value used in the filter. The argument values passed to the Query object's execute() method are substituted into the query in the order specified:

import java.util.List;
import javax.jdo.Query;

// ...

Query q = pm.newQuery(Person.class);
q.setFilter("lastName == lastNameParam");
q.setOrdering("height desc");
q.declareParameters("String lastNameParam");

try {
  List<Person> results = (List<Person>) q.execute("Smith");
  if (!results.isEmpty()) {
    for (Person p : results) {
      // Process result p
    }
  } else {
    // Handle "no results" case
  }
} finally {
  q.closeAll();
}

Here is the same query using the string syntax:

Query q = pm.newQuery("select from Person " +
                      "where lastName == lastNameParam " +
                      "parameters String lastNameParam " +
                      "order by height desc");

List<Person> results = (List<Person>) q.execute("Smith");

You can mix these styles of defining the query. For example:

Query q = pm.newQuery(Person.class,
                      "lastName == lastNameParam order by height desc");
q.declareParameters("String lastNameParam");

List<Person> results = (List<Person>) q.execute("Smith");

You can reuse a single Query instance with different values substituted for the parameters by calling the execute() method multiple times. Each call performs the query and returns the results as a collection.

The JDOQL string syntax supports literal specification of string and numeric values; all other value types must use parameter substitution. Literals within the query string can be enclosed in either single (') or double (") quotation marks. Here is an example using a string literal:

Query q = pm.newQuery(Person.class,
                      "lastName == 'Smith' order by height desc");

Filters

A property filter specifies

  • A property name
  • A comparison operator
  • A property value
For example:

Filter propertyFilter =
    new FilterPredicate("height", FilterOperator.GREATER_THAN_OR_EQUAL, minHeight);
Query q = new Query("Person").setFilter(propertyFilter);
Query q = pm.newQuery(Person.class);
q.setFilter("height <= maxHeight");

The property value must be supplied by the application; it cannot refer to or be calculated in terms of other properties. An entity satisfies the filter if it has a property of the given name whose value compares to the value specified in the filter in the manner described by the comparison operator.

The comparison operator can be any of the following:

Operator Meaning
== Equal to
< Less than
<= Less than or equal to
> Greater than
>= Greater than or equal to
!= Not equal to

As described on the main Queries page, a single query cannot use inequality filters (<, <=, >, >=, !=) on more than one property. (Multiple inequality filters on the same property, such as querying for a range of values, are permitted.) contains() filters, corresponding to IN filters in SQL, are supported using the following syntax:

// Query for all persons with lastName equal to Smith or Jones
Query q = pm.newQuery(Person.class, ":p.contains(lastName)");
q.execute(Arrays.asList("Smith", "Jones"));

The not-equal (!=) operator actually performs two queries: one in which all other filters are unchanged and the not-equal filter is replaced with a less-than (<) filter, and one where it is replaced with a greater-than (>) filter. The results are then merged, in order. A query can have no more than one not-equal filter, and a query that has one cannot have any other inequality filters.

The contains() operator also performs multiple queries: one for each item in the specified list, with all other filters unchanged and the contains() filter replaced with an equality (==) filter. The results are merged in order of the items in the list. If a query has more than one contains() filter, it is performed as multiple queries, one for each possible combination of values in the contains() lists.

A single query containing not-equal (!=) or contains() operators is limited to no more than 30 subqueries.

For more information about how != and contains() queries translate to multiple queries in a JDO/JPA framework, see the article Queries with != and IN filters.

In the JDOQL string syntax, you can separate multiple filters with the && (logical "and") and || (logical "or") operators:

q.setFilter("lastName == 'Smith' && height < maxHeight");

Negation (logical "not") is not supported. Keep in mind, also, that the || operator can be employed only when the filters it separates all have the same property name (that is, when they can be combined into a single contains() filter):

// Legal: all filters separated by || are on the same property
Query q = pm.newQuery(Person.class,
                      "(lastName == 'Smith' || lastName == 'Jones')" +
                      " && firstName == 'Harold'");

// Not legal: filters separated by || are on different properties
Query q = pm.newQuery(Person.class,
                      "lastName == 'Smith' || firstName == 'Harold'");

Sort Orders

A query sort order specifies

  • A property name
  • A sort direction (ascending or descending)

For example:

// Order alphabetically by last name:
Query q1 = new Query("Person").addSort("lastName", SortDirection.ASCENDING);

// Order by height, tallest to shortest:
Query q2 = new Query("Person").addSort("height", SortDirection.DESCENDING);

For example:

// Order alphabetically by last name:
Query q = pm.newQuery(Person.class);
q.setOrdering("lastName asc");

// Order by height, tallest to shortest:
Query q = pm.newQuery(Person.class);
q.setOrdering("height desc");

If a query includes multiple sort orders, they are applied in the sequence specified. The following example sorts first by ascending last name and then by descending height:

Query q =
    new Query("Person")
        .addSort("lastName", SortDirection.ASCENDING)
        .addSort("height", SortDirection.DESCENDING);
Query q = pm.newQuery(Person.class);
q.setOrdering("lastName asc, height desc");

If no sort orders are specified, the results are returned in the order they are retrieved from Cloud Datastore.

Note: Because of the way Cloud Datastore executes queries, if a query specifies inequality filters on a property and sort orders on other properties, the property used in the inequality filters must be ordered before the other properties.

Ranges

A query can specify a range of results to be returned to