Blog

Curt Gratz

February 08, 2012

Spread the word


Share your thoughts

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.

Add Your Comment

Recent Entries

BoxLang 1.0.0 Beta 27 Launched

BoxLang 1.0.0 Beta 27 Launched

We are excited to bring you Beta 27 with enhanced XML handling, new CLI app support, improved error handling, and expanded interoperability with Java and CFML runtimes. We've also added new HTTP event hooks, improved caching strategies, and a streamlined class resolution process to make your applications more performant, even in debug modes.

Luis Majano
Luis Majano
January 31, 2025
Into the Box 2025 | Plan Your Trip With Us!

Into the Box 2025 | Plan Your Trip With Us!

Are you ready to join us for Into the Box 2025 from April 30th to May 2nd in Washington, D.C.? Let’s make your trip planning as smooth as possible. Here you’ll find Airfare discounts, Hotel Deals and fun things to do to the the best out of your trip to D.C.

Maria Jose Herrera
Maria Jose Herrera
January 30, 2025