Title: SQLTemplate Result Mapping

This chapter pertains to selecting SQLTemplates that fetch a single result set. By default the returned result is a List of DataRows or Persistent objects, depending on how the query was configured. However SQLTemplate is much more powerful and can be set up to fetch lists of scalars, and lists of Object[] instances with an arbitrary mix of objects and scalars.

Default Results

To get either DataObjects or DataRows, not much configuration is needed:

SQLTemplate query = new SQLTemplate(Artist.class, "SELECT * FROM ARTIST");

// List of Artist
List artists = context.performQuery(query);
SQLTemplate query = new SQLTemplate(Artist.class, "SELECT * FROM ARTIST");

// Force DataRows
query.setFetchingDataRows(true);

// List of DataRow
List rows = context.performQuery(query);

Scalar Results

To select a list of scalar values, you will need to use a SQLResult class to tell Cayenne how to deal with it:

SQLTemplate query = new SQLTemplate(Painting.class, "SELECT ESTIMATED_PRICE P FROM PAINTING");

// *** let Cayenne know that result is a scalar
SQLResult resultDescriptor = new SQLResult();
resultDescriptor.addColumnResult("P");
query.setResult(resultDescriptor);

// List of Number's
List prices = context.performQuery(query);

Or if the query nature guarantees only a single row in the result (aggregate query), do this:

SQLTemplate query = new SQLTemplate(Painting.class, "SELECT SUM(ESTIMATED_PRICE) S FROM PAINTING");

// *** let Cayenne know that result is a scalar
SQLResult resultDescriptor = new SQLResult();
resultDescriptor.addColumnResult("S");
query.setResult(resultDescriptor);

// List of Number's
Number assetsValue = (Number) DataObjectUtils.objectForQuery(context, query)

Mixed Results

SQLResult can be used to fetch a mix of objects and scalars. In this case the result will be Object[] that contains scalars and objects in the order they were configured in SQLResultSetMapping:

SQLTemplate query = new SQLTemplate(Artist.class, "SELECT t0.ARTIST_ID, t0.NAME, t0.DATE_OF_BIRTH, COUNT(t1.PAINTING_ID) C " +
      "FROM ARTIST t0 LEFT JOIN PAINTING t1 ON (t0.ARTIST_ID = t1.ARTIST_ID) " +
      "GROUP BY t0.ARTIST_ID, t0.NAME, t0.DATE_OF_BIRTH");

// *** let Cayenne know that result is a mix of Artist objects and the count of their paintings
EntityResult artistResult = new EntityResult(Artist.class);
artistResult.addDbField(Artist.ARTIST_ID_PK_COLUMN, "ARTIST_ID");
artistResult.addObjectField(Artist.NAME_PROPERTY, "NAME");
artistResult.addObjectField(Artist.DATE_OF_BIRTH_PROPERTY, "DATE_OF_BIRTH");

SQLResult resultDescriptor = new SQLResult();
resultDescriptor.addEntityResult(artistResult);
resultDescriptor.addColumnResult("C");
query.setResult(resultDescriptor);

// List of Object[]
Number assetsValue = (Number) DataObjectUtils.objectForQuery(context, query)