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.
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);
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:
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(); |
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 The second way to create an inner join is SELECT BOOK.* FROM BOOK,AUTHOR WHERE BOOK.AUTHOR_ID=AUTHOR.AUTHOR_ID criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID); 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); |
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.
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);
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);
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.
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).
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);
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 ) );
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);
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