ColdFusion ORM: Using HQL

It is important to note in ColdFusion ORM that functions like entityLoadByPK() and entityLoad() are merely convenience functions. Each (so far as I know) simply hides the underlying HQL (Hibernate Query Language). HQL is much like SQL, aside from the fact that not all SQL aggregates and functions are supported as well as we can reference object relational mappings in our HQL as Hibernate understands how to translate them into the appropriate SQL based on our datasource type.

There are two simple ways to execute HQL in a ColdFusion application. The first is with ORMExecuteQuery(). The second is to use a cfquery block with dbtype="hql". For this set of examples we will use ORMExecuteQuery().

We can do a few cool things with HQL. For one, since HQL knows how our objects are mapped we can use the shortcut 'id' to reference a property. Given an ORM object with the cfproperty id definition:

<cfproperty name="chicken_id" fieldtype="id" type="numeric" unsavedvalue="-1" />

The following HQL statements are considered synonymous:

from Chicken where id = 1;
from Chicken where chicken_id = 1;

Please note that the object name 'Chicken' is case sensitive. If you mistype the case here you will receive the error 'org.hibernate.hql.ast.QuerySyntaxException: Chicken is not mapped [ from chicken where chicken_id = 1 ]'.

Another cool thing, we can query on composite objects as well. Given Rooster in a one-to-many relationship with Hens, we can get all roosters who are associated with a red hen like this:

from Rooster rooster where rooster.hen.color = 'red';

Note that the query above uses an inner join by default. Other join types are supported by explicitly specifying the join just like in standard SQL.

Just like in SQL, we can also use 'order by' to sort the resulting collection of ORM objects. To return a collection ordered alphabetically by the Rooster's name:

from Rooster order by name asc;

Lastly (well, certainly not last, but the end of this primer), HQL supports aggregate functions. This works a bit differently in that the result is an array of arrays. This is technically how a query object works in ColdFusion, but without the niceties of struct-type column names. This confuses me as HQL supports 'as' (like select sum(column) as mysum from Object). Maybe that's something coming in CF X? For now expect an array for each object found with an array of properties.