Advanced Criteria Techniques

For a basic description and examples of the Criteria Object with Peers please view the Peers Howto document. This document intends to show more advanced techniques using Criteria, such as comparators and joins. As always, for more information on the methods available in the Criteria Object, view the javadocs.

Ordering the Results

One of the common clauses in an SQL Query is the ORDER BY clause. With the criteria object, the results can be ordered via the addAscendingOrderByColumn(String columnname) and addDescendingOrderByColumn(String columnname) methods. As an example, consider the book table from the tutorial. To get all books ordered by their title and ISBN (where the ISBN should only be taken into accout for equal titles), use the following code:

Criteria criteria = new Criteria();
criteria.addAscendingOrderByColumn(BookPeer.TITLE);
criteria.addAscendingOrderByColumn(BookPeer.ISBN);

List books = BookPeer.doSelect(criteria);

Using Criteria to create Joins

In a relational database, there are two different join types: inner joins and outer joins. Both types can be generated using Torque and will be discussed seperately in the following:

Inner joins

Imagine we want to know all authors which have published at least one book. This can be achieved using the following code:

Criteria criteria = new Criteria();
criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID, Criteria.INNER_JOIN);

List bookAuthors = AuthorPeer.doSelect(criteria);

This method creates a so called "inner join", i.e. only author entries which have a corresponding book entry are selected.

Details
All joins which are created using Criteria are only used for data selection, not for reading the related objects. For example, in the above code, only author objects are read in from the database. The book objects which are related to the authors are not read, meaning that
Author author = (Author) bookAuthors.get(0);
List books = author.getBooks();
performs a background read in the database to get all the books for one author. If you do not like this behaviour, there are also methods which do read the related objects, see for example BaseBookPeer.doSelectJoinAuthor(). These methods are protected to keep the public API reasonable. If you need public access to such a method, simply overwrite the method in the Peer class and declare the method public. Note that these methods perform inner joins (this behavour will hopefully be changed in the near future).
Also, in SQL, there are two different ways to state an inner join. The first way is a statement like
SELECT BOOK.* FROM BOOK INNER JOIN AUTHOR ON BOOK.AUTHOR_ID=AUTHOR.AUTHOR_ID
SQL like this is produced if the join type is stated explicitly (as above).

The second way to create an inner join is
SELECT BOOK.* FROM BOOK,AUTHOR WHERE BOOK.AUTHOR_ID=AUTHOR.AUTHOR_ID
A SQL statement like this is created by not supplying the join type, for example in
criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID);
Note that both SQL statements return the same result set.

It is not possible to combine both ways of creating a join in most databases, i.e. code like
criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID);
criteria.addJoin(BookPeer.PUBLISHER_ID, PublisherPeer.PUBLISHER_ID, Criteria.INNER_JOIN);
produces an SQL error in most databases. In most circumstances, it is recommended to state t the join type explicitly. However, if the "INNER JOIN" syntax is not supported by your database, try not to state the join type explicitly.

Outer joins

To discuss the difference between inner joins and outer joins, consider the following code fragments. Joins will be used there just for discussing the effects of the different join types, but for no other reason.

First, let us reconsider the inner join code:

Criteria criteria = new Criteria();
criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID, Criteria.INNER_JOIN);

List bookAuthors = AuthorPeer.doSelect(criteria);

For an inner join, the database only returns an entry in one table if there is a corresponding entry in the other table. In the example above, this means that only authors with a book are read. In contrast, with an outer join, an entry in one table is also returned even if there is no corresponding entry in the other table:

Criteria criteria = new Criteria();
criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID, Criteria.LEFT_JOIN);
List authors = AuthorPeer.doSelect(criteria);

The above code uses a left (outer) join. There, authors which have no books also appear in the list of authors. There are two kinds of outer joins: left joins and right joins. In a left join, entries in the first table are returned even if no entry in the second table is found, whereas in a right join, entries in the second table are returned even if there are no entries in the first table. Both kinds are supported in Torque via the constants Criteria.LEFT_JOIN and Criteria.RIGHT_JOIN.

Usually, outer joins are used for reading in several tables at once (not ?yet? supported in Torque), or for ordering the contents of one table by the contents of another table.

Using DISTINCT with Criteria

All of the examples in the section "joins" return multiples of the desired table columns. In the first example, the author will appear for every book that is attached to it. If an author has published 10 books, the author will appear in the returned List 10 times. To avoid this problem so that our returned List only returns one author once despite the number of times it appears, we can use the setDistinct() method in Criteria. For instance:

Criteria criteria = new Criteria();
criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID, Criteria.INNER_JOIN);
criteria.setDistinct();

List bookAuthors = AuthorPeer.doSelect(criteria);

Putting Joins, Ordering and Distinct Together

To put all the above together, we can query the bookstore database for all authors which have published a book, and order them by the author's name. This requires a join between the book table and the author table.

Criteria criteria = new Criteria();
criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID, Criteria.INNER_JOIN);
criteria.setDistinct();
criteria.addAscendingOrderByColumn(AuthorPeer.NAME);

List bookAuthors = AuthorPeer.doSelect(criteria);

Using the Comparators in Criteria

The Criteria Object has the following camparators:

Criteria.ALT_NOT_EQUAL
Criteria.CUSTOM
Criteria.DISTINCT
Criteria.EQUAL
Criteria.GREATER_EQUAL
Criteria.GREATER_THAN
Criteria.IN
Criteria.JOIN
Criteria.LESS_EQUAL
Criteria.LESS_THAN
Criteria.LIKE
Criteria.NOT_EQUAL
Criteria.NOT_IN

The comparators can be used to return results that satisfy the chosen comparisons. As an example, assume we have Invoice OM and Peer Objects that map to an invoice table in a database. The invoice table contains the columns, INVOICE_ID, COST, DATE and DESCRIPTION. Where the id is an integer, the cost a double, the date an mysql DATETIME and the Description a VARCHAR.

In the case of an invoice, we may need to know all the invoices that are above a certain limit. Where the limit is greater than $1000, this could be done via:

Criteria criteria = new Criteria();
criteria.add(InvoicePeer.COST, 1000, Criteria.GREATER_THAN);

List invoices = InvoicePeer.doSelect(criteria);

This will return a Vector of Invoice OM Objects which have cost values greater than $1000. The other comparitors work similarly and can be used in the same manner though many of the comparators are present as methods in the Criteria Object already, such as the Joins.

Case insensitive LIKE Comparator

A LIKE comparison is usually case sensitive (unless the underlying database only provides case sensitive LIKE clauses - e.g. MySQL). To get a case insensitive LIKE you need to tell the criteria that it should ignore the case thus:

Criteria criteria = new Criteria();
criteria.add(InvoicePeer.TABLE_NAME, searchField,
        (Object) ("%" + searchCriteria + "%"), Criteria.LIKE);
criteria.getCriterion(InvoicePeer.TABLE_NAME, searchField).setIgnoreCase(true);

List invoices = InvoicePeer.doSelect(criteria);

For PostgreSQL this will use ILIKE, for other databases it will use the SQL upper() function on the column and search string (for Oracle you may want to define a function index to make this efficient).

Using the CUSTOM Comparator to check for NULL and NOT NULL

There is currently no conditional operator for NULL and NOT NULL as the database implementations vary fairly widely. The way to solve it for a particular application is through the CUSTOM comparitor. Using the Invoice example again to check if the description is null;

Criteria criteria = new Criteria();
criteria.add(InvoicePeer.DESCRIPTION, (Object)"DESCRIPTION is NULL", Criteria.CUSTOM);

List invoices = InvoicePeer.doSelect(criteria);

Using Criterion to use a Column twice in a Criteria

The Criteria Object extends the functionality of a Hashtable and as such suffers from the Hashtable limitation of the key having to be unique in the Hashtable. When a Criteria is set to use the same column twice, it overwrites the previous key. The way around this is to use the Criterion Object. The Criterion is a final inner class of Criteria. Because it is a member class the Criterion can "look" into Criteria's instance fields and methods including ones declared private. The Criterion also carries the default package visibility which means it can be used in a sub-class of Criteria.

Using the Invoice table and OM Object again, we may want to search on the Invoice Amounts within a certain Range, such as $1000 and $5000. For this we would need to use the Criterion:

Criteria criteria = new Criteria();
criteria.add(InvoicePeer.COST, 1000, Criteria.GREATER_EQUAL);

Criteria.Criterion criterion = criteria.getCriterion(InvoicePeer.COST);
criterion.and(
               criteria.getNewCriterion(
                             criterion.getTable(),
                             criterion.getColumn(),
                             new Integer(5000),
                             Criteria.LESS_EQUAL )
               );

Simplifying Criteria

The Criteria Object can be verbose to use directly in your code. Often in an application the 80:20 rule applies when dealing with queries. The same 20% of queries are used 80% of the time. While Criteria and Criterion offer a tonne of flexibility, often having something simple to use is easier.

One way to achieve this is to create a class that extends Criteria and add convenience methods for your application or are specific to your database. In this case the example Object will be the SimpleCriteria with the methods that allow access to the examples above.

//Turbine
import org.apache.torque.util.Criteria;

/**
 * SimpleCriteria is a simple case of the more powerful Criteria
 * Object.
 */

public class SimpleCriteria extends Criteria
{
    /** currently used as DEFAULT_CAPACITY in Criteria is private */
    private static final int DEFAULT_CAPACITY = 10;

    /*
     * Constructor
     */
    public SimpleCriteria()
    {
        super(DEFAULT_CAPACITY);
    }

    /*
     * Represents the Greater Than in the WHERE
     * clause of an SQL Statement
     *
     * @param columnname the column name
     * @param columnvalue the column value to be compared against
     */
    public SimpleCriteria greaterThan(String columnname, int columnvalue)
    {
        super.add(columnname, columnvalue, Criteria.GREATER_THAN);
        return this;
    }

   /*
     * Represents the Is NULL in the WHERE
     * clause of an SQL Statement
     *
     * @param columnname the column name
     */
    public SimpleCriteria isNull(String columnname)
    {
        super.add(columnname, (columnname + " is NULL"), Criteria.CUSTOM);
        return this;
    }

   /*
     * Represents the Is NULL in the WHERE
     * clause of an SQL Statement
     *
     * @param columnname the column name
     */
    public SimpleCriteria isBetween(String columnname, int min, int max)
    {
        super.add(columnname, min, Criteria.GREATER_THAN);
        super.Criterion criterion = criteria.getCriterion(columnname);
        criterion.and(
                   super.getNewCriterion(
                              criterion.getTable(),
                              criterion.getColumn(),
                              new Integer(max),
                              Criteria.LESS_EQUAL )
                    );
        return this;
    }

}

This will simplify the code being written in the Business Objects or Actions and condense all the Criteria knowledge into the SimpleCriteria Object. The SimpleCriteria Object used in the same manner as Criteria. For example the initial

SimpleCriteria criteria = new SimpleCriteria();
criteria.isBetween(InvoicePeer.COST, 1000, 5000);

List invoices = InvoicePeer.doSelect(criteria);

Debugging Criteria's

Criteria contains a toString() method which will output a representation of the Criteria as a String. A Criteria to a large extent represents the statements in a WHERE clause of SQL. To see the queries being processed by your application you can configure the logging system to capture the SQL by adding the following to your log4j.properties file:

log4j.logger.org.apache.torque.util.BasePeer = DEBUG