docs/2.0/sqltemplate-query.html

Title: SQLTemplate Query

	<P>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.</P>

<H3><A name="SQLTemplateQuery-CreatingSQLTemplate"></A>Creating SQLTemplate</H3>

<P>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.</P>
<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">
<SPAN class="code-keyword">import</SPAN> org.apache.cayenne.query.SQLTemplate;
...
<SPAN class="code-comment">// create selecting SQLTemplate
</SPAN>SQLTemplate selectQuery = <SPAN class="code-keyword">new</SPAN> SQLTemplate(Artist.class, <SPAN class="code-quote">&quot;select * from ARTIST&quot;</SPAN>);
...
<SPAN class="code-comment">// create updating SQLTemplate
</SPAN>SQLTemplate updateQuery = <SPAN class="code-keyword">new</SPAN> SQLTemplate(Artist.class, <SPAN class="code-quote">&quot;delete from ARTIST&quot;</SPAN>);
</PRE>
</DIV></DIV>

<H3><A name="SQLTemplateQuery-SelectingObjectswithSQLTemplate"></A>Selecting Objects with SQLTemplate</H3>

<P>Selecting SQLTemplate is very similar to SelectQuery in many respects. It can be executed via <TT>DataContext.performQuery(..)</TT> and supports the same configuration parameters, such as fetch limit, pagination, etc. It can be configured to return DataObjects (default) or data rows.</P>
<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">
<SPAN class="code-keyword">import</SPAN> org.apache.cayenne.query.SQLTemplate;
...
<SPAN class="code-comment">// fetch all artists, but no more than 1000 objects...
</SPAN>SQLTemplate rawSelect = <SPAN class="code-keyword">new</SPAN> SQLTemplate(Artist.class, <SPAN class="code-quote">&quot;select * from ARTIST&quot;</SPAN>);
rawSelect.setFetchLimit(1000);

List artists = dataContext.performQuery(rawSelect);
</PRE>
</DIV></DIV>

<H3><A name="SQLTemplateQuery-ModifyingDatawithSQLTemplate"></A>Modifying Data with SQLTemplate</H3>

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

<DIV class="panelMacro"><TABLE class="noteMacro"><COLGROUP><COL width="24"><COL></COLGROUP><TR><TD valign="top"><IMG src="http://cayenne.apache.org/docs/1.2/images/icons/emoticons/warning.gif" width="16" height="16" align="absmiddle" alt="" border="0"></TD><TD>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.</TD></TR></TABLE></DIV>

<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">
<SPAN class="code-keyword">import</SPAN> org.apache.cayenne.query.SQLTemplate;
...
<SPAN class="code-comment">// fetch all artists, but no more than 1000 objects...
</SPAN>SQLTemplate rawDelete = <SPAN class="code-keyword">new</SPAN> SQLTemplate(Artist.class, <SPAN class="code-quote">&quot;delete from ARTIST&quot;</SPAN>);
<SPAN class="code-object">int</SPAN>[] deleteCounts = dataContext.performNonSelectingQuery(rawDelete);
</PRE>
</DIV></DIV>

<H3><A name="SQLTemplateQuery-CustomizingSQLDialects"></A>Customizing SQL Dialects</H3>

<P>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.</P>

<P>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 <TT>SQLTemplate.setTemplate(...)</TT>:</P>
<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">
<SPAN class="code-comment">// build template with <SPAN class="code-keyword">default</SPAN> SQL
</SPAN>SQLTemplate query = <SPAN class="code-keyword">new</SPAN> SQLTemplate(Artist.class, <SPAN class="code-quote">&quot;select * from ARTIST&quot;</SPAN>);

<SPAN class="code-comment">// <SPAN class="code-keyword">for</SPAN> Postgres it would be nice to trim the CHAR ARTIST_NAME column
</SPAN><SPAN class="code-comment">// or otherwise it will be returned padded with spaces
</SPAN><SPAN class="code-object">String</SPAN> pgTemplate = <SPAN class="code-quote">&quot;SELECT ARTIST_ID, RTRIM(ARTIST_NAME), DATE_OF_BIRTH FROM ARTIST&quot;</SPAN>;
query.setTemplate(PostgresAdapter.class.getName(), pgTemplate);
</PRE>
</DIV></DIV>