Controlling Result DataRow Caps

(available since 3.0)

Queries like "SELECT * FROM..." and even "SELECT COLUMN1, COLUMN2, ... FROM ..." can sometimes result in Cayenne exceptions on attempts to convert fetched DataRows to objects. Essentially any query that is not using a #result directive to describe the result set is prone to this problem, as different databases may produce different capitalization of the java.sql.ResultSet columns.

The most universal way to address this issue is to describe each column explicitly in the SQLTemplate via #result directive, as mentioned above: "SELECT #result('column1'), #result('column2'), ..". However this becomes unpractical for the tables with lots of columns. For such cases Cayenne provides a shortcut based on the fact that normally an ORM mapping follows some naming convention for the column names. Simply put, for case-insensitive databases developers normally use either all lowercase or all uppercase column names.

Here is the API to force Cayenne to follow the naming convention (also available as a dropdown in the Modeler). Note that you shouldn't bother with this unless you are getting CayenneRuntimeExceptions when fetching with SQLTemplate.

SQLTemplate query = new SQLTemplate("SELECT * FROM ARTIST");
query.setColumnNamesCapitalization(CapsStrategy.LOWER);
List objects = context.performQuery(query);

or 

SQLTemplate query = new SQLTemplate("SELECT * FROM ARTIST");
query.setColumnNamesCapitalization(CapsStrategy.UPPER);
List objects = context.performQuery(query);

SQLTemplate and Prefetching

Prefetching allows to fetch related objects of different kinds in a single query run. SQLTemplate uses "joint" prefetching. However Cayenne expects the user to provide special naming of the result columns to be able to instantiate persistent objects from the result set. Columns belonging to the "root" entity of the query should use unqualified names corresponding to the root DbEntity columns, for each related entity column names must be prefixed with relationship name and a dot (e.g. "toArtist.ID"). Column naming can be controlled with "#result" directive. E.g.:

SQLTemplate q = new SQLTemplate(
                Artist.class,
                "SELECT distinct "
                        + "#result('ESTIMATED_PRICE' 'BigDecimal' '' 'paintingArray.ESTIMATED_PRICE'), "
                        + "#result('PAINTING_TITLE' 'String' '' 'paintingArray.PAINTING_TITLE'), "
                        + "#result('GALLERY_ID' 'int' '' 'paintingArray.GALLERY_ID'), "
                        + "#result('PAINTING_ID' 'int' '' 'paintingArray.PAINTING_ID'), "
                        + "#result('ARTIST_NAME' 'String'), "
                        + "#result('DATE_OF_BIRTH' 'java.util.Date'), "
                        + "#result('t0.ARTIST_ID' 'int' '' 'ARTIST_ID') "
                        + "FROM ARTIST t0, PAINTING t1 "
                        + "WHERE t0.ARTIST_ID = t1.ARTIST_ID");
q.addPrefetch("paintingArray")
List objects = context.performQuery(query);

Note that a call to "addPrefetch" is still needed to tell Cayenne which relationships are prefetched.