Not all features of SQL can (or should) be mapped in the object model in terms of DataObjects. For such cases Cayenne provides a powerful mechanism for running both selecting and non-selecting SQL using scriptable SQLTemplate query. This chapter describes how simple SQLTemplates can be created and executed with DataContext to select or update the database data. Subsequent chapters show how to build SQL dynamically, bind parameters and describe result sets using SQLTemplate scripting features.

Creating SQLTemplate

SQLTemplates can be built using CayenneModeler. Here we demonstrate how to do the same thing via API. SQLTemplate consists of root and dynamic template string. Dynamic behavior of the template is discussed in subsequent chapters, for now it is sufficient to know that the template string is simply a valid SQL statement in the target database SQL dialect.

import org.apache.cayenne.query.SQLTemplate;
...
// create selecting SQLTemplate
SQLTemplate selectQuery = new SQLTemplate(Artist.class, "select * from ARTIST");
...
// create updating SQLTemplate
SQLTemplate updateQuery = new SQLTemplate(Artist.class, "delete from ARTIST");

Selecting Objects with SQLTemplate

Selecting SQLTemplate is very similar to SelectQuery in many respects. It can be executed via DataContext.performQuery(..) and supports the same configuration parameters, such as fetch limit, pagination, etc. It can be configured to return DataObjects (default) or data rows.

import org.apache.cayenne.query.SQLTemplate;
...
// fetch all artists, but no more than 1000 objects...
SQLTemplate rawSelect = new SQLTemplate(Artist.class, "select * from ARTIST");
rawSelect.setFetchLimit(1000);

List artists = dataContext.performQuery(rawSelect);

Modifying Data with SQLTemplate

Non-selecting SQLTemplate allows to execute arbitrary SQL that modifies the database, but does not return the results. DataContext.performNonSelectingQuery(..) is used for this task.

When changing or deleting data via SQLTemplate you must realize that such changes are done directly to the database, bypassing the context, and therefore may potentially leave object graph in an inconsistent state.
import org.apache.cayenne.query.SQLTemplate;
...
// fetch all artists, but no more than 1000 objects...
SQLTemplate rawDelete = new SQLTemplate(Artist.class, "delete from ARTIST");
int[] deleteCounts = dataContext.performNonSelectingQuery(rawDelete);

Customizing SQL Dialects

Even though SQL is an industry standard, different DB vendors still have their own dialects and extensions. Two versions of the same query written for Oracle and PostgreSQL may look quiet different.

Each SQLTemplate query has a default template, usually set in constructor. Internally it also keeps a map of alternative templates. This map normally uses a fully-qualified class name of the target DbAdapter as a key. This way Cayenne can determine which one of the SQL strings to use during the execution. Alternative SQL strings are configured using SQLTemplate.setTemplate(...):

// build template with default SQL
SQLTemplate query = new SQLTemplate(Artist.class, "select * from ARTIST");

// for Postgres it would be nice to trim the CHAR ARTIST_NAME column
// or otherwise it will be returned padded with spaces
String pgTemplate = "SELECT ARTIST_ID, RTRIM(ARTIST_NAME), DATE_OF_BIRTH FROM ARTIST";
query.setTemplate(PostgresAdapter.class.getName(), pgTemplate);