| Title: Building Expressions |
| |
| <P>The Expression class (<TT>org.apache.cayenne.exp.Expression</TT>) provides <TT>Expression.fromString(String)</TT> as a convenience method to create expressions of arbitrary complexity. The structure of expressions is fairly intuitive, with the <A href="http://incubator.apache.org/cayenne/1_2/grammar/ExpressionParser.html" class="external-link" rel="nofollow">formal grammar</A> showing the formal syntax and operators currently supported, but this topic is devoted to showing examples of usage. To demonstrate, an expression that matches Paintings with names that start with "A" and a price less than $1000.00 can be written as:</P> |
| |
| <DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent"> |
| <PRE class="code-java"> |
| Expression e = Expression.fromString(<SPAN class="code-quote">"paintingTitle like 'A%' and estimatedPrice < 1000"</SPAN>); |
| </PRE> |
| </DIV></DIV> |
| |
| <P>As you can see, the Expression class provides an easy way to specify the <TT>WHERE</TT> portion of a database query.</P> |
| |
| <H3><A name="BuildingExpressions-BinaryOperators"></A>Binary Operators</H3> |
| |
| <P>Expressions used as query qualifiers must use binary operators:</P> |
| |
| <DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent"> |
| <PRE class="code-java"> |
| <SPAN class="code-comment">// valid qualifier |
| </SPAN>Expression e1 = Expression.fromString(<SPAN class="code-quote">"artistName like 'A%'"</SPAN>); |
| |
| <SPAN class="code-comment">// INVALID QUALIFIER - <SPAN class="code-keyword">this</SPAN> will result in a SQL exception even |
| </SPAN><SPAN class="code-comment">// though it is still a valid Cayenne expression |
| </SPAN>Expression e2 = Expression.fromString(<SPAN class="code-quote">"artistName"</SPAN>); |
| </PRE> |
| </DIV></DIV> |
| |
| <H3><A name="BuildingExpressions-CharacterConstants"></A>Character Constants</H3> |
| |
| <P>Character constants should be enclosed in single or double quotes:</P> |
| <DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent"> |
| <PRE class="code-java"> |
| <SPAN class="code-comment">// e1 and e2 are equivalent |
| </SPAN>Expression e1 = Expression.fromString(<SPAN class="code-quote">"name = 'ABC'"</SPAN>); |
| Expression e2 = Expression.fromString(<SPAN class="code-quote">"name = \"</SPAN>ABC\""); |
| </PRE> |
| </DIV></DIV> |
| |
| <H3><A name="BuildingExpressions-CaseSensitive"></A>Case Sensitive</H3> |
| |
| <P>Predefined expression operators are all case sensitive and are usually lowercase. Complex words mostly follow the "Java naming style":</P> |
| <DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent"> |
| <PRE class="code-java"> |
| <SPAN class="code-comment">// correct |
| </SPAN>Expression e1 = Expression.fromString(<SPAN class="code-quote">"artistName likeIgnoreCase 'A%'"</SPAN>); |
| |
| <SPAN class="code-comment">// INCORRECT - will result in ParseException |
| </SPAN>Expression e2 = Expression.fromString(<SPAN class="code-quote">"artistName LIKEIGNORECASE 'A%'"</SPAN>); |
| </PRE> |
| </DIV></DIV> |
| |
| <H3><A name="BuildingExpressions-Groupings"></A>Groupings</H3> |
| |
| <P>Grouping of operations is done with parenthesis:</P> |
| <DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent"> |
| <PRE class="code-java"> |
| Expression e1 = Expression.fromString(<SPAN class="code-quote">"value = (estimatedPrice + 250.00) * 3"</SPAN>); |
| </PRE> |
| </DIV></DIV> |
| |
| <H3><A name="BuildingExpressions-Prefixes"></A>Prefixes</H3> |
| |
| <P>Object expressions are unquoted strings, <B>optionally</B> prefixed by "obj:". Database expressions are unquoted strings, <B>always</B> prefixed with "db:":</P> |
| |
| <DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent"> |
| <PRE class="code-java"> |
| <SPAN class="code-comment">// object path |
| </SPAN>Expression e1 = Expression.fromString(<SPAN class="code-quote">"artistName = 'Salvador Dali'"</SPAN>); |
| |
| <SPAN class="code-comment">// same object path |
| </SPAN>Expression e2 = Expression.fromString(<SPAN class="code-quote">"obj:artistName = 'Salvador Dali'"</SPAN>); |
| |
| <SPAN class="code-comment">// database path, <SPAN class="code-quote">"db:"</SPAN> prefix is mandatory |
| </SPAN>Expression e3 = Expression.fromString(<SPAN class="code-quote">"db:ARTIST_NAME = 'Salvador Dali'"</SPAN>); |
| </PRE> |
| </DIV></DIV> |
| |
| <P>Please note that "obj:" and "db:" are case sensitive.</P> |
| |
| |
| <H3><A name="BuildingExpressions-NamedParameterExpressions"></A>Named Parameter Expressions</H3> |
| |
| <P>Expressions can have named parameters (names that start with "$"). Parameterized expressions are an easy way to create reusable expression templates:</P> |
| <DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent"> |
| <PRE class="code-java"> |
| <SPAN class="code-keyword">final</SPAN> Expression template = Expression.fromString(<SPAN class="code-quote">"artistName = $name"</SPAN>); |
| SelectQuery query; |
| List values; |
| ... |
| Map params = <SPAN class="code-keyword">new</SPAN> HashMap(); |
| params.put(<SPAN class="code-quote">"name"</SPAN>, <SPAN class="code-quote">"Salvador Dali"</SPAN>); |
| query = <SPAN class="code-keyword">new</SPAN> SelectQuery(Artist.class, template.expWithParameters(params)); |
| values = dataContext.performQuery(query); |
| ... |
| params.put(<SPAN class="code-quote">"name"</SPAN>, <SPAN class="code-quote">"Monet"</SPAN>); |
| query = <SPAN class="code-keyword">new</SPAN> SelectQuery(Artist.class, template.expWithParameters(params)); |
| values = dataContext.performQuery(query); |
| </PRE> |
| </DIV></DIV> |
| |
| <P>To create a named parameterized expression with a LIKE clause, the wildcard(s) must be part of the values in the Map and not the expression string itself:</P> |
| <DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent"> |
| <PRE class="code-java"> |
| <SPAN class="code-keyword">final</SPAN> Expression template = Expression.fromString(<SPAN class="code-quote">"artistName like $name"</SPAN>); |
| SelectQuery query; |
| List values; |
| ... |
| Map params = <SPAN class="code-keyword">new</SPAN> HashMap(); |
| params.put(<SPAN class="code-quote">"name"</SPAN>, <SPAN class="code-quote">"Salvi%"</SPAN>); |
| query = <SPAN class="code-keyword">new</SPAN> SelectQuery(Artist.class, template.expWithParameters(params)); |
| values = dataContext.performQuery(query); |
| ... |
| </PRE> |
| </DIV></DIV> |
| |
| <H3><A name="BuildingExpressions-KeyPathExpressions"></A>Key Path Expressions</H3> |
| |
| <P>A very powerful feature of Cayenne's expressions are the ability to specify relationships in the expression string as a "dotted" key path. Cayenne will automatically determine all the join information. For example, if basing a query off the Painting:</P> |
| |
| <DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent"> |
| <PRE class="code-java"> |
| <SPAN class="code-keyword">final</SPAN> Expression template = Expression.fromString(<SPAN class="code-quote">"artist.artistName = $artist and gallery.galleryName = $gallery"</SPAN>); |
| SelectQuery query; |
| List values; |
| ... |
| Map params = <SPAN class="code-keyword">new</SPAN> HashMap(); |
| params.put(<SPAN class="code-quote">"artist"</SPAN>, <SPAN class="code-quote">"Salvador Dali"</SPAN>); |
| params.put(<SPAN class="code-quote">"gallery"</SPAN>, <SPAN class="code-quote">"Louvre"</SPAN>); |
| query = <SPAN class="code-keyword">new</SPAN> SelectQuery(Artist.class, template.expWithParameters(params)); |
| values = dataContext.performQuery(query); |
| ... |
| </PRE> |
| </DIV></DIV> |
| |
| <P>Note that the key path can contain multiple "dots" in the name – there is no predefined limit. Also, the parameter doesn't have to be a String (or Number/etc), it can also be a Cayenne DataObject if you already have one in memory:</P> |
| |
| <DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent"> |
| <PRE class="code-java"> |
| <SPAN class="code-keyword">final</SPAN> Expression template = Expression.fromString(<SPAN class="code-quote">"artist.artistName = $artist and gallery = $gallery"</SPAN>); |
| SelectQuery query; |
| List values; |
| ... |
| Map params = <SPAN class="code-keyword">new</SPAN> HashMap(); |
| params.put(<SPAN class="code-quote">"artist"</SPAN>, <SPAN class="code-quote">"Salvador Dali"</SPAN>); |
| params.put(<SPAN class="code-quote">"gallery"</SPAN>, gallery); <SPAN class="code-comment">// gallery = instance of Gallery |
| </SPAN>query = <SPAN class="code-keyword">new</SPAN> SelectQuery(Artist.class, template.expWithParameters(params)); |
| values = dataContext.performQuery(query); |
| ... |
| </PRE> |
| </DIV></DIV> |
| |
| <H3><A name="BuildingExpressions-OptionalNamedParameterValues"></A>Optional Named Parameter Values</H3> |
| |
| <P>Cayenne by default automatically omits parts of an expression which have no matching value. Using the expression from above:</P> |
| |
| <DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent"> |
| <PRE class="code-java"> |
| <SPAN class="code-keyword">final</SPAN> Expression template = Expression.fromString(<SPAN class="code-quote">"artist.artistName = $artist and gallery.galleryName = $gallery"</SPAN>); |
| SelectQuery query; |
| List values; |
| ... |
| Map params = <SPAN class="code-keyword">new</SPAN> HashMap(); |
| params.put(<SPAN class="code-quote">"artist"</SPAN>, <SPAN class="code-quote">"Salvador Dali"</SPAN>); |
| query = <SPAN class="code-keyword">new</SPAN> SelectQuery(Artist.class, template.expWithParameters(params)); |
| values = dataContext.performQuery(query); |
| ... |
| </PRE> |
| </DIV></DIV> |
| |
| <P>Even though the specified expression has two named parameters, the "gallery" key has been omitted. Cayenne will automatically translate the expression into <TT>"artist.artistName = $artist"</TT> (which becomes <TT>"artist.artistName = 'Salvador Dali'"</TT>). This feature allows a restrictive search qualifier to be written and prompt the user for search criteria. If the user leaves values out, the expression can automatically widen to be a less restrictive search by omitting the keys for the map.</P> |
