11.6. Projections
Prev | Chapter 11. Query | Next

## 11.6. Projections

At the beginning of the chapter, we said that JDO queries serve two purposes: object filtering and data retrieval. So far, we have concentrated primarily on object filtering. We now shift our focus to data retrieval, starting with projections.

A projection consists of one or more values formed by traversing the fields of the candidates. You can use projections to get back only the fields or relations that you are interested in, rather than manually pulling the desired data from each result object. As you will see later, you can even instruct JDO to pack the projected field values into a class of your choosing.

Projections are established with the `setResult` method:

    public void setResult (String result);

`result` is a comma-separated string of result expressions. Each result expression might be a field name, a relation traversal, or any other valid JDOQL clause. In all our queries to this point, we've taken advantage of the fact that when you don't specify a result string explicitly, it defaults to `this`, meaning the query returns each matching object itself (actually, `result` defaults to `distinct this as C`, where `C` is the unqualified candidate class name, but for now just think of it as `this`).

We present a simple projection below. We're still using the object model defined in Section 11.1, “Object Filtering”.

Example 11.12. Projection

    Query query = pm.newQuery (Magazine.class);
    query.setResult ("title, price");
    Collection results = (Collection) query.execute ();
    for (Iterator itr = results.iterator (); itr.hasNext ();)
    {
        Object[] data = (Object[]) itr.next ();
        processData ((String) data[0], (Double) data[1]);
    }
    query.close (results);

Each query result in this case is not a `Magazine` instance, but an array consisting of each magazine's title and price. Projections allow you to acquire only the data you're interested in, and ignore the rest.

`JDOHelper.getObjectId` becomes a particularly useful JDOQL method when applied to projections. Notice that because we only select a single result expression this time, we get back the raw result values (in this case, identity objects) instead of object arrays.

    Query query = pm.newQuery (Magazine.class, "publisher.revenue > 1000000");
    query.setResult ("JDOHelper.getObjectId (this)");
    Collection oids = (Collection) query.execute ();
    for (Iterator itr = oids.iterator (); itr.hasNext ();)
        processObjectId (itr.next ());
    query.close (oids);

Projections aren't limited to the candidate class and its fields. Here is a query selecting the first 3 characters of the cover article's title for all magazines under 5 dollars:

Example 11.13. Projection Field Traversal

    Query query = pm.newQuery (Magazine.class, "price < 5");
    query.setResult ("coverArticle.title.substring (0, 3)");
    Collection ttls = (Collection) query.execute ();
    for (Iterator itr = ttls.iterator (); itr.hasNext ();)
        processTitle ((String) itr.next ());
    query.close (ttls);

But what if we change our minds, and decide we're really interested in the subtitles of all cover articles, rather than their titles? Setting the result query to `coverArticle.subtitles.substring (0, 3)` won't do the trick; `subtitles` is a `Collection` field, and `Collection` doesn't have a `substring` method. What we're after isn't the `subtitles` field itself, but its elements.

You may recall from previous examples that we used query variables to represent the elements of collections. We can apply the same solution here. By binding a variable to the elements of the `subtitles` collection in our filter and selecting the variable in our result string, we achieve our goal.

Example 11.14. Projection Variables

    Query query = pm.newQuery (Magazine.class, "price < 5 "
        + "&& coverArticle.subtitles.contains (ttl)");
    query.setResult ("ttl.substring (0, 3)");
    Collection ttls = (Collection) query.execute ();
    for (Iterator itr = ttls.iterator (); itr.hasNext ();)
        processSubtitle ((String) itr.next ());
    query.close (ttls);

Now we have our subtitle data, but we're faced with a new problem: a lot of subtitles start with the same 3 characters, and we really only want to process each string once. Luckily, JDO has a built-in solution. If you begin a result string with the `distinct` keyword, JDO filters out duplicates. This applies not only to simple projections as in this example, but to complex projections consisting of multiple return values, including persistent object values.

Example 11.15. Distinct Projection

    Query query = pm.newQuery (Magazine.class, "price < 5 "
        + "&& coverArticle.subtitles.contains (ttl)");
    query.setResult ("distinct ttl.substring (0, 3)");
    Collection ttls = (Collection) query.execute ();
    for (Iterator itr = ttls.iterator (); itr.hasNext ();)
        processSubtitle ((String) itr.next ());
    query.close (ttls);

| Note
Some object-relational mapping products require you to alter your query to circumvent duplicate results caused by relational joins. In the simple case, this may only involve using a `SELECT DISTINCT` equivalent. But in the complex case, such as with aggregate data, you might need to tell the implementation to use subselects and other complicated workarounds to avoid the relational joins problem. This is not the case in JDO. JDO implementations always automatically eliminate duplicates caused by relational joins. You only need to use the `distinct` keyword in your JDO result string when there are repeated values in the database that you'd like to filter out.

Remember the unbound variable example earlier in this chapter? We wanted to find all the cover articles of magazines that sold over 10,000 copies, but we had to work around the fact that there is no relation from `Article` to `Magazine`:

    Query query = pm.newQuery (Article.class, "mag.copiesSold > 10000 "
        + "&& mag.coverArticle == this");
    query.declareVariables ("Magazine mag");
    Collection arts = (Collection) query.execute ();
    for (Iterator itr = arts.iterator (); itr.hasNext ();)
        processArticle ((Article) itr.next ());
    query.close (arts);

To conclude the section, let's simplify this query using our newfound knowledge of projections:

    Query query = pm.newQuery (Magazine.class, "copiesSold > 10000");
    query.setResult ("coverArticle");
    Collection arts = (Collection) query.execute ();
    for (Iterator itr = arts.iterator (); itr.hasNext ();)
        processArticle ((Article) itr.next ());
    query.close (arts);

Prev | Up | Next
11.5. Limits and Ordering | Home | 11.7. Aggregates