Cayenne User Documentation
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 SQLResultSetMapping 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
    SQLResultSetMapping rsMap = new SQLResultSetMapping();
    rsMap.addColumnResult("P");
    query.setResultSetMapping(rsMap);

    // 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
    SQLResultSetMapping rsMap = new SQLResultSetMapping();
    rsMap.addColumnResult("S");
    query.setResultSetMapping(rsMap);

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

### Mixed Results

SQLResultSetMapping 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");

    SQLResultSetMapping rsMap = new SQLResultSetMapping();
    rsMap.addEntityResult(artistResult);
    rsMap.addColumnResult("C");
    query.setResultSetMapping(rsMap);

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

.
Copyright ©2001-2008 Apache Software Foundation