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 Objects. 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);