Chapter 9. Queries

Table of Contents

Custom Queries

Queries are Java objects used by the application to communicate with the database. Cayenne knows how to translate queries into SQL statements appropriate for a particular database engine. Most often queries are used to find objects matching certain criteria, but there are other types of queries too. E.g. those allowing to run native SQL, call DB stored procedures, etc. When committing objects, Cayenne itself creates special queries to insert/update/delete rows in the database.

There is a number of built-in queries in Cayenne, described later in this chapter. Most of the newer queries use fluent API and can be created and executed as easy-to-read one-liners. Users can define their own query types to abstract certain DB interactions that for whatever reason can not be adequately described by the built-in set.

Queries can be roughly categorized as "object" and "native". Object queries (most notably ObjectSelect, SelectById, and EJBQLQuery) are built with abstractions originating in the object model (the "object" side in the "object-relational" divide). E.g. ObjectSelect is assembled from a Java class of the objects to fetch, a qualifier expression, orderings, etc. - all of this expressed in terms of the object model.

Native queries describe a desired DB operation as SQL code (SQLSelect, SQLTemplate query) or a reference to a stored procedure (ProcedureQuery), etc. The results of native queries are usually presented as Lists of Maps, with each map representing a row in the DB (a term "data row" is often used to describe such a map). They can potentially be converted to objects, however it may take a considerable effort to do so. Native queries are also less (if at all) portable across databases than object queries.


ObjectSelect supersedes older SelectQuery. SelectQuery is still available and supported.

ObjectSelect is the most commonly used query in Cayenne applications. This may be the only query you will ever need. It returns a list of persistent objects (or data rows) of a certain type specified in the query:

List<Artist> objects = ObjectSelect.query(Artist.class).select(context);

This returned all rows in the "ARTIST" table. If the logs were turned on, you might see the following SQL printed:

INFO: === returned 5 row. - took 5 ms.

This SQL was generated by Cayenne from the ObjectSelect above. ObjectSelect can have a qualifier to select only the data matching specific criteria. Qualifier is simply an Expression (Expressions where discussed in the previous chapter), appended to the query using "where" method. If you only want artists whose name begins with 'Pablo', you might use the following qualifier expression:

List<Artist> objects = ObjectSelect.query(Artist.class)

The SQL will look different this time:

[bind: 1->NAME:'Pablo%']
INFO: === returned 1 row. - took 6 ms.

ObjectSelect allows to assemble qualifier from parts, using "and" and "or" method to chain then together:

List<Artist> objects = ObjectSelect.query(Artist.class)

To order the results of ObjectSelect, one or more orderings can be applied:

List<Artist> objects = ObjectSelect.query(Artist.class)

There's a number of other useful methods in ObjectSelect that define what to select and how to optimize database interaction (prefetching, caching, fetch offset and limit, pagination, etc.). Some of them are discussed in separate chapters on caching and performance optimization. Others are fairly self-explanatory. Please check the API docs for the full extent of the ObjectSelect features.


EJBQLQuery was created as a part of an experiment in adopting some of Java Persistence API (JPA) approaches in Cayenne. It is a parameterized object query that is created from query String. A String used to build EJBQLQuery must conform to JPQL (JPA query language):

EJBQLQuery query = new EJBQLQuery("select a FROM Artist a");

JPQL details can be found in any JPA manual. Here we'll mention only how this fits into Cayenne and what are the differences between EJBQL and other Cayenne queries.

Although most frequently EJBQLQuery is used as an alternative to SelectQuery, there are also DELETE and UPDATE varieties available.


As of this version of Cayenne, DELETE and UPDATE do not change the state of objects in the ObjectContext. They are run directly against the database instead. 

EJBQLQuery select = new EJBQLQuery("select a FROM Artist a WHERE = 'Salvador Dali'");
List<Artist> artists = context.performQuery(select);
EJBQLQuery delete = new EJBQLQuery("delete from Painting");
EJBQLQuery update = new EJBQLQuery("UPDATE Painting AS p SET = 'P2' WHERE = 'P1'");

In most cases SelectQuery is preferred to EJBQLQuery, as it is API-based, and provides you with better compile-time checks. However sometimes you may want a completely scriptable object query. This is when you might prefer EJBQL. A more practical reason for picking EJBQL over SelectQuery though is that the former offers some extra selecting capabilities, namely aggregate functions and subqueries:

EJBQLQuery query = new EJBQLQuery("select a, COUNT(p) FROM Artist a JOIN a.paintings p GROUP BY a");
List<Object[]> result = context.performQuery(query);
for(Object[] artistWithCount : result) {
    Artist a = (Artist) artistWithCount[0];
    int hasPaintings = (Integer) artistWithCount[1];

This also demonstrates a previously unseen type of select result - a List of Object[] elements, where each entry in an Object[] is either a DataObject or a scalar, depending on the query SELECT clause. A result can also be a list of scalars:

EJBQLQuery query = new EJBQLQuery("select FROM Artist a");
List<String> names = context.performQuery(query);

EJBQLQuery supports an "IN" clause with three different usage-patterns. The following example would require three individual positional parameters (named parameters could also have been used) to be supplied.

select p from Painting p where p.paintingTitle in (?1,?2,?3)

The following example requires a single positional parameter to be supplied. The parameter can be any concrete implementation of the java.util.Collection interface such as java.util.List or java.util.Set.

select p from Painting p where p.paintingTitle in ?1

The following example is functionally identical to the one prior.

select p from Painting p where p.paintingTitle in (?1)

It is possible to convert an Expression object used with a SelectQuery to EJBQL. Use the Expression#appendAsEJBQL methods for this purpose.

While Cayenne Expressions discussed previously can be thought of as identical to JPQL WHERE clause, and indeed they are very close, there are a few noteable differences:

  • Null handling: SelectQuery would translate the expressions matching NULL values to the corresponding "X IS NULL" or "X IS NOT NULL" SQL syntax. EJBQLQuery on the other hand requires explicit "IS NULL" (or "IS NOT NULL") syntax to be used, otherwise the generated SQL will look like "X = NULL" (or "X <> NULL"), which will evaluate differently.

  • Expression Parameters: SelectQuery uses "$" to denote named parameters (e.g. "$myParam"), while EJBQL uses ":" (e.g. ":myParam"). Also EJBQL supports positional parameters denoted by the question mark: "?3".


SQLTemplate is a query that allows to run native SQL from a Cayenne application. It comes handy when the standard ORM concepts are not sufficient for a given query or an update. SQL is too powerful and allows to manipulate data in ways that are not easily described as a graph of related entities. Cayenne acknowledges this fact and provides this facility to execute SQL, mapping the result to objects when possible. Here are examples of selecting and non-selecting SQLTemplates:

SQLTemplate select = new SQLTemplate(Artist.class, "select * from ARTIST"); 
List<Artist> result = context.performQuery(select);
SQLTemplate update = new SQLTemplate(Artist.class, "delete from ARTIST");
QueryResponse response = context.performGenericQuery(update);

Cayenne doesn't make any attempt to make sense of the SQL semantics, so it doesn't know whether a given query is performing a select or update, etc. It is the the user's decision to run a given query as a selecting or "generic".


Any data modifications done to DB as a result of SQLTemplate execution do not change the state of objects in the ObjectContext. So some objects in the context may become stale as a result.

Another point to note is that the first argument to the SQLTemplate constructor - the Java class - has the same meaning as in SelectQuery only when the result can be converted to objects (e.g. when this is a selecting query and it is selecting all columns from one table). In this case it denotes the "root" entity of this query result. If the query does not denote a single entity result, this argument is only used for query routing, i.e. determining which database it should be run against. You are free to use any persistent class or even a DataMap instance in such situation. It will work as long as the passed "root" maps to the same database as the current query.

To achieve interoperability between mutliple RDBMS a user can specify multiple SQL statements for the same SQLTemplate, each corresponding to a native SQL dialect. A key used to look up the right dialect during execution is a fully qualified class name of the corresponding DbAdapter. If no DB-specific statement is present for a given DB, a default generic statement is used. E.g. in all the examples above a default statement will be used regardless of the runtime database. So in most cases you won't need to explicitly "translate" your SQL to all possible dialects. Here is how this works in practice:

SQLTemplate select = new SQLTemplate(Artist.class, "select * from ARTIST"); 

// For Postgres it would be nice to trim padding of all CHAR columns.
// Otherwise those will be returned with whitespace on the right.
// assuming "NAME" is defined as CHAR...
query.setTemplate(PostgresAdapter.class.getName(), pgSQL);

Scripting SQLTemplate with Velocity

The most interesting aspect of SQLTemplate (and the reason why it is called a "template") is that a SQL string is treated by Cayenne as an Apache Velocity template. Before sending it to DB as a PreparedStatement, the String is evaluated in the Velocity context, that does variable substitutions, and performs special callbacks in response to various directives, thus controlling query interaction with the JDBC layer.

Check Velocity docs for the syntax details. Here we'll just mention the two main scripting elements - "variables" (that look like $var) and "directives" (that look like #directive(p1 p2 p3)). All built-in Velocity directives are supported. Additionally Cayenne defines a number of its own directives to bind parameters to PreparedStatements and to control the structure of the ResultSet. These directives are described in the following sections.

Variable Substitution

All variables in the template string are replaced from query parameters:

SQLTemplate query = new SQLTemplate(Artist.class, "delete from $tableName");
query.setParameters(Collections.singletonMap("tableName", "mydb.PAINTING"));

// this will generate SQL like this: "delete from mydb.PAINTING"

The example above demonstrates the point made earlier in this chapter - even if we don't know upfront which table the query will run against, we can still use a fixed "root" in constructor (Artist.class in this case) , as we are not planning on converting the result to objects.

Variable substitution within the text uses "object.toString()" method to replace the variable value. Keep in mind that this may not be appropriate in all situations. E.g. passing a date object in a WHERE clause expression may be converted to a String not understood by the target RDBMS SQL parser. In such cases variable should be wrapped in #bind directive as described below.


These are the Cayenne directives used to customize SQLTemplate parsing and integrate it with the JDBC layer:


Creates a PreparedStatement positional parameter in place of the directive, binding the value to it before statement execution. #bind is allowed in places where a "?" would be allowed in a PreparedStatement. And in such places it almost always makes sense to pass objects to the template via this or other forms of #bind instead of inserting them inline.


#bind(value jdbcType)
#bind(value jdbcType scale)


  • value - can either be a char constant or a variable that is resolved from the query parameters. Note that the variable can be a collection, that will be automatically expanded into a list of individual value bindings. This is useful for instance to build IN conditions.

  • jdbcType - is a JDBC data type of the parameter as defined in java.sql.Types.

  • scale - An optional scale of the numeric value. Same as "scale" in PreparedStatement.


#bind($xyz 'VARCHAR')
#bind($xyz 'DECIMAL' 2)

Full example:

update ARTIST set NAME = #bind($name) where ID = #bind($id)


Same as #bind, but also includes the "=" sign in front of the value binding. Look at the example below - we took the #bind example and replaced "ID = #bind(..)" with "ID #bindEqual(..)". While it looks like a clumsy shortcut to eliminate the equal sign, the actual reason why this is useful is that it allows the value to be null. If the value is not null, "= ?" is generated, but if it is, the resulting chunk of the SQL would look like "IS NULL" and will be compilant with what the DB expects.


#bindEqual(value jdbcType)
#bindEqual(value jdbcType scale)

Arguments: (same as #bind)


#bindEqual($xyz 'VARCHAR')
#bindEqual($xyz 'DECIMAL' 2)

Full example:

update ARTIST set NAME = #bind($name) where ID #bindEqual($id)


This directive deals with the same issue as #bindEqual above, only it generates "not equal" in front of the value (or IS NOT NULL).


#bindNotEqual(value jdbcType)
#bindNotEqual(value jdbcType scale)

Arguments: (same as #bind)


#bindNotEqual($xyz 'VARCHAR')
#bindNotEqual($xyz 'DECIMAL' 2)

Full example:

update ARTIST set NAME = #bind($name) where ID #bindEqual($id)


It can be tricky to use a Persistent object or an ObjectId in a binding, especially for tables with compound primary keys. This directive helps to handle such binding. It maps columns in the query to the names of Persistent object ID columns, extracts ID values from the object, and generates SQL like "COL1 = ? AND COL2 = ? ..." , binding positional parameters to ID values. It can also correctly handle null object. Also notice how we are specifying a Velocity array for multi-column PK.


#bindObjectEqual(value columns idColumns)


  • value - must be a variable that is resolved from the query parameters to a Persistent or ObjectId.

  • columns - the names of the columns to generate in the SQL.

  • idColumn - the names of the ID columns for a given entity. Must match the order of "columns" to match against.


#bindObjectEqual($a 't0.ID' 'ID')
#bindObjectEqual($b ['t0.FK1', 't0.FK2'] ['PK1', 'PK2'])

Full example:

SQLTemplate select = new SQLTemplate(Artist.class, sql);

Artist a = ....
select.setParameters(Collections.singletonMap("a", a)); 


Same as #bindObjectEqual above, only generates "not equal" operator for value comparison (or IS NOT NULL).


#bindObjectNotEqual(value columns idColumns)

Arguments: (same as #bindObjectEqual)


#bindObjectNotEqual($a 't0.ID' 'ID')
#bindObjectNotEqual($b ['t0.FK1', 't0.FK2'] ['PK1', 'PK2'])

Full example:

String sql = "SELECT * FROM PAINTING t0 WHERE #bindObjectNotEqual($a 't0.ARTIST_ID' 'ARTIST_ID' ) ORDER BY PAINTING_ID";
SQLTemplate select = new SQLTemplate(Artist.class, sql);

Artist a = ....
select.setParameters(Collections.singletonMap("a", a)); 


Renders a column in SELECT clause of a query and maps it to a key in the result DataRow. Also ensures the value read is of the correct type. This allows to create a DataRow (and ultimately - a persistent object) from an arbitrary ResultSet.


#result(column javaType)
#result(column javaType alias)
#result(column javaType alias dataRowKey)


  • column - the name of the column to render in SQL SELECT clause.

  • javaType - a fully-qualified Java class name for a given result column. For simplicity most common Java types used in JDBC can be specified without a package. These include all numeric types, primitives, String, SQL dates, BigDecimal and BigInteger. So "#result('A' 'String')", "#result('B' 'java.lang.String')" and "#result('C' 'int')" are all valid

  • alias - specifies both the SQL alias of the column and the value key in the DataRow. If omitted, "column" value is used.

  • dataRowKey - needed if SQL 'alias' is not appropriate as a DataRow key on the Cayenne side. One common case when this happens is when a DataRow retrieved from a query is mapped using joint prefetch keys (see below). In this case DataRow must use database path expressions for joint column keys, and their format is incompatible with most databases alias format.


#result('DATE_OF_BIRTH' 'java.util.Date') 
#result('DOB' 'java.util.Date' 'DATE_OF_BIRTH')
#result('DOB' 'java.util.Date' '' 'artist.DATE_OF_BIRTH') 
#result('SALARY' 'float') 

Full example:

SELECT #result('ID' 'int'), #result('NAME' 'String'), #result('DATE_OF_BIRTH' 'java.util.Date') FROM ARTIST

#chain and #chunk

#chain and #chunk directives are used for conditional inclusion of SQL code. They are used together with #chain wrapping multiple #chunks. A chunk evaluates its parameter expression and if it is NULL suppresses rendering of the enclosed SQL block. A chain renders its prefix and its chunks joined by the operator. If all the chunks are suppressed, the chain itself is suppressed. This allows to work with otherwise hard to script SQL semantics. E.g. a WHERE clause can contain multiple conditions joined with AND or OR. Application code would like to exclude a condition if its right-hand parameter is not present (similar to Expression pruning discussed above). If all conditions are excluded, the entire WHERE clause should be excluded. chain/chunk allows to do that.


#chain(operator) ... #end
#chain(operator prefix) ... #end
#chunk() ... #end
#chunk(param) ... #end 

Full example:

#chain('OR' 'WHERE') 
	#chunk($name) NAME LIKE #bind($name) #end
	#chunk($id) ARTIST_ID > #bind($id) #end

Mapping SQLTemplate Results

Here we'll discuss how to convert the data selected via SQLTemplate to some useable format, compatible with other query results. It can either be very simple or very complex, depending on the structure of the SQL, JDBC driver nature and the desired result structure. This section presents various tips and tricks dealing with result mapping.

By default SQLTemplate is expected to return a List of Persistent objects of its root type. This is the simple case:

SQLTemplate query = new SQLTemplate(Artist.class, "SELECT * FROM ARTIST");

// List of Artists
List<Artist> artists = context.performQuery(query);

Just like SelectQuery, SQLTemplate can fetch DataRows. In fact DataRows option is very useful with SQLTemplate, as the result type most often than not does not represent a Cayenne entity, but instead may be some aggregated report or any other data whose object structure is opaque to Cayenne:

SQLTemplate query = new SQLTemplate(Artist.class, sql);

// ensure we are fetching DataRows

// List of DataRow
List<DataRow> rows = context.performQuery(query);

In the example above, even though the query root is Artist. the result is a list of artist names with painting counts (as mentioned before in such case "root" is only used to find the DB to fetch against, but has no bearning on the result). The DataRows here are the most appropriate and desired result type.

In a more advanced case you may decide to fetch a list of scalars or a list of Object[] with each array entry being either an entity or a scalar. You probably won't be doing this too often and it requires quite a lot of work to setup, but if you want your SQLTemplate to return results similar to EJBQLQuery, it is doable using SQLResult as described below:

SQLTemplate query = new SQLTemplate(Painting.class, "SELECT ESTIMATED_PRICE P FROM PAINTING");

// let Cayenne know that result is a scalar
SQLResult resultDescriptor = new SQLResult();

// List of BigDecimals
List<BigDecimal> prices = context.performQuery(query); 
SQLTemplate query = new SQLTemplate(Artist.class, "SELECT t0.ID, t0.NAME, t0.DATE_OF_BIRTH, COUNT(t1.PAINTING_ID) C " +
      "GROUP BY t0.ID, t0.NAME, t0.DATE_OF_BIRTH");

// let Cayenne know that result is a mix of Artist objects and the count of their paintings
EntityResult artistResult = new EntityResult(Artist.class);
artistResult.addDbField(Artist.ID_PK_COLUMN, "ARTIST_ID");
artistResult.addObjectField(Artist.NAME_PROPERTY, "NAME");
artistResult.addObjectField(Artist.DATE_OF_BIRTH_PROPERTY, "DATE_OF_BIRTH");

SQLResult resultDescriptor = new SQLResult();

// List of Object[]
List<Object[]> data = context.performQuery(query);

Another trick related to mapping result sets is making Cayenne recognize prefetched entities in the result set. This emulates "joint" prefetching of SelectQuery, and is achieved by special column naming. Columns belonging to the "root" entity of the query should use unqualified names corresponding to the root DbEntity columns. For each related entity column names must be prefixed with relationship name and a dot (e.g. "toArtist.ID"). Column naming can be controlled with "#result" directive:

String sql = "SELECT distinct "
    + "#result('t1.ESTIMATED_PRICE' 'BigDecimal' '' 'paintings.ESTIMATED_PRICE'), "
    + "#result('t1.PAINTING_TITLE' 'String' '' 'paintings.PAINTING_TITLE'), "
    + "#result('t1.GALLERY_ID' 'int' '' 'paintings.GALLERY_ID'), "
    + "#result('t1.ID' 'int' '' 'paintings.ID'), "
    + "#result('NAME' 'String'), "
    + "#result('DATE_OF_BIRTH' 'java.util.Date'), "
    + "#result('t0.ID' 'int' '' 'ID') "
    + "FROM ARTIST t0, PAINTING t1 "
    + "WHERE t0.ID = t1.ARTIST_ID";

SQLTemplate q = new SQLTemplate(Artist.class, sql);
List<Artist> objects = context.performQuery(query);

And the final tip deals with capitalization of the DataRow keys. Queries like "SELECT * FROM..." and even "SELECT COLUMN1, COLUMN2, ... FROM ..." can sometimes result in Cayenne exceptions on attempts to convert fetched DataRows to objects. Essentially any query that is not using a #result directive to describe the result set is prone to this problem, as different databases may produce different capitalization of the java.sql.ResultSet columns.

The most universal way to address this issue is to describe each column explicitly in the SQLTemplate via #result, e.g.: "SELECT #result('column1'), #result('column2'), ..". However this quickly becomes impractical for tables with lots of columns. For such cases Cayenne provides a shortcut based on the fact that an ORM mapping usually follows some naming convention for the column names. Simply put, for case-insensitive databases developers normally use either all lowercase or all uppercase column names. Here is the API that takes advantage of that user knowledge and forces Cayenne to follow a given naming convention for the DataRow keys (this is also available as a dropdown in the Modeler):

SQLTemplate query = new SQLTemplate("SELECT * FROM ARTIST");
List objects = context.performQuery(query);


SQLTemplate query = new SQLTemplate("SELECT * FROM ARTIST");
List objects = context.performQuery(query); 

None of this affects the generated SQL, but the resulting DataRows are using correct capitalization. Note that you probably shouldn't bother with this unless you are getting CayenneRuntimeExceptions when fetching with SQLTemplate.


Stored procedures are mapped as separate objects in CayenneModeler. ProcedureQuery provides a way to execute them with a certain set of parameters. Just like with SQLTemplate, the outcome of a procedure can be anything - a single result set, mutliple result sets, some data modification (returned as an update count), or a combination of these. So use "performQuery" to get a single result set, and use "performGenericQuery" for anything else:

ProcedureQuery query = new ProcedureQuery("my_procedure", Artist.class);

// Set "IN" parameter values
query.addParam("p1", "abc");
query.addParam("p2", 3000);

List<Artist> result = context.performQuery(query);
// here we do not bother with root class. 
// Procedure name gives us needed routing information
ProcedureQuery query = new ProcedureQuery("my_procedure");

query.addParam("p1", "abc");
query.addParam("p2", 3000);

QueryResponse response = context.performGenericQuery(query); 

A stored procedure can return data back to the application as result sets or via OUT parameters. To simplify the processing of the query output, QueryResponse treats OUT parameters as if it was a separate result set. If a stored procedure declares any OUT or INOUT parameters, QueryResponse will contain their returned values in the very first result list:

ProcedureQuery query = new ProcedureQuery("my_procedure");
QueryResponse response = context.performGenericQuery(query);

// read OUT parameters
List out = response.firstList();

if(!out.isEmpty()) {
    Map outParameterValues = (Map) outList.get(0);

There maybe a situation when a stored procedure handles its own transactions, but an application is configured to use Cayenne-managed transactions. This is obviously conflicting and undesirable behavior. In this case ProcedureQueries should be executed explicitly wrapped in an "external" Transaction. This is one of the few cases when a user should worry about transactions at all. See Transactions section for more details.


NamedQuery is a query that is a reference to another query stored in the DataMap. The actual stored query can be SelectQuery, SQLTemplate, EJBQLQuery, etc. It doesn't matter - the API for calling them is the same - via a NamedQuery:

String[] keys = new String[] {"loginid", "password"};
Object[] values = new String[] {"joe", "secret"};

NamedQuery query = new NamedQuery("Login", keys, values);

List<User> matchingUsers = context.performQuery(query); 

Custom Queries

If a user needs some extra functionality not addressed by the existing set of Cayenne queries, he can write his own. The only requirement is to implement org.apache.cayenne.query.Query interface. The easiest way to go about it is to subclass some of the base queries in Cayenne.

E.g. to do something directly in the JDBC layer, you might subclass AbstractQuery:

public class MyQuery extends AbstractQuery {

    public SQLAction createSQLAction(SQLActionVisitor visitor) {
        return new SQLAction() {

            public void performAction(Connection connection, OperationObserver observer) throws SQLException, Exception {
                // 1. do some JDBC work using provided connection... 
                // 2. push results back to Cayenne via OperationObserver

To delegate the actual query execution to a standard Cayenne query, you may subclass IndirectQuery:

public class MyDelegatingQuery extends IndirectQuery {
    protected Query createReplacementQuery(EntityResolver resolver) {
        SQLTemplate delegate = new SQLTemplate(SomeClass.class, generateRawSQL());
        return delegate;
    protected String generateRawSQL() {
        // build some SQL string

In fact many internal Cayenne queries are IndirectQueries, delegating to SelectQuery or SQLTemplate after some preprocessing.