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