I have been using criteria queries in favor of HQL queries quite a bit latley, so I thought  I would do a quick post of what a query would look like in HQL and then the same query written as a criteria query.

Some of the reasons I like to use criteria queries are

• Readablity
• No building up query strings and concatenating them to build a complex query
• Powerful Object Oriented API

You can learn more about using criteria queries on our wiki at http://wiki.coldbox.org/wiki/Extras:CriteriaBuilder.cfm

OK, on to the example.

First, the HQL version of the query

HQL Query

var qs = "";
qs = qs & "select r from Response as r INNER JOIN Activity as a";
qs = qs & " where r.id=:id and a.uid=:uid";
qs = qs & " order by a.pagenum asc";
var params = {id=rc.id,uid=userID};
var results = RService.executeQuery(query=qs,params=params,offset=0,max=25,asQuery=false);
qs = "count(r.id) from Response as r INNER JOIN Activity as a";
qs = qs & " where r.id=:id and a.uid=:uid";
var count = RService.executeQuery(query=qs,params=params);

So, you can see, there is a lot of concatenating strings, and then we need another version of the query to get the count, so now lets look at how we can do that same thing with a criteria query

Criteria Query

var c = RService.newCriteria();
c.eq('id',rc.id)
   .createAlias('Activity','a')
   .and(c.restrictions.eq('a.uid',userID))
   .order('a.activity','asc');
var count = c.count();
var results = c.list(max=25,offset=0);

What is nice about this is, not only is it more concise, it will make only one trip to the database to return both the count and the list. I encourage you to go to http://wiki.coldbox.org/wiki/Extras:CriteriaBuilder.cfm and check out what you can do with criteria queries and how you can incorporate them into your ORM enabled applications to make finding entities even easier.