English

Google App Engine

Queries in JDO

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

Every datastore query uses an index, a table that contains the results for the query in the desired order. An App Engine application defines its indexes in a configuration file named datastore-indexes.xml. The development web server automatically generates suggestions for this file as it encounters queries that do not yet have indexes configured.

The index-based query mechanism supports most common kinds of queries, but it does not support some queries you may be used to from other database technologies. For more information on App Engine indexes read the Introduction to Indexes. Restrictions on queries, and their explanations, are described below.

Introducing Queries in JDO

A query retrieves entities from the datastore that meet a set of conditions. The query specifies an entity kind, zero or more conditions based on entity property values (sometimes called "filters"), and zero or more sort order descriptions. When the query is executed, it fetches all entities of the given kind that meet all of the given conditions, sorted in the order described.

A query can also return just the keys of the result entities instead of the entities themselves.

JDO can perform queries for entities that meet certain criteria. You can also use a JDO Extent to represent the collection of every entity of a kind (every stored object of a class).

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. (The App Engine datastore does not support SQL queries with the JDO interface.)

The query API supports several calling styles. You can specify a complete query in a string, using the JDOQL string syntax. You can also specify some or all parts of the query by calling methods on the query object.

Here is a simple example of a query using the method style of calling, with one filter and one sort order, using parameter substitution for the value used in the filter. The Query object's execute() method is called with the values to substitute in the query, in the order they are declared.

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

// ...

    Query query = pm.newQuery(Employee.class);
    query.setFilter("lastName == lastNameParam");
    query.setOrdering("hireDate desc");
    query.declareParameters("String lastNameParam");

    try {
        List<Employee> results = (List<Employee>) query.execute("Smith");
        if (!results.isEmpty()) {
            for (Employee e : results) {
                // ...
            }
        } else {
            // ... no results ...
        }
    } finally {
        query.closeAll();
    }

Here is the same query using the string syntax:

    Query query = pm.newQuery("select from Employee " +
                              "where lastName == lastNameParam " +
                              "parameters String lastNameParam " +
                              "order by hireDate desc");

    List<Employee> results = (List<Employee>) query.execute("Smith");

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

    Query query = pm.newQuery(Employee.class,
                              "lastName == lastNameParam order by hireDate desc");
    query.declareParameters("String lastNameParam");

    List<Employee> results = (List<Employee>) query.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 value literals within the string for string values and numeric values. Surround strings in either single-quotes (') or double-quotes ("). All other value types must use parameter substitution. Here is an example using a string literal value:

    Query query = pm.newQuery(Employee.class,
                              "lastName == 'Smith' order by hireDate desc");

Query Filters

A filter specifies a field name, an operator, and a value. The value must be provided by the app; it cannot refer to another field, or be calculated in terms of other fields.

The filter operator can be any of the following:

  • < less than
  • <= less than or equal to
  • = equal to
  • > greater than
  • >= greater than or equal to
  • != not equal to

contains() filters (more commonly known as IN filters in SQL) are also supported using the following syntax:

    // Give me all employees with lastName equal to Smith or Jones
    Query query = pm.newQuery(Employee.class,
                              ":p.contains(lastName)");
    query.execute(Arrays.asList("Smith", "Jones"));

The != operator actually performs two queries: one where all other filters are the same and the not-equal filter is replaced with a less-than filter, and one where the not-equal filter is replaced with a greater-than filter. The results are merged, in order. As described below in the discussion of inequality filters, a query can only have one not-equal filter, and such a query cannot have other inequality filters.

The contains() operator also performs multiple queries, one for each item in the provided list value where all other filters are the same and the contains() filter is replaced with an equal-to filter. The results are merged, in the order of the items in the list. If a query has more than one contains() filter, the query is performed as multiple queries, one for each combination of values in the contains() filters.

A single query containing != or contains() operators is limited to 30 sub-queries.

For more information, please refer to Queries with != and IN filters.

Due to the way the App Engine datastore executes queries, 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.

    query.setFilter("lastName == 'Smith' && hireDate > hireDateMinimum");
    query.declareParameters("Date hireDateMinimum");

An entity must match all filters to be a result. In the JDOQL string syntax, you can separate multiple filters with || (logical "or") and && (logical "and"), although keep in mind that || can only be employed when the filters it separates all have the same field name. In other words, || is only legal in situations where the filters it separates can be combined into a single contains() filter:

    // legal, all filters separated by || are on the same field
    Query query = pm.newQuery(Employee.class,
                              "(lastName == 'Smith' || lastName == 'Jones')" +
                              " && firstName == 'Harold'");

    // not legal, filters separated by || are on different fields
    Query query = pm.newQuery(Employee.class,
                              "lastName == 'Smith' || firstName == 'Harold'");

Negation (logical "not") is not supported.

Query Sort Orders

A sort order specifies a property and a direction, either ascending or descending. The results are returned sorted by the given orders, in the order they were specified. If no sort orders are specified for the query, the ordering of the results is undefined and will be returned as they are retrieved from the datastore.

Due to the way the App Engine datastore executes queries, if a query specifies inequality filters on a property and sort orders on other properties, the property used with the inequality filters must be ordered before the other properties.

    query.setOrdering("hireDate desc, firstName asc");

Query Ranges

A query can specify a range of results to be returned to the application. The range specifies which result in the complete result set should be the first one returned, and which should be the last, using numeric indexes, starting with zero for the first result. For example, a range of 5, 10 returns the 6th, 7th, 8th, 9th and 10th results.

The starting offset has implications for performance: the datastore must retrieve and then discard all results prior to the starting offset. For example, a query with a range of 5, 10 fetches ten results from the datastore, then discards the first five and returns the remaining five to the application.

    query.setRange(5, 10);

If your goal is to implement pagination with the App Engine datastore, you should instead consider the techniques described in this article. The examples in this article are in Python, but the same concepts apply in Java.

Extents

A JDO Extent represents every object in the datastore of a particular class.

You start an Extent using the PersistenceManager's getExtent() method, passing it to the data class. The Extent class implements the Iterable interface for accessing results. When you are done accessing results, you call the closeAll() method.

The following example iterates over every Employee object in the datastore:

import java.util.Iterator;
import javax.jdo.Extent;

// ...

    Extent extent = pm.getExtent(Employee.class, false);
    for (Employee e : extent) {
        // ...
    }
    extent.closeAll();

An extent retrieves results in batches, as needed.

Querying Entity Keys

Entity keys can be the subject of a query filter or sort order. In JDO, you refer to the entity key in the query using the primary key field of the object. The datastore considers the complete key value for such queries, including the entity's parent path, the kind, and the app-assigned key name string or system-assigned numeric ID.

To use a key as a query filter, you specify the parameter type to be a Key with declareParameters(). The following finds all Persons with a given favorite food, assuming an unowned one-to-one relationship between Person and Food:

    Food chocolate = ...
    Query q = pm.newQuery(Person.class);
    query.setFilter("favoriteFood = favoriteFoodParam");
    query.declareParameters(Key.class.getName() + " favoriteFoodParam");
    List<Person> chocolateLovers = (List<Person>) q.execute(chocolate.getKey());

A query can return entity keys instead of full entities. You can trigger this behavior by only selecting the @PrimaryKey field in your query:

    Query q = pm.newQuery("select id from " + Person.class.getName());
    List<String> ids = (List<String>) q.execute();

Note: Queries that return keys are faster and cost less CPU than queries that return entities, since the keys themselves are already in the index. As a result, the query doesn't need to fetch the actual entities. If you only need the keys from your query results, consider using a keys-only query.

Because an entity key is unique across all entities in the system, key queries make it easy to retrieve entities of a given kind in batches, such as for a batch dump of the contents of the datastore. Unlike JDOQL ranges, this works efficiently for any number of entities.

Keys are ordered first by parent path, then by kind, then by key name or ID. Kinds and key names are strings and are ordered by byte value. IDs are integers and are ordered numerically. If entities of the same parent and kind use a mix of key name strings and numeric IDs, entities with numeric IDs are considered to be less than entities with key name strings. Elements of the parent path are compared similarly: by kind (string), then by key name (string) or ID (number).

Queries involving keys use indexes just like queries involving properties. Queries on keys require custom indexes in the same cases as with properties, with a couple of exceptions: inequality filters or an ascending sort order on Entity.KEY_RESERVED_PROPERTY do not require a custom index, but a descending sort order on Entity.KEY_RESERVED_PROPERTY does. As with all queries, the development web server creates appropriate configuration entries in this file when a query that needs a custom index is tested.

Deleting Entities By Query

If you are issuing a query with the goal of deleting all the entities that match the query filter, you can save yourself a bit of coding by using JDO's "delete by query" feature. The following deletes all People over a given height:

    Query query = pm.newQuery(Person.class);
    query.setFilter("height > maxHeightParam");
    query.declareParameters("int maxHeightParam");
    query.deletePersistentAll(maxHeight);

You'll notice that the only difference here is that instead of calling query.execute(...) we are instead calling query.deletePersistentAll(...). All of the rules and restrictions relating to filters, sort orders, and indexes explained above apply to queries whether you are selecting or deleting the result set. Note, however, that just as if you had deleted these Person entities with pm.deletePersistent(...), any dependent children of the entities deleted by the query will also be deleted. For more information on dependent children please see Dependent Children and Cascading Deletes.

Using Query Cursors in JDO

In JDO, you can use an extension and the JDOCursorHelper class to use cursors with JDO queries. Cursors work when fetching results as a list or using an iterator. To get a Cursor, you pass either the result List or Iterator to the JDOCursorHelper.getCursor() static method:

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.jdo.Query;
import com.google.appengine.api.datastore.Cursor;
import org.datanucleus.store.appengine.query.JDOCursorHelper;

// ...
        Query query = pm.newQuery(Employee.class);
        query.setRange(0, 20);
        // ...

        List<Employee> results = (List<Employee>) query.execute();
        // Use the first 20 results...

        Cursor cursor = JDOCursorHelper.getCursor(results);
        String cursorString = cursor.toWebSafeString();
        // Store the cursorString...

        // ...

        // Query query = the same query that produced the cursor
        // String cursorString = the string from storage
        Cursor cursor = Cursor.fromWebSafeString(cursorString);
        Map<String, Object> extensionMap = new HashMap<String, Object>();
        extensionMap.put(JDOCursorHelper.CURSOR_EXTENSION, cursor);
        query.setExtensions(extensionMap);
        query.setRange(0, 20);

        List<Employee> results = (List<Employee>) query.execute();
        // Use the next 20 results...

For more information, see Query Cursors.

Setting the Read Policy and Datastore Call Deadline

You can set the read policy (strong consistency vs. eventual consistency) and the datastore call deadline for all calls made by a PersistenceManager instance using configuration. You can also override these options for an individual Query object.

To override the read policy for a single Query, call its addExtension() method as follows:

        Query q = pm.newQuery(Employee.class);
        q.addExtension("datanucleus.appengine.datastoreReadConsistency", "EVENTUAL");

The possible values are "EVENTUAL" (for reads with eventual consistency) and "STRONG" (for reads with strong consistency). The default is "STRONG", unless set otherwise in the configuration set in the jdoconfig.xml.

To override the datastore call deadline for a single Query, call its setTimeoutMillis() method:

q.setTimeoutMillis(3000);

The value is an amount of time, as a number of milliseconds.

There is no way to override the configuration for these options when you fetch entities by key.

When eventual consistency is selected for a datastore query, the indexes the query uses to gather results are also accessed with eventual consistency. Queries occasionally return entities that don't match the query criteria&mdash'though this is also true with a strongly consistent read policy. (You can use transactions to ensure a consistent result set if the query uses an ancestor filter.) See Transaction Isolation in App Engine for more information on how entities and indexes are updated.