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

## 17.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 list of `Object`s. If you select multiple columns, it returns a list 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 `ISBN` and `VERS` columns of all `MAG` table records, using the data model we defined in Section 17.2, “Retrieving Persistent Objects with SQL”.

Example 17.5. Column Projection

    Query query = pm.newQuery ("javax.jdo.query.SQL",
        "SELECT ISBN, VERS FROM MAG");
    List results = (List) query.execute ();
    for (Iterator itr = results.iterator (); itr.hasNext ();)
    {
        Object[] data = (Object[]) results.next ();
        processISBNAndVersion (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 17.6. Result Class

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

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

        public String 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 ISBN AS id, VERS AS versionNumber FROM MAG");
    query.setResultClass (Identity.class);
    List results = (List) query.execute ();
    for (Iterator itr = results.iterator (); itr.hasNext ();)
        processIdentity ((Identity) itr.next ());
    query.close (results);

Prev | Up | Next
17.2. Retrieving Persistent Objects with SQL | Home | 17.4. Named SQL Queries