src/main/site/content/docs/3.0/sqltemplate-basics.html

---
#  Licensed to the Apache Software Foundation (ASF) under one
#  or more contributor license agreements.  See the NOTICE file
#  distributed with this work for additional information
#  regarding copyright ownership.  The ASF licenses this file
#  to you under the Apache License, Version 2.0 (the
#  "License"); you may not use this file except in compliance
#  with the License.  You may obtain a copy of the License at
#
#    http://www.apache.org/licenses/LICENSE-2.0
#
#  Unless required by applicable law or agreed to in writing,
#  software distributed under the License is distributed on an
#  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
#  KIND, either express or implied.  See the License for the
#  specific language governing permissions and limitations
#  under the License.

title: SQLTemplate Basics
url: /docs/3.0/sqltemplate-basics.html
layout: docs_legacy
---

	<H3><A name="SQLTemplateBasics-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="SQLTemplateBasics-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="SQLTemplateBasics-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="/docs/3.0/images/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="SQLTemplateBasics-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>