| Title: Tutorial Mapping Query |
| |
| |
| |
| <P>Now that we've got a feel of how <A href="tutorial-datacontext.html" title="Tutorial DataContext">DataContext works</A> and how to <A href="tutorial-dataobjects.html" title="Tutorial DataObjects">create and save objects</A>, let's take a small diversion and implement a piece of code that will delete all data from the database every time the tutorial application is started. We'll learn how to map a raw SQL query with parameters using the modeler and then execute it in the code.</P> |
| |
| <UL> |
| <LI>Open CayenneModeler, go to <TT>"File > Recent Files"</TT> and choose the tutorial mapping file.</LI> |
| <LI>When the project opens, select the DataMap, and click on "Create Query" button (or select "<TT>Project > Create Query</TT>" from the menu).</LI> |
| <LI>For the query type select "Raw SQL" and click "Create".</LI> |
| <LI>For "Query Name" enter "DeleteAll" - this will be the key used to reference query later in the code:</LI> |
| </UL> |
| |
| |
| <P><SPAN class="image-wrap" style=""><IMG src="tutorial-mapping-query.data/sql-template.jpg" style="border: 0px solid black"></SPAN></P> |
| |
| <P>Ignore "Select Properties" settings as the query will not select anything. Rather it will delete all rows from the table with name specified as a parameter. </P> |
| |
| <UL> |
| <LI>Go to "SQL Scripts" tab and enter the following in the right panel:</LI> |
| </UL> |
| |
| |
| <DIV class="preformatted panel" style="border-width: 1px;"><DIV class="preformattedContent panelContent"> |
| <PRE>delete from $table</PRE> |
| </DIV></DIV> |
| |
| <P>Note that "$table" is a variable that will be dynamically substituted by Cayenne in runtime with the table name. (Detailed discussion of SQLTemplate scripting options is provided <A href="scripting-sqltemplate.html" title="Scripting SQLTemplate">here</A>).</P> |
| |
| <UL> |
| <LI>Query mapping is complete. Save the project in the Modeler and go back to Eclipse.</LI> |
| <LI>In Eclipse, refresh the mapping by right-clicking on cayenne-tutorial project and selecting "Refresh".</LI> |
| <LI>Now add the following code inserting it after the DataContext creation line and before any other code:</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">QueryChain chain = <SPAN class="code-keyword">new</SPAN> QueryChain(); |
| chain.addQuery(<SPAN class="code-keyword">new</SPAN> NamedQuery(<SPAN class="code-quote">"DeleteAll"</SPAN>, Collections.singletonMap( |
| <SPAN class="code-quote">"table"</SPAN>, |
| <SPAN class="code-quote">"PAINTING"</SPAN>))); |
| chain.addQuery(<SPAN class="code-keyword">new</SPAN> NamedQuery(<SPAN class="code-quote">"DeleteAll"</SPAN>, Collections.singletonMap( |
| <SPAN class="code-quote">"table"</SPAN>, |
| <SPAN class="code-quote">"ARTIST"</SPAN>))); |
| chain.addQuery(<SPAN class="code-keyword">new</SPAN> NamedQuery(<SPAN class="code-quote">"DeleteAll"</SPAN>, Collections.singletonMap( |
| <SPAN class="code-quote">"table"</SPAN>, |
| <SPAN class="code-quote">"GALLERY"</SPAN>))); |
| |
| context.performGenericQuery(chain);</PRE> |
| </DIV></DIV> |
| |
| <P>Note that since we need to run the same query three times with different sets of parameters, first we created a <TT><A href="querychain.html" title="QueryChain">QueryChain</A></TT> that can hold other queries. Then we added three <TT><A href="namedquery.html" title="NamedQuery">NamedQueries</A></TT> (the order is important - painting has to be deleted before artist or gallery due to the foreign key constraints). NamedQuery is a query that is a reference to another query mapped via CayenneModeler (in this case - a query called "DeleteAll" that we mapped above).</P> |
| |
| <P>Now we can re-run the application and see the following output in the beginning of the log:</P> |
| |
| <DIV class="preformatted panel" style="border-width: 1px;"><DIV class="preformattedContent panelContent"> |
| <PRE>INFO QueryLogger: delete from PAINTING |
| INFO QueryLogger: === updated 2 rows. |
| INFO QueryLogger: delete from ARTIST |
| INFO QueryLogger: === updated 1 row. |
| INFO QueryLogger: delete from GALLERY |
| INFO QueryLogger: === updated 1 row.</PRE> |
| </DIV></DIV> |
| |
| <P>Note that raw sql queries above (aka <A href="sqltemplate-query.html" title="SQLTemplate Query">SQLTemplates</A>) are run directly against the database bypassing Cayenne object layer. If instead of bulk delete you needed to delete individual objects, you should do so using <TT>DataContext.deleteObject(..)</TT> as discussed <A href="tutorial-delete.html" title="Tutorial Delete">here</A>.</P> |
| |
| <HR> |
| <P><B>Next Step: <A href="tutorial-selectquery.html" title="Tutorial SelectQuery">Tutorial SelectQuery</A></B></P> |
| <HR> |
