docs/1.2/tutorial-selectquery.html

Title: Tutorial SelectQuery

	

<P>It was shown before how to <A href="tutorial-dataobjects.html" title="Tutorial DataObjects">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 similar to how the SQLTemplate <A href="tutorial-mapping-query.html" title="Tutorial Mapping Query">was mapped</A>, in this chapter however we'll show how to create it using Cayenne API.</P>

<P>We don't have too much data in the database yet, but we can still demonstrate the main principles:</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="codeHeader panelHeader" style="border-bottom-width: 1px;"><B>Main.java</B></DIV><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  QueryLogger: SELECT t0.NAME, t0.ARTIST_ID, t0.GALLERY_ID, t0.ID FROM PAINTING t0
INFO  QueryLogger: === returned 2 rows. - took 20 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="codeHeader panelHeader" style="border-bottom-width: 1px;"><B>Main.java</B></DIV><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  QueryLogger: SELECT t0.NAME, t0.ARTIST_ID, t0.GALLERY_ID, t0.ID FROM PAINTING t0 
                             WHERE UPPER(t0.NAME) LIKE UPPER(?) [bind: 'gi%']
INFO  QueryLogger: === returned 1 row. - took 28 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="codeHeader panelHeader" style="border-bottom-width: 1px;"><B>Main.java</B></DIV><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>INFO  QueryLogger: SELECT t0.NAME, t0.ARTIST_ID, t0.GALLERY_ID, t0.ID FROM PAINTING t0, ARTIST t1 
                             WHERE t0.ARTIST_ID = t1.ID AND (t1.DATE_OF_BIRTH &lt; ?) [bind: '1906-01-01 00:00:00.3']
INFO  QueryLogger: === returned 2 rows. - took 31 ms.</PRE>
</DIV></DIV>

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