docs/3.0/tutorial-selectquery.html

Title: Tutorial SelectQuery

<h2>Tutorial SelectQuery</h2>

	<P>This section shows how to select objects from the database using SelectQuery. </P>

<H3>Introducing SelectQuery</H3>

<P>It was shown before how to <A href="tutorial-persistent-objects.html" title="Tutorial Persistent Objects">persist new objects</A>. Cayenne <A href="queries.html" title="Queries">queries</A> are used to access already saved objects. The primary query type used for selecting objects is <A href="selectquery.html" title="SelectQuery">SelectQuery</A>. It can be mapped in CayenneModeler or created via the API. We'll use the later approach in this section. We don't have too much data in the database yet, but we can still demonstrate the main principles below.</P>

<UL>
	<LI>Select all paintings (the code, and the log output it generates):</LI>
</UL>


<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">SelectQuery select1 = <SPAN class="code-keyword">new</SPAN> SelectQuery(Painting.class);
List paintings1 = context.performQuery(select1);</PRE>
</DIV></DIV>

<DIV class="preformatted panel" style="border-width: 1px;"><DIV class="preformattedContent panelContent">
<PRE>INFO: SELECT t0.GALLERY_ID, t0.ARTIST_ID, t0.NAME, t0.ID FROM PAINTING t0
INFO: === returned 2 rows. - took 18 ms.</PRE>
</DIV></DIV>

<UL>
	<LI>Select paintings that start with &quot;gi&quot;, ignoring case (read more about qualifier Expressions and ExpressionFactory <A href="expressions.html" title="Expressions">here</A>:</LI>
</UL>


<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">Expression qualifier2 = ExpressionFactory.likeIgnoreCaseExp(
                Painting.NAME_PROPERTY,
                <SPAN class="code-quote">&quot;gi%&quot;</SPAN>);
SelectQuery select2 = <SPAN class="code-keyword">new</SPAN> SelectQuery(Painting.class, qualifier2);
List paintings2 = context.performQuery(select2);</PRE>
</DIV></DIV>

<DIV class="preformatted panel" style="border-width: 1px;"><DIV class="preformattedContent panelContent">
<PRE>INFO: SELECT t0.GALLERY_ID, t0.ARTIST_ID, t0.NAME, t0.ID FROM PAINTING t0 
WHERE UPPER(t0.NAME) LIKE UPPER(?) [bind: 1-&gt;NAME:'gi%']
INFO: === returned 1 row. - took 12 ms.</PRE>
</DIV></DIV>

<UL>
	<LI>Select all paintings done by artists who were born more than a 100 years ago (demonstrating using Expression.fromString(..) instead of ExpressionFactory):</LI>
</UL>


<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">Calendar c = <SPAN class="code-keyword">new</SPAN> GregorianCalendar();
c.set(c.get(Calendar.YEAR) - 100, 0, 1, 0, 0, 0);

Expression qualifier3 = Expression.fromString(<SPAN class="code-quote">&quot;artist.dateOfBirth &lt; $date&quot;</SPAN>);
qualifier3 = qualifier3.expWithParameters(Collections.singletonMap(<SPAN class="code-quote">&quot;date&quot;</SPAN>, c.getTime()));
SelectQuery select3 = <SPAN class="code-keyword">new</SPAN> SelectQuery(Painting.class, qualifier3);
List paintings3 = context.performQuery(select3);</PRE>
</DIV></DIV>

<DIV class="preformatted panel" style="border-width: 1px;"><DIV class="preformattedContent panelContent">
<PRE>SELECT t0.GALLERY_ID, t0.ARTIST_ID, t0.NAME, t0.ID FROM PAINTING t0 
JOIN ARTIST t1 ON (t0.ARTIST_ID = t1.ID) WHERE t1.DATE_OF_BIRTH &lt; ? 
[bind: 1-&gt;DATE_OF_BIRTH:'1909-01-01 00:00:00.378']
INFO: === returned 2 rows. - took 19 ms.</PRE>
</DIV></DIV>

<HR>
<P><B>Next Step: <A href="tutorial-delete.html" title="Tutorial Delete">Tutorial Delete</A></B></P>
<HR>