docs/3.0/sqltemplate-result-mapping.html

Title: SQLTemplate Result Mapping

	<P>This chapter pertains to selecting SQLTemplates that fetch a single result set. By default the returned result is a List of DataRows or Persistent objects, depending on how the query was configured. However SQLTemplate is much more powerful and can be set up to fetch  lists of scalars, and lists of Object[] instances with an arbitrary mix of objects and scalars.</P>

<H3><A name="SQLTemplateResultMapping-DefaultResults"></A>Default Results</H3>

<P>To get either DataObjects or DataRows, not much configuration is needed:</P>

<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">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">// List of Artist
</SPAN>List artists = context.performQuery(query);</PRE>
</DIV></DIV>

<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">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">// Force DataRows
</SPAN>query.setFetchingDataRows(<SPAN class="code-keyword">true</SPAN>);

<SPAN class="code-comment">// List of DataRow
</SPAN>List rows = context.performQuery(query);</PRE>
</DIV></DIV>

<H3><A name="SQLTemplateResultMapping-ScalarResults"></A>Scalar Results</H3>

<P>To select a list of scalar values, you will need to use a <TT>SQLResult</TT> class to tell Cayenne how to deal with it:</P>

<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">SQLTemplate query = <SPAN class="code-keyword">new</SPAN> SQLTemplate(Painting.class, <SPAN class="code-quote">&quot;SELECT ESTIMATED_PRICE P FROM PAINTING&quot;</SPAN>);

<SPAN class="code-comment">// *** let Cayenne know that result is a scalar
</SPAN>SQLResult resultDescriptor = <SPAN class="code-keyword">new</SPAN> SQLResult();
resultDescriptor.addColumnResult(<SPAN class="code-quote">&quot;P&quot;</SPAN>);
query.setResult(resultDescriptor);

<SPAN class="code-comment">// List of <SPAN class="code-object">Number</SPAN>'s
</SPAN>List prices = context.performQuery(query);</PRE>
</DIV></DIV>

<P>Or if the query nature guarantees only a single row in the result (aggregate query), do this:</P>

<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">SQLTemplate query = <SPAN class="code-keyword">new</SPAN> SQLTemplate(Painting.class, <SPAN class="code-quote">&quot;SELECT SUM(ESTIMATED_PRICE) S FROM PAINTING&quot;</SPAN>);

<SPAN class="code-comment">// *** let Cayenne know that result is a scalar
</SPAN>SQLResult resultDescriptor = <SPAN class="code-keyword">new</SPAN> SQLResult();
resultDescriptor.addColumnResult(<SPAN class="code-quote">&quot;S&quot;</SPAN>);
query.setResult(resultDescriptor);

<SPAN class="code-comment">// List of <SPAN class="code-object">Number</SPAN>'s
</SPAN><SPAN class="code-object">Number</SPAN> assetsValue = (<SPAN class="code-object">Number</SPAN>) DataObjectUtils.objectForQuery(context, query)</PRE>
</DIV></DIV>

<H3><A name="SQLTemplateResultMapping-MixedResults"></A>Mixed Results</H3>

<P><TT>SQLResult</TT> can be used to fetch a mix of objects and scalars. In this case the result will be <TT>Object[]</TT> that contains scalars and objects in the order they were configured in SQLResultSetMapping:</P>

<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">SQLTemplate query = <SPAN class="code-keyword">new</SPAN> SQLTemplate(Artist.class, <SPAN class="code-quote">&quot;SELECT t0.ARTIST_ID, t0.NAME, t0.DATE_OF_BIRTH, COUNT(t1.PAINTING_ID) C &quot;</SPAN> +
      <SPAN class="code-quote">&quot;FROM ARTIST t0 LEFT JOIN PAINTING t1 ON (t0.ARTIST_ID = t1.ARTIST_ID) &quot;</SPAN> +
      <SPAN class="code-quote">&quot;GROUP BY t0.ARTIST_ID, t0.NAME, t0.DATE_OF_BIRTH&quot;</SPAN>);

<SPAN class="code-comment">// *** let Cayenne know that result is a mix of Artist objects and the count of their paintings
</SPAN>EntityResult artistResult = <SPAN class="code-keyword">new</SPAN> EntityResult(Artist.class);
artistResult.addDbField(Artist.ARTIST_ID_PK_COLUMN, <SPAN class="code-quote">&quot;ARTIST_ID&quot;</SPAN>);
artistResult.addObjectField(Artist.NAME_PROPERTY, <SPAN class="code-quote">&quot;NAME&quot;</SPAN>);
artistResult.addObjectField(Artist.DATE_OF_BIRTH_PROPERTY, <SPAN class="code-quote">&quot;DATE_OF_BIRTH&quot;</SPAN>);

SQLResult resultDescriptor = <SPAN class="code-keyword">new</SPAN> SQLResult();
resultDescriptor.addEntityResult(artistResult);
resultDescriptor.addColumnResult(<SPAN class="code-quote">&quot;C&quot;</SPAN>);
query.setResult(resultDescriptor);

<SPAN class="code-comment">// List of <SPAN class="code-object">Object</SPAN>[]
</SPAN><SPAN class="code-object">Number</SPAN> assetsValue = (<SPAN class="code-object">Number</SPAN>) DataObjectUtils.objectForQuery(context, query)</PRE>
</DIV></DIV>