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.
This example will use the related tables in the Turbine Security system between the Turbine_Role and Turbine_Permission tables, with the bridging table, Turbine_Role_Permission. The relationship between these tables are in the Core Schema document, with Turbine_Role and Turbine_Permission both having a one-to-many relationships with Turbine_Role_Permission table. As Role exists as a container for Permissions, one requirement that may be desired for an application is to show a list of the Permissions in a Role. Please note though, that this is for the sake of example, the Role Object in the org.apache.turbine.om.security package contains a convenience method getPermissions() which will achieve pretty much the same objective. The following is used as an example as it is a relatively familiar problem and uses components that are part of the core Turbine system.
Create the Criteria Object;
Criteria criteria = new Criteria(); criteria.addJoin(PermissionPeer.PERMISSION_ID,RolePermissionPeer.PERMISSION_ID); criteria.addJoin(RolePermissionPeer.ROLE_ID, RolePeer.ROLE_ID); List roles = RolePeer.doSelect(criteria);
The addJoin() method works by joining the tables left to right, the above criteria will return all the Roles that have a Permission. To view the output of the criteria, print it out with criteria.toString().
If instead though we want to view all the Permissions that are connected to a Role we would reverse the join via:
Criteria criteria = new Criteria(); criteria.addJoin(RolePeer.ROLE_ID,RolePermissionPeer.ROLE_ID); criteria.addJoin(RolePermissionPeer.PERMISSION_ID, PermissionPeer.PERMISSION_ID); List permissions = PermissionPeer.doSelect(criteria);
Both the above examples return multiples of the desired table columns. In the first example, the Role will appear for every Permission that is attached to it. If a Role has 10 Permissions, the Role will appear in the returned Vector 10 times. To avoid this problem and so that our returned Vector only returns the Role once despite the number of times it appears, we can use the setDistinct() method in Criteria. For instance, in the Role example:
Criteria criteria = new Criteria(); criteria.addJoin(PermissionPeer.PERMISSION_ID,RolePermissionPeer.PERMISSION_ID); criteria.addJoin(RolePermissionPeer.ROLE_ID, RolePeer.ROLE_ID); criteria.setDistinct(); List roles = RolePeer.doSelect(criteria);
One of the common clauses in an SQL Query is the ORDER BY clause. This can be emulated with the Criteria Object via the addAscendingOrderByColumn(String columnname) and addDescendingOrderByColumn(String columnname) methods. For the Role example, the Role OM Objects in the returned Vector can be ordered by the TURBINE_ROLE columns, ROLE_ID or NAME. If the column chosen to order by is the ROLE_ID, the returned Vector is ordered in numbers, if the NAME is the ordered column, the Vector returns a set of Role Objects, ordered alphabetically by their names.
Criteria criteria = new Criteria(); criteria.addJoin(PermissionPeer.PERMISSION_ID,RolePermissionPeer.PERMISSION_ID); criteria.addJoin(RolePermissionPeer.ROLE_ID, RolePeer.ROLE_ID); criteria.setDistinct(); criteria.addAscendingOrderByColumn(RolePeer.NAME); List roles = RolePeer.doSelect(criteria);
To put all the above together into a useful method, we can query the core Turbine Schema for the Groups that a User has a Role in. This requires a join across the TURBINE_USER_GROUP_ROLE table from the TURBINE_GROUP to the TURBINE_USER table. This will return a Vector of Groups that the User has a Role in.
public List allUsersGroups(RunData data) throws Exception { List groups = new ArrayList(10); //get the User from RunData User user = data.getUser(); //make sure the User has logged in if (user.hasLoggedIn()) { Criteria criteria = new Criteria(); criteria.addJoin(GroupPeer.GROUP_ID, UserGroupRolePeer.GROUP_ID); criteria.addJoin(UserGroupRolePeer.USER_ID, TurbineUserPeer.USER_ID); criteria.add(TurbineUserPeer.USERNAME, user.getUserName()); criteria.addAscendingOrderByColumn(GroupPeer.NAME); criteria.setDistinct(); groups = GroupPeer.doSelect(criteria); } return v; }
At the action or screen level a test can be done to make sure the Vector has size before displaying the Groups in a Velocity Template.
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