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 > SQLTemplate Result Mapping
Date Sun, 07 Feb 2010 12:17: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/SQLTemplate+Result+Mapping">SQLTemplate
Result Mapping</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>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">"SELECT * FROM ARTIST"</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">"SELECT * FROM ARTIST"</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">"SELECT ESTIMATED_PRICE P FROM
PAINTING"</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">"P"</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">"SELECT SUM(ESTIMATED_PRICE) S
FROM PAINTING"</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">"S"</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">"SELECT t0.ARTIST_ID, t0.NAME, t0.DATE_OF_BIRTH,
COUNT(t1.PAINTING_ID) C "</span> +
      <span class="code-quote">"FROM ARTIST t0 LEFT JOIN PAINTING t1 ON (t0.ARTIST_ID
= t1.ARTIST_ID) "</span> +
      <span class="code-quote">"GROUP BY t0.ARTIST_ID, t0.NAME, t0.DATE_OF_BIRTH"</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">"ARTIST_ID"</span>);
artistResult.addObjectField(Artist.NAME_PROPERTY, <span class="code-quote">"NAME"</span>);
artistResult.addObjectField(Artist.DATE_OF_BIRTH_PROPERTY, <span class="code-quote">"DATE_OF_BIRTH"</span>);

SQLResult resultDescriptor = <span class="code-keyword">new</span> SQLResult();
rsMap.addEntityResult(artistResult);
rsMap.addColumnResult(<span class="code-quote">"C"</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>

     </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/SQLTemplate+Result+Mapping">View
Online</a>
       |
       <a href="http://cwiki.apache.org/confluence/pages/diffpagesbyversion.action?pageId=84829&revisedVersion=5&originalVersion=4">View
Change</a>
              |
       <a href="http://cwiki.apache.org/confluence/display/CAYDOC/SQLTemplate+Result+Mapping?showComments=true&amp;showCommentArea=true#addcomment">Add
Comment</a>
            </div>
</div>
</div>
</div>
</div>
</body>
</html>

Mime
View raw message