cayenne-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From conflue...@apache.org
Subject [CONF] Apache Cayenne Documentation > Scripting SQLTemplate
Date Fri, 16 Oct 2009 20:53:00 GMT
<html>
<head>
    <base href="http://cwiki.apache.org/confluence">
            <link rel="stylesheet" href="/confluence/s/1519/1/1/_/styles/combined.css?spaceKey=CAYDOC&amp;forWysiwyg=true"
type="text/css">
    </head>
<body style="background-color: white" bgcolor="white">
<div id="pageContent">
<div id="notificationFormat">
<div class="wiki-content">
<div class="email">
     <h2><a href="http://cwiki.apache.org/confluence/display/CAYDOC/Scripting+SQLTemplate">Scripting
SQLTemplate</a></h2>
     <h4>Page <b>edited</b> by             <a href="http://cwiki.apache.org/confluence/display/~andrus">Andrus
Adamchik</a>
    </h4>
     
          <br/>
     <div class="notificationGreySide">
         <p>SQLTemplate's internal SQL string is a dynamic script that is processed
at runtime to generate PreparedStatement SQL code. Dynamic nature of SQLTemplate makes possible
a few important things - it allows to bind parameters on the fly; it provides a way to pass
extra information to Cayenne that is not included in the SQL text; it supports including/excluding
chunks of SQL depending on runtime parameters.</p>

<p>Scripting of SQL strings is done using <a href="http://velocity.apache.org" rel="nofollow">Apache
Velocity</a>. Velocity was chosen primarily for its concise template language (no XML
tags within SQL!) that doesn't conflict with the SQL syntax. When creating dynamic SQL template,
all standard Velocity directives are available, including <tt>#set</tt>, <tt>#foreach</tt>,
<tt>#if</tt>. However due to the nature of the SQL and the need to integrate it
with Cayenne runtime, only a few Cayenne custom directives are normally used. These directives
(<tt>#bind</tt>..., <tt>#result</tt>, <tt>#chain</tt>,
<tt>#chunk</tt>) are described below.</p>
<div class='panelMacro'><table class='noteMacro'><colgroup><col width='24'><col></colgroup><tr><td
valign='top'><img src="/confluence/images/icons/emoticons/warning.gif" width="16" height="16"
align="absmiddle" alt="" border="0"></td><td><b>Directive Syntax Note</b><br
/>Velocity directives start with pound sign (#) and have their parameters separated by
space, not comma. E.g. <tt>#bind('SOMESTRING' 'VARCHAR')</tt>.</td></tr></table></div>

<h3><a name="ScriptingSQLTemplate-NamedParameters"></a>Named Parameters</h3>
<p><tt>SQLTemplate.setParameters(java.util.Map)</tt> allows setting a number
of named parameters that are used to build parts of the query. During template processing
by Velocity all keys in the parameters map are available as variables. For example if the
map contains a key "name", its value can be referenced as "$name" in the template. Value of
the parameter will be inserted in the SQL unmodified:</p>

<h5><a name="ScriptingSQLTemplate-NamedParametersExample"></a>Named Parameters
Example</h5>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">
<span class="code-comment">// build SQLTemplate
</span><span class="code-object">String</span> sql = <span class="code-quote">"delete
from $tableName"</span>;
SQLTemplate delete = <span class="code-keyword">new</span> SQLTemplate(Artist.class,
sql);
...
<span class="code-comment">// <span class="code-keyword">this</span> will
create a query <span class="code-quote">"delete from ARTIST"</span>
</span>delete.setParameters(Collections.singletonMap(<span class="code-quote">"tableName"</span>,
<span class="code-quote">"ARTIST"</span>));
...
<span class="code-comment">// <span class="code-keyword">this</span> will
create a query <span class="code-quote">"delete from PAINTING"</span>
</span>delete.setParameters(Collections.singletonMap(<span class="code-quote">"tableName"</span>,
<span class="code-quote">"PAINTING"</span>));
</pre>
</div></div>

<h3><a name="ScriptingSQLTemplate-DescribingtheResults%23resultDirective"></a>Describing
the Results - #result Directive</h3>

<p><tt>#result</tt> directive is used in selecting SQLTemplates to quickly
map an arbitrary ResultSet to a DataObject (or a data row with known keys), and also to control
Java types of result values. #result directive has a variable number of arguments:</p>

<ul>
	<li><tt>#result(columnName)</tt> - e.g. <tt>#result('ARTIST_NAME')</tt></li>
	<li><tt>#result(columnName javaType)</tt> - e.g. <tt>#result('DATE_OF_BIRTH'
'java.util.Date')</tt></li>
	<li><tt>#result(columnName javaType columnAlias)</tt> - e.g. <tt>#result('DATE_OF_BIRTH'
'java.util.Date' 'DOB')</tt> - in this case returned data row will use "DOB" instead
of "DATE_OF_BIRTH" for the result value.</li>
</ul>


<div class='panelMacro'><table class='noteMacro'><colgroup><col width='24'><col></colgroup><tr><td
valign='top'><img src="/confluence/images/icons/emoticons/warning.gif" width="16" height="16"
align="absmiddle" alt="" border="0"></td><td>Generally "javaType" argument
is a fully-qualified Java class name for a given result column. However for simplicity most
common Java types used in JDBC can be specified without a package. These include all numeric
types, primitives, String, SQL dates, BigDecimal and BigInteger. So "#result('A' 'String')",
"#result('B' 'java.lang.String')" and "#result('C' 'int')" are all valid.</td></tr></table></div>

<p>While "select * from" queries may work just fine, in many cases it is a good idea
to explicitly describe results.</p>

<h5><a name="ScriptingSQLTemplate-%23resultExamples"></a>#result Examples</h5>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">
<span class="code-comment">// build selecting SQLTemplate
</span><span class="code-object">String</span> sql = <span class="code-quote">"SELECT"</span>
   + <span class="code-quote">" #result('ARTIST_ID' '<span class="code-object">int</span>'),"</span>
   + <span class="code-quote">" #result('ARTIST_NAME' '<span class="code-object">String</span>'),"</span>
   + <span class="code-quote">" #result('DATE_OF_BIRTH' 'java.util.Date')"</span>
   + <span class="code-quote">" FROM ARTIST"</span>;
SQLTemplate select = <span class="code-keyword">new</span> SQLTemplate(Artist.class,
sql);
...
DataContext context...;
List artists = context.performQuery(select);
</pre>
</div></div>

<p>Note that it is possible to mix columns described via #result() with regular columns.
Columns without an explicit #result() directive will be mapped automatically using JDBC metadata.
 E.g.:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">
<span class="code-comment">// build selecting SQLTemplate
</span><span class="code-object">String</span> sql = <span class="code-quote">"SELECT"</span>
   + <span class="code-quote">" #result('ARTIST_ID' '<span class="code-object">long</span>'),"</span>
   + <span class="code-quote">" ARTIST_NAME, DATE_OF_BIRTH FROM ARTIST"</span>;</pre>
</div></div>

<h3><a name="ScriptingSQLTemplate-BindingParameters%23bindDirective"></a>Binding
Parameters - #bind Directive</h3>
<p>SQLTemplate uses <tt>#bind</tt> directive to indicate value binding.
It has the same meaning as PreparedStatement question mark ("?"), however it also tells Cayenne
about the nature of the bound value, so it should be used for all bindings. <tt>#bind()</tt>
directive can have a variable number of arguments. The following are the valid invocation
formats:</p>

<ul>
	<li><tt>#bind(value)</tt> - e.g. <tt>#bind($xyz)</tt> or <tt>#bind('somestring')</tt></li>
	<li><tt>#bind(value jdbcTypeName)</tt> - e.g. <tt>#bind($xyz 'VARCHAR')</tt>.
Second argument is the name of JDBC type for this binding. Valid JDBC types are defined in
java.sql.Types class. This form is the the most common and useful. It is generally preferred
to the single argument form, as it explicitly tells what type of JDBC value this binding is.</li>
	<li><tt>#bind(value jdbcTypeName scale)</tt> - e.g. <tt>#bind($xyz
'DECIMAL' 2)</tt></li>
</ul>


<h5><a name="ScriptingSQLTemplate-%23binddirectiveexample"></a>#bind directive
example</h5>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">
<span class="code-comment">// build SQLTemplate
</span><span class="code-object">String</span> sql = <span class="code-quote">"update
ARTIST set ARTIST_NAME = #bind($name) where ARTIST_ID = #bind($id)"</span>;
SQLTemplate update = <span class="code-keyword">new</span> SQLTemplate(Artist.class,
sql);
...
<span class="code-comment">// set parameters and run it...
</span>Map parameters = <span class="code-keyword">new</span> HashMap();
parameters.put(<span class="code-quote">"name"</span>, <span class="code-quote">"Publo
Picasso"</span>);
parameters.put(<span class="code-quote">"id"</span>, <span class="code-keyword">new</span>
<span class="code-object">Integer</span>(1001));
update.setParameters(parameters);

DataContext context...;
context.performNonSelectingQuery(update);
</pre>
</div></div>

<p>SQLTemplate also supports binding Collections for building <tt>IN ( .. )</tt>
queries. In any of the <tt>#bind</tt> invocation formats above, you may specify
a Collection of values in place of <tt>value</tt>, and Cayenne will automatically
expand it.</p>

<h5><a name="ScriptingSQLTemplate-Collectionexample"></a>Collection example</h5>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">
<span class="code-comment">// build SQLTemplate
</span><span class="code-object">String</span> sql = "SELECT ARTIST_ID,
ARTIST_NAME FROM ARTIST WHERE ARTIST_NAME IN (#bind($names))
SQLTemplate select = <span class="code-keyword">new</span> SQLTemplate(Artist.class,
sql);
...
<span class="code-comment">// set parameters and run it...
</span>Map parameters = <span class="code-keyword">new</span> HashMap();
parameters.put(<span class="code-quote">"names"</span>, Arrays.asList(<span
class="code-keyword">new</span> <span class="code-object">String</span>[]
{ <span class="code-quote">"Monet"</span>, <span class="code-quote">"Publo
Picasso"</span>}));
select.setParameters(parameters);

DataContext context...;
List artists = context.performQuery(select);
</pre>
</div></div>


<h3><a name="ScriptingSQLTemplate-NullValuesinBindings%23bindEqualand%23bindNotEqualDirectives"></a>Null
Values in Bindings - #bindEqual and #bindNotEqual Directives</h3>
<p>Sometimes when a parameter is NULL, SQL code has to be changed. For example, instead
of "WHERE COLUMN = ?", PreparedStatement should be rewritten as "WHERE COLUMN IS NULL", and
instead of "WHERE COLUMN &lt;&gt; ?" - as "WHERE COLUMN IS NOT NULL". <tt>#bindEqual</tt>
and <tt>#bindNotEqual</tt> directives are used to dynamically generate correct
SQL string in this case. Their semantics is the same as #bind directive above, except that
they do not require "=", "!=" or "&lt;&gt;" in front of them:</p>

<ul>
	<li><tt>#bindEqual(value)</tt>, <tt>#bindNotEqual(value)</tt></li>
	<li><tt>#bindEqual(value jdbcTypeName)</tt>, <tt>#bindNotEqual(value
jdbcTypeName)</tt></li>
	<li><tt>#bindEqual(value jdbcTypeName scale)</tt>, <tt>#bindNotEqual(value
jdbcTypeName scale)</tt></li>
</ul>


<h5><a name="ScriptingSQLTemplate-NullValueExamples"></a>Null Value Examples</h5>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">
<span class="code-comment">// build SQLTemplate
</span><span class="code-comment">// note that <span class="code-quote">"="</span>
is ommitted <span class="code-keyword">for</span> the second binding, since it
is a part of the directive
</span><span class="code-object">String</span> sql = "update ARTIST set
ARTIST_NAME = #bind($name) where ARTIST_ID #bindEqual($id);
SQLTemplate update = <span class="code-keyword">new</span> SQLTemplate(Artist.class,
sql);
...
<span class="code-comment">// set parameters and run it...
</span>Map parameters = <span class="code-keyword">new</span> HashMap();
parameters.put(<span class="code-quote">"name"</span>, <span class="code-quote">"Publo
Picasso"</span>);
parameters.put(<span class="code-quote">"id"</span>, <span class="code-keyword">new</span>
<span class="code-object">Integer</span>(1001));
update.setParameters(parameters);

DataContext context...;

<span class="code-comment">// after binding processing PrepapredStatement SQL will look
like 
</span><span class="code-comment">// <span class="code-quote">"update ARTIST
set ARTIST_NAME = ? where ARTIST_ID = ?"</span>
</span>context.performNonSelectingQuery(update);
</pre>
</div></div>

<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">
<span class="code-comment">// build SQLTemplate
</span><span class="code-comment">// note that <span class="code-quote">"!="</span>
is omitted <span class="code-keyword">for</span> the second binding, since it
is a part of the directive
</span><span class="code-object">String</span> sql = <span class="code-quote">"update
ARTIST set ARTIST_NAME = #bind($name) where ARTIST_ID #bindNotEqual($id)"</span>;
SQLTemplate update = <span class="code-keyword">new</span> SQLTemplate(Artist.class,
sql);
...
<span class="code-comment">// set parameters and run it...
</span>Map parameters = <span class="code-keyword">new</span> HashMap();
parameters.put(<span class="code-quote">"name"</span>, <span class="code-quote">"Publo
Picasso"</span>);
parameters.put(<span class="code-quote">"id"</span>, <span class="code-keyword">null</span>);
update.setParameters(parameters);

DataContext context...;

<span class="code-comment">// after binding processing PrepapredStatement SQL will look
like 
</span><span class="code-comment">// <span class="code-quote">"update ARTIST
set ARTIST_NAME = ? where ARTIST_ID IS NOT NULL"</span>
</span>context.performNonSelectingQuery(update);
</pre>
</div></div>

<h3><a name="ScriptingSQLTemplate-BindingPersistentObjectValues%23bindObjectEqualand%23bindObjectNotEqualdirectives"></a>Binding
Persistent Object Values - #bindObjectEqual and #bindObjectNotEqual directives</h3>

<p>It can be tricky to use a Persistent object (or an ObjectId) in a binding, especially
for tables with compound primary keys. There are two directives to help with that - <tt>#bindObjectEqual</tt>
and <tt>#bindObjectNotEqual</tt>. Long explicit form of these directives is the
following:</p>

<ul>
	<li><tt>#bindObjectEqual(object columns idColumns)</tt></li>
	<li><tt>#bindObjectNotEqual(object columns idColumns)</tt></li>
</ul>


<p>An <tt>"object"</tt> argument can be one of Persistent, ObjectId or Map.
<tt>"columns"</tt> and <tt>"idColumns"</tt> can be of type Object[],
Collection or Object. What these directives do is build the SQL to match <tt>"columns"</tt>
(i.e. the columns from the SQL query) against <tt>"idColumns"</tt> (i.e. the names
of the PK columns for a given entity) for a given object. E.g.:</p>

<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java"><span class="code-object">String</span> sql = <span
class="code-quote">"SELECT * FROM PAINTING t0"</span>
                + <span class="code-quote">" WHERE #bindObjectEqual($a 't0.ARTIST_ID'
'ARTIST_ID' ) ORDER BY PAINTING_ID"</span>;
SQLTemplate select = <span class="code-keyword">new</span> SQLTemplate(Artist.class,
sql);

Artist a = ....
select.setParameters(Collections.singletonMap(<span class="code-quote">"a"</span>,
a));</pre>
</div></div>

<p>In case of compound PK, arrays can be used for the last two parameters:</p>

<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java"><span class="code-object">String</span> sql = <span
class="code-quote">"SELECT * FROM TABLE_X t0"</span>
                + <span class="code-quote">" WHERE #bindObjectEqual($a ['t0.FK1', 't0.FK2']
['PK1', 'PK2'] )"</span>;
SQLTemplate select = <span class="code-keyword">new</span> SQLTemplate(TableX.class,
sql);</pre>
</div></div>

<p>In the case when SQL columns have the same names as PK columns, and there's no naming
conflict that would force to use fully qualified column names, a short form of these directives
can be used, where column names are inferred from the ObjectId:</p>

<ul>
	<li><tt>#bindObjectEqual(object)</tt></li>
	<li><tt>#bindObjectNotEqual(object)</tt></li>
</ul>


<p>E.g.:</p>

<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java"><span class="code-object">String</span> sql = <span
class="code-quote">"SELECT * FROM PAINTING WHERE #bindObjectEqual($a) ORDER BY PAINTING_ID"</span>;
SQLTemplate select = <span class="code-keyword">new</span> SQLTemplate(Artist.class,
sql);</pre>
</div></div>

<h3><a name="ScriptingSQLTemplate-BuildingDynamicSQL%23chainand%23chunkDirectives"></a>Building
Dynamic SQL - #chain and #chunk Directives</h3>
<p>Often it is desirable to exclude parts of the WHERE clause if some parameters are
null or not set. This task is not trivial considering the semantics of a SQL statement. Consider
this fairly simple example:</p>

<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">
<span class="code-object">String</span> sql = <span class="code-quote">"SELECT
DISTINCT"</span>
   + <span class="code-quote">" #result('ARTIST_ID' '<span class="code-object">int</span>'),"</span>
   + <span class="code-quote">" #result('ARTIST_NAME' '<span class="code-object">String</span>'),"</span>
   + <span class="code-quote">" #result('DATE_OF_BIRTH' 'java.util.Date')"</span>
   + <span class="code-quote">" FROM ARTIST t0"</span>
   + <span class="code-quote">" WHERE ARTIST_NAME LIKE #bind($name)"</span>
   + <span class="code-quote">" OR ARTIST_ID &gt; #bind($id)"</span>;
SQLTemplate select = <span class="code-keyword">new</span> SQLTemplate(Artist.class,
sql);
</pre>
</div></div>

<p>It would be nice to exclude ARTIST_NAME matching if "name" parameter is null, exclude
ARTIST_ID matching if "id" is null, and exclude the whole WHERE clause if both are null. <tt>#chain</tt>
and <tt>#chunk</tt> directives are used for this purpose. Each logical piece is
wrapped in a conditional "chunk", and a number of chunks are grouped in a chain. If chain
contains no chunks it doesn't render anything enclosed in it.</p>

<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">
<span class="code-object">String</span> sql = <span class="code-quote">"SELECT
DISTINCT"</span>
   + <span class="code-quote">" #result('ARTIST_ID' '<span class="code-object">int</span>'),"</span>
   + <span class="code-quote">" #result('ARTIST_NAME' '<span class="code-object">String</span>'),"</span>
   + <span class="code-quote">" #result('DATE_OF_BIRTH' 'java.util.Date')"</span>
   + <span class="code-quote">" FROM ARTIST t0"</span>
   + " #chain('OR' 'WHERE')                              <span class="code-comment">//
start chain prefixed by WHERE, 
</span>                                                         <span class="code-comment">//
and joined by OR
</span>   + <span class="code-quote">" #chunk($name) ARTIST_NAME LIKE #bind($name)
#end"</span> <span class="code-comment">// ARTIST_NAME <span class="code-quote">"chunk"</span>
</span>   + <span class="code-quote">" #chunk($id) ARTIST_ID &gt; #bind($id)
#end"</span>          <span class="code-comment">// ARTIST_ID <span class="code-quote">"chunk"</span>
</span>   + <span class="code-quote">" #end"</span>;                   
                        <span class="code-comment">// end of chain
</span>SQLTemplate select = <span class="code-keyword">new</span> SQLTemplate(Artist.class,
sql);
</pre>
</div></div>

     </div>
     <div id="commentsSection" class="wiki-content pageSection">
       <div style="float: right;">
            <a href="http://cwiki.apache.org/confluence/users/viewnotifications.action"
class="grey">Change Notification Preferences</a>
       </div>

       <a href="http://cwiki.apache.org/confluence/display/CAYDOC/Scripting+SQLTemplate">View
Online</a>
       |
       <a href="http://cwiki.apache.org/confluence/pages/diffpagesbyversion.action?pageId=10524&revisedVersion=9&originalVersion=8">View
Change</a>
              |
       <a href="http://cwiki.apache.org/confluence/display/CAYDOC/Scripting+SQLTemplate?showComments=true&amp;showCommentArea=true#addcomment">Add
Comment</a>
            </div>
</div>
</div>
</div>
</div>
</body>
</html>

Mime
View raw message