Cayenne User Documentation
Advanced SQLTemplate

### 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 normall 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(SQLTemplate.LOWERCASE_COLUMN_NAMES);
    List objects = context.performQuery(query);

or

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

.
Copyright ©2001-2008 Apache Software Foundation