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

Into the Box 2024 Last Early Bird Days!

Into the Box 2024 Last Early Bird Days!

Time is ticking, with less than 60 days remaining until the excitement of Into the Box 2024 unfolds! Don't let this golden opportunity slip away; our exclusive Early Bird Pricing is here for a limited time only, available until March 31st. Why wait? Secure your seat now and take advantage of this steal!

Maria Jose Herrera
Maria Jose Herrera
March 20, 2024
Ortus February Newsletter 2024

Ortus February Newsletter 2024

Welcome to Ortus Solutions’ monthly roundup, where we're thrilled to showcase cutting-edge advancements, product updates, and exciting events! Join us as we delve into the latest innovations shaping the future of technology.

Maria Jose Herrera
Maria Jose Herrera
March 06, 2024
Unveiling the Future of CFML Development - 3rd Round of Sessions

Unveiling the Future of CFML Development - 3rd Round of Sessions

Welcome back to our journey into the future of CFML development! As excitement continues to build for Into the Box 2024, we're thrilled to bring you the latest updates on what promises to be a transformative event. Continuing our blog post series, let's delve deeper into the third wave of session releases and discover the key surprises awaiting attendees. Learn More

Maria Jose Herrera
Maria Jose Herrera
March 01, 2024