12.3. SQL Projections
Prev | Chapter 12. SQL Queries | Next

## 12.3. SQL Projections

SQL queries without a candidate class are treated as projections of column data. If you select a single column, the query returns a collection of `Object`s. If you select multiple columns, it returns a collection of `Object[]`s. In either case, each column value is obtained using the `java.sql.ResultSet.getObject` method. The following example demonstrates a query for the values of the `PK` and `VERSION` columns of all `MAGAZINE` table records, using the data model we defined in Section 12.2, “Retrieving Persistent Objects with SQL”.

Example 12.4. Column Projection

    Query query = pm.newQuery ("javax.jdo.query.SQL",
        "SELECT PK, VERSION FROM MAGAZINE");
    Collection results = (Collection) query.execute ();
    for (Iterator itr = results.iterator (); itr.hasNext ();)
    {
        Object[] data = (Object[]) results.next ();
        processPrimaryKeyAndVersion (data[0], data[1]);
    }
    query.close (results);

Notice that in the code above, we did not set a candidate class. Therefore, the query is treated as a projection.

Our discussion of JDOQL query result classes in Section 11.8, “Result Class” also applies to SQL queries. As with JDOQL queries, SQL queries can automatically pack their results into objects of a specified type. JDO uses the `java.sql.ResultSetMetaData.getColumnLabel` method to match each column alias to the result class' public fields and JavaBean setter methods. Here is a modification of our example above that packs the selected column values into JavaBean instances.

Example 12.5. Result Class

    public class Identity
    {
        private long id;
        private int versionNumber;

        public void setId (long id)
        {
            this.id = id;
        }

        public long getId ()
        {
            return id;
        }

        public void setVersionNumber (int versionNumber)
        {
            this.versionNumber = versionNumber;
        }

        public int getVersionNumber ()
        {
            return versionNumber;
        }
    }

    Query query = pm.newQuery ("javax.jdo.query.SQL",
        "SELECT PK AS id, VERSION AS versionNumber FROM MAGAZINE");
    query.setResultClass (Identity.class);
    Collection results = (Collection) query.execute ();
    for (Iterator itr = results.iterator (); itr.hasNext ();)
        processIdentity ((Identity) itr.next ());
    query.close (results);

Prev | Up | Next
12.2. Retrieving Persistent Objects with SQL | Home | 12.4. Named SQL Queries