Cayenne User Documentation
Parameterized Queries

SelectQuery objects can be rather complex. They may contain long qualifiers and lots of tuning parameters. Parameterized Queries feature addresses reusability of complex queries. With this feature, for each group of queries that differ only in the values used in the qualifier, a developer may create a single shared "prototype" or "template" query, and use it later as a factory for other queries. All settings configured in the prototype object will be passed to the final queries. Qualifier of the prototype query may use named parameters that are substituted for real values when a final query is created from the prototype.

| "Prototype" queries are normally created in CayenneModeler and stored in the DataMap XML file. "Queries Stored in DataMap" chapter describes how to use such templates. This chapter shows how to create them on the spot using the API calls.

Building a prototype query using `Expression.fromString(..)`:

    import org.apache.cayenne.exp.Expression;
    import org.apache.cayenne.query.SelectQuery;
    ...
    // create a qualifier with two named parameters: "pname" and "aname"
    Expression qual = Expression.fromString("paintingTitle = $pname or toArtist.artistName = $aname");

    // build a query prototype of a query - simply another select query
    SelectQuery proto = new SelectQuery(Painting.class, qual);
    proto.setDistinct(true);

Same example but using ExpressionFactory:

    import java.util.*;
    import org.apache.cayenne.exp.Expression;
    import org.apache.cayenne.exp.ExpressionFactory;
    import org.apache.cayenne.exp.ExpressionParameter;
    import org.apache.cayenne.query.SelectQuery;
    ...
    // create a qualifier with two named parameters:
    //  "pname" and "aname"
    List list = new ArrayList();
    list.add(ExpressionFactory.matchExp("paintingTitle",
             new ExpressionParameter("pname")));
    list.add(ExpressionFactory.matchExp("toArtist.artistName",
             new ExpressionParameter("aname")));
    Expression qual = ExpressionFactory.joinExp(Expression.OR, list);

    // build a query prototype of a query - simply another select query
    SelectQuery proto = new SelectQuery(Painting.class, qual);
    proto.setDistinct(true);

Prototype built in the example above can be used to create other queries. Relevalnt API is:

- public SelectQuery SelectQuery.queryWithParameters(Map parameters, boolean pruneMissing)
  Creates and returns a new SelectQuery using current query as a prototype. Map of parameters is used to substitute named parameters in the qualifier with the real values. Returned query is a separate instance and can be further customized without affecting the prototype. If `pruneMissing` is true and some of the named parameters are missing from the parameters map, Cayenne would prune expressions that can not be resolved. If pruneMissing is set to false, the method would throw an exception unless all the named parameters can be resolved.
- public SelectQuery SelectQuery.queryWithParameters(Map parameters)
  A shortcut for `public SelectQuery queryWithParameters(Map parameters, true)`.

Example of using queryWithParameters is shown below:

    import java.util.Map;
    import java.util.HashMap;
    import org.apache.cayenne.query.SelectQuery;
    ...
    SelectQuery proto = ... // this was built in the example above

    // create a query
    Map params1 = new HashMap();
    params1.put("aname", "Dali");
    SelectQuery query1 = proto.queryWithParameters(params1);

    // further customize returned query
    // without affecting the prototype
    query1.setFetchLimit(100);
    ...
    // create another query with a different set of parameters
    Map params2 = new HashMap();
    params2.put("aname", "Monet");
    params2.put("pname", "The Beach at Trouville");
    SelectQuery query2 = proto.queryWithParameters(params2);
    ...

.
Copyright ©2001-2007 Apache Software Foundation