The Expression class (org.apache.cayenne.exp.Expression) provides Expression.fromString(String) as a convenience method to create expressions of arbitrary complexity. The structure of expressions is fairly intuitive, with the formal grammar 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:

Expression e = Expression.fromString("paintingTitle like 'A%' and estimatedPrice < 1000");

As you can see, the Expression class provides an easy way to specify the WHERE portion of a database query.

Binary Operators

Expressions used as query qualifiers must use binary operators:

// valid qualifier
Expression e1 = Expression.fromString("artistName like 'A%'");

// INVALID QUALIFIER - this will result in a SQL exception even
// though it is still a valid Cayenne expression
Expression e2 = Expression.fromString("artistName");

Character Constants

Character constants should be enclosed in single or double quotes:

// e1 and e2 are equivalent
Expression e1 = Expression.fromString("name = 'ABC'");
Expression e2 = Expression.fromString("name = \"ABC\"");

Case Sensitive

Predefined expression operators are all case sensitive and are usually lowercase. Complex words mostly follow the "Java naming style":

// correct
Expression e1 = Expression.fromString("artistName likeIgnoreCase 'A%'");

// INCORRECT - will result in ParseException
Expression e2 = Expression.fromString("artistName LIKEIGNORECASE 'A%'");

Groupings

Grouping of operations is done with parenthesis:

Expression e1 = Expression.fromString("value = (estimatedPrice + 250.00) * 3");

Prefixes

Object expressions are unquoted strings, optionally prefixed by "obj:". Database expressions are unquoted strings, always prefixed with "db:":

// object path
Expression e1 = Expression.fromString("artistName = 'Salvador Dali'");

// same object path
Expression e2 = Expression.fromString("obj:artistName = 'Salvador Dali'");

// database path, "db:" prefix is mandatory
Expression e3 = Expression.fromString("db:ARTIST_NAME = 'Salvador Dali'");

Please note that "obj:" and "db:" are case sensitive.

Named Parameter Expressions

Expressions can have named parameters (names that start with "$"). Parameterized expressions are an easy way to create reusable expression templates:

final Expression template = Expression.fromString("artistName = $name");
SelectQuery query;
List values;
...
Map params = new HashMap();
params.put("name", "Salvador Dali");
query = new SelectQuery(Artist.class, template.expWithParameters(params));
values = dataContext.performQuery(query);
...
params.put("name", "Monet");
query = new SelectQuery(Artist.class, template.expWithParameters(params));
values = dataContext.performQuery(query);

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:

final Expression template = Expression.fromString("artistName like $name");
SelectQuery query;
List values;
...
Map params = new HashMap();
params.put("name", "Salvi%");
query = new SelectQuery(Artist.class, template.expWithParameters(params));
values = dataContext.performQuery(query);
...

Key Path Expressions

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:

final Expression template = Expression.fromString("artist.artistName = $artist and gallery.galleryName = $gallery");
SelectQuery query;
List values;
...
Map params = new HashMap();
params.put("artist", "Salvador Dali");
params.put("gallery", "Louvre");
query = new SelectQuery(Artist.class, template.expWithParameters(params));
values = dataContext.performQuery(query);
...

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:

final Expression template = Expression.fromString("artist.artistName = $artist and gallery = $gallery");
SelectQuery query;
List values;
...
Map params = new HashMap();
params.put("artist", "Salvador Dali");
params.put("gallery", gallery); // gallery = instance of Gallery
query = new SelectQuery(Artist.class, template.expWithParameters(params));
values = dataContext.performQuery(query);
...

Optional Named Parameter Values

Cayenne by default automatically omits parts of an expression which have no matching value. Using the expression from above:

final Expression template = Expression.fromString("artist.artistName = $artist and gallery.galleryName = $gallery");
SelectQuery query;
List values;
...
Map params = new HashMap();
params.put("artist", "Salvador Dali");
query = new SelectQuery(Artist.class, template.expWithParameters(params));
values = dataContext.performQuery(query);
...

Even though the specified expression has two named parameters, the "gallery" key has been omitted. Cayenne will automatically translate the expression into "artist.artistName = $artist" (which becomes "artist.artistName = 'Salvador Dali'"). 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.