docs/1.2/parameterized-queries.html

Title: Parameterized Queries

	

<P>SelectQuery objects can be rather complex. They may contain long qualifiers and lots of tuning parameters. Parameterized Queries feature addresses reusability of complex queries. With this feature, for each group of queries that differ only in the values used in the qualifier, a developer may create a single shared &quot;prototype&quot; or &quot;template&quot; query, and use it later as a factory for other queries. All settings configured in the prototype object will be passed to the final queries. Qualifier of the prototype query may use named parameters that are substituted for real values when a final query is created from the prototype.</P>
<DIV class="panelMacro"><TABLE class="infoMacro"><COLGROUP><COL width="24"><COL></COLGROUP><TR><TD valign="top"><IMG src="http://cayenne.apache.org/docs/1.2/images/icons/emoticons/information.gif" width="16" height="16" align="absmiddle" alt="" border="0"></TD><TD>&quot;Prototype&quot; queries are normally created in CayenneModeler and stored in the DataMap XML file. &quot;Queries Stored in DataMap&quot; chapter describes how to use such templates. This chapter shows how to create them on the spot using the API calls.</TD></TR></TABLE></DIV>

<P>Building a prototype query using <TT>Expression.fromString(..)</TT>:</P>

<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java"><SPAN class="code-keyword">import</SPAN> org.objectstyle.cayenne.exp.Expression;
<SPAN class="code-keyword">import</SPAN> org.objectstyle.cayenne.query.SelectQuery;
...
<SPAN class="code-comment">// create a qualifier with two named parameters: <SPAN class="code-quote">&quot;pname&quot;</SPAN> and <SPAN class="code-quote">&quot;aname&quot;</SPAN>
</SPAN>Expression qual = Expression.fromString(<SPAN class="code-quote">&quot;paintingTitle = $pname or toArtist.artistName = $aname&quot;</SPAN>);

<SPAN class="code-comment">// build a query prototype of a query - simply another select query
</SPAN>SelectQuery proto = <SPAN class="code-keyword">new</SPAN> SelectQuery(Painting.class, qual);
proto.setDistinct(<SPAN class="code-keyword">true</SPAN>);
</PRE>
</DIV></DIV>

<P>Same example but using ExpressionFactory:</P>

<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java"><SPAN class="code-keyword">import</SPAN> java.util.*;
<SPAN class="code-keyword">import</SPAN> org.objectstyle.cayenne.exp.Expression;
<SPAN class="code-keyword">import</SPAN> org.objectstyle.cayenne.exp.ExpressionFactory;
<SPAN class="code-keyword">import</SPAN> org.objectstyle.cayenne.exp.ExpressionParameter;
<SPAN class="code-keyword">import</SPAN> org.objectstyle.cayenne.query.SelectQuery;
...
<SPAN class="code-comment">// create a qualifier with two named parameters: 
</SPAN><SPAN class="code-comment">//  <SPAN class="code-quote">&quot;pname&quot;</SPAN> and <SPAN class="code-quote">&quot;aname&quot;</SPAN>
</SPAN>List list = <SPAN class="code-keyword">new</SPAN> ArrayList();
list.add(ExpressionFactory.matchExp(<SPAN class="code-quote">&quot;paintingTitle&quot;</SPAN>, 
         <SPAN class="code-keyword">new</SPAN> ExpressionParameter(<SPAN class="code-quote">&quot;pname&quot;</SPAN>)));
list.add(ExpressionFactory.matchExp(<SPAN class="code-quote">&quot;toArtist.artistName&quot;</SPAN>, 
         <SPAN class="code-keyword">new</SPAN> ExpressionParameter(<SPAN class="code-quote">&quot;aname&quot;</SPAN>)));
Expression qual = ExpressionFactory.joinExp(Expression.OR, list);

<SPAN class="code-comment">// build a query prototype of a query - simply another select query
</SPAN>SelectQuery proto = <SPAN class="code-keyword">new</SPAN> SelectQuery(Painting.class, qual);
proto.setDistinct(<SPAN class="code-keyword">true</SPAN>);
</PRE>
</DIV></DIV>
<P>Prototype built in the example above can be used to create other queries. Relevalnt API is:</P>

<UL>
	<LI>public SelectQuery <B>SelectQuery.queryWithParameters</B>(Map parameters, boolean pruneMissing)<BR>
      Creates and returns a new SelectQuery using current query as a prototype. Map of parameters is used to substitute named parameters in the qualifier with the real values. Returned query is a separate instance and can be further customized without affecting the prototype. If <TT>pruneMissing</TT> is true and some of the named parameters are missing from the parameters map, Cayenne would prune expressions that can not be resolved. If pruneMissing is set to false, the method would throw an exception unless all the named parameters can be resolved.</LI>
	<LI>public SelectQuery <B>SelectQuery.queryWithParameters</B>(Map parameters)<BR>
      A shortcut for <TT>public SelectQuery queryWithParameters(Map parameters, true)</TT>.</LI>
</UL>


<P>Example of using queryWithParameters is shown below:</P>

<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java"><SPAN class="code-keyword">import</SPAN> java.util.Map;
<SPAN class="code-keyword">import</SPAN> java.util.HashMap;
<SPAN class="code-keyword">import</SPAN> org.objectstyle.cayenne.query.SelectQuery;
...
SelectQuery proto = ... <SPAN class="code-comment">// <SPAN class="code-keyword">this</SPAN> was built in the example above
</SPAN>
<SPAN class="code-comment">// create a query
</SPAN>Map params1 = <SPAN class="code-keyword">new</SPAN> HashMap();
params1.put(<SPAN class="code-quote">&quot;aname&quot;</SPAN>, <SPAN class="code-quote">&quot;Dali&quot;</SPAN>);
SelectQuery query1 = proto.queryWithParameters(params1);

<SPAN class="code-comment">// further customize returned query 
</SPAN><SPAN class="code-comment">// without affecting the prototype
</SPAN>query1.setFetchLimit(100);
... 
<SPAN class="code-comment">// create another query with a different set of parameters
</SPAN>Map params2 = <SPAN class="code-keyword">new</SPAN> HashMap();
params2.put(<SPAN class="code-quote">&quot;aname&quot;</SPAN>, <SPAN class="code-quote">&quot;Monet&quot;</SPAN>);
params2.put(<SPAN class="code-quote">&quot;pname&quot;</SPAN>, <SPAN class="code-quote">&quot;The Beach at Trouville&quot;</SPAN>);
SelectQuery query2 = proto.queryWithParameters(params2);
... 
</PRE>
</DIV></DIV>