17.2. Retrieving Persistent Objects with SQL
Prev | Chapter 17. SQL Queries | Next

## 17.2. Retrieving Persistent Objects with SQL

When you give a SQL `Query` a candidate class, it will return persistent instances of that class. At a minimum, your SQL must select the class' primary key columns, discriminator column (if mapped), and version column (also if mapped). The JDO runtime uses the values of the primary key columns to construct each result object's identity, and possibly to match it with a persistent object already in the `PersistenceManager`'s cache. When an object is not already cached, the implementation creates a new object to represent the current result row. It might use the discriminator column value to make sure it constructs an object of the correct subclass. Finally, the query records available version column data for use in optimistic concurrency checking, should you later change the result object and flush it back to the database.

Aside from the primary key, discriminator, and version columns, any columns you select are used to populate the persistent fields of each result object. JDO implementations will compete on how effectively they map your selected data to your persistent instance fields.

Let's make the discussion above concrete with an example. It uses the following simple mapping between a class and the database:

Example 17.2. Retrieving Persistent Objects

    Query query = pm.newQuery ("javax.jdo.query.SQL", "SELECT ISBN, TITLE, PRICE, "
        + "VERS FROM MAG WHERE PRICE > 5 AND PRICE < 10");
    query.setClass (Magazine.class);
    List results = (List) query.execute ();
    for (Iterator itr = results.iterator (); itr.hasNext ();)
        processMagazine ((Magazine) itr.next ());
    query.close (results);

It is very important to notice that we explicitly set the candidate class for the query. If we had not performed this step, the query would have been treated as a projection. We cover SQL projections later in this chapter.

The query above works as advertised, but isn't very flexible. Let's update it to take in parameters for the minimum and maximum price, so we can reuse it to find magazines in any price range:

Example 17.3. SQL Query Parameters

    Query query = pm.newQuery ("javax.jdo.query.SQL", "SELECT ISBN, TITLE, PRICE, "
        + "VERS FROM MAG WHERE PRICE > ? AND PRICE < ?");
    query.setClass (Magazine.class);

    Double min = new Double (5D);
    Double max = new Double (10D);
    List results = (List) query.execute (min, max);
    for (Iterator itr = results.iterator (); itr.hasNext ();)
        processMagazine ((Magazine) itr.next ());
    query.close (results);

Like JDBC prepared statements, SQL queries represent parameters with question marks. When you run a query with multiple parameters, the order of your arguments to the `execute` method must match the order of the question marks for each parameter in your SQL. To use the `Query` interface's `executeWithMap` method, imagine that the question marks are labeled from 1 to N, and key each parameter value on the correct `Integer` position. Here is the above example again, this time using a parameter map:

Example 17.4. SQL Query Parameter Map

    Query query = pm.newQuery ("javax.jdo.query.SQL", "SELECT ISBN, TITLE, PRICE, "
        + "VERS FROM MAG WHERE PRICE > ? AND PRICE < ?");
    query.setClass (Magazine.class);

    Map params = new HashMap ();
    params.put (new Integer (1), new Double (5D));
    params.put (new Integer (2), new Double (10D));
    List results = (List) query.executeWithMap (params);
    for (Iterator itr = results.iterator (); itr.hasNext ();)
        processMagazine ((Magazine) itr.next ());
    query.close (results);

Prev | Up | Next
Chapter 17. SQL Queries | Home | 17.3. SQL Projections