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.
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.
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.
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.|
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(...):