SelectQuery is the most commonly used query in user applications. This may be the only query you will need in most appplications. It returns a list of persistent objects of a certain type specified in the query:

SelectQuery query = new SelectQuery(Artist.class);
List<Artist> objects = context.performQuery(query);

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

INFO: SELECT t0.DATE_OF_BIRTH, t0.NAME, t0.ID FROM ARTIST t0
INFO: === returned 5 row. - took 5 ms.

This SQL was generated by Cayenne from the SelectQuery above. SelectQuery can have a qualifier to select only the data that you care about. Qualifier is simply an Expression (Expressions where discussed in the previous chapter). If you only want artists whose name begins with 'Pablo', you might use the following qualifier expression:

SelectQuery query = new SelectQuery(Artist.class, 
        ExpressionFactory.likeExp(Artist.NAME_PROPERTY, "Pablo%"));
List<Artist> objects = context.performQuery(query);

The SQL will look different this time:

INFO: SELECT t0.DATE_OF_BIRTH, t0.NAME, t0.ID FROM ARTIST t0 WHERE t0.NAME LIKE ?
[bind: 1->NAME:'Pablo%']
INFO: === returned 1 row. - took 6 ms.

SelectQuery allows to append parts of qualifier to self:

SelectQuery query = new SelectQuery(Artist.class);
query.setQualifier(ExpressionFactory.likeExp(Artist.NAME_PROPERTY, "A%"));
query.andQualifier(ExpressionFactory.greaterExp(Artist.DATE_OF_BIRTH_PROPERTY, someDate));

To order the results of SelectQuery, one or more Orderings can be applied. Ordering were already discussed earlier. E.g.:

SelectQuery query = new SelectQuery(Artist.class);

// create Ordering object explicitly
query.addOrdering(new Ordering(Artist.DATE_OF_BIRTH_PROPERTY, SortOrder.DESCENDING));

// or let SelectQuery create it behind the scenes
query.addOrdering(Artist.NAME_PROPERTY, SortOrder.ASCENDING);

There's a number of other useful properties in SelectQuery 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 SelectQuery 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.

EJBQLQuery select = new EJBQLQuery("select a FROM Artist a WHERE a.name = 'Salvador Dali'");
List<Artist> artists = context.performQuery(select);

EJBQLQuery delete = new EJBQLQuery("delete from Painting");
context.performGenericQuery(delete);

EJBQLQuery update = new EJBQLQuery("UPDATE Painting AS p SET p.name = 'P2' WHERE p.name = 'P1'");
context.performGenericQuery(update);

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 a.name FROM Artist a");
List<String> names = context.performQuery(query);

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:

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".

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...
String pgSQL = "SELECT ARTIST_ID, RTRIM(NAME), DATE_OF_BIRTH FROM ARTIST";
query.setTemplate(PostgresAdapter.class.getName(), pgSQL);

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"

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

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

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

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

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

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

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

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

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

#bindObjectEqual(value columns idColumns)

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

String sql = "SELECT * FROM PAINTING t0 WHERE #bindObjectEqual($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)); 

#bindObjectNotEqual(value columns idColumns)

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

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)); 

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

#result('NAME')
#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') 

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

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

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

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

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

String sql = SELECT t0.NAME, COUNT(1) FROM ARTIST t0 JOIN PAINTING t1 ON (t0.ID = t1.ARTIST_ID) "
    + "GROUP BY t0.NAME ORDER BY COUNT(1)";
SQLTemplate query = new SQLTemplate(Artist.class, sql);

// ensure we are fetching DataRows
query.setFetchingDataRows(true);

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

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

// let Cayenne know that result is a scalar
SQLResult resultDescriptor = new SQLResult();
resultDescriptor.addColumnResult("P");
query.setResult(resultDescriptor);

// 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 " +
      "FROM ARTIST t0 LEFT JOIN PAINTING t1 ON (t0.ID = t1.ARTIST_ID) " +
      "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();
resultDescriptor.addEntityResult(artistResult);
resultDescriptor.addColumnResult("C");
query.setResult(resultDescriptor);

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

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);
q.addPrefetch(Artist.PAINTINGS_PROPERTY)
List<Artist> objects = context.performQuery(query);

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

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

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); 

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 {

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

            @Override
            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 {
    
    @Override
    protected Query createReplacementQuery(EntityResolver resolver) {
        SQLTemplate delegate = new SQLTemplate(SomeClass.class, generateRawSQL());
        delegate.setFetchingDataRows(true);
        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.