Ask a Jedi: Using HQL versus other ORM Functions

This post is more than 2 years old.

Robert asked a pretty interesting question earlier in the week:

Currently going through the lynda.com ColdFusion 9 tutorial online, and was introduced to "Hibernate Query Language" usage inside ColdFusion as a substitute for writing SQL queries. This was the first time I was introduced to this alternative query language so I checked out "Chapter 14. HQL: The Hibernate Query Language" in the Docs on jboss.org site. What I don't know is this: is this important to learn in light of the ORM direction CF9 is taking? Can you comment how how much I should learn/use HQL over SQL when using the ORM capabilities inside CF9?

My first reply to him was to ask for some clarification. It felt like he was implying that using HQL wasn't working with ORM. His response to this was also interesting:

I guess I separated (wrongly) the use of generated ORM cfc functions (and calling those getter/setter functions in an app.) away from the "traditional" querying of table data for result pages. I think of SQL as something you do to/with your database directly, and ORM as something you do to/with data objects indirectly touching your database. So, I quess if I drink the CF9 ORM framework "koolaid", then the straw has to be HQL, right? I don't really know how different HQL is from SQL. It could be I'm blowing smoke here.
So before I even begin to respond to this, let me just state right away - I am very much the ORM newbie. If your introduction to ORM was with ColdFusion 9, I've only got maybe one year on you. I've been using CFCs for many years and I've only recently begun to feel very comfortable and knowledgeable on them. ORM? Shoot - I might as well be a rank amateur. Maybe in 4-5 years I'll feel like an expert, but for now, what your going to get is my opinion, and what seems right to me now. (I'm also sure my readers won't let me down. I know a few of you have opinions, right?)

I take from this, again, that there is some concern that perhaps using HQL may not be "The Right Way". As we know, there is never one right way, but certainly there are things that are normally recommended. So you may ask - when does it make sense to use entityLoad (or entityLoadByPk, or entityLoadByExample) versus HQL with ormExecuteQuery?

From what I see - entityLoad is useful for fetching a precise set of entities. All people, or all people where gender=female. This would be my normal way of browsing through the entire set of data, or a specific subset defined by exact property values.

Anything "fuzzy" though, for example, all people with an age less than 30, or all people whose name is like "cam", will require an HQL call.

As far as I know, these are hard and fast rules. You simply can't get data based on a non-specific filter without resorting to a HQL query (please correct me if I'm wrong). Therefore there are times when the choice is out of your hands.

Don't forget that your result is still an array (or single) set of objects. Your still working with entities versus an old school query result set. Also don't forget that your HQL is working with your aliased data. You still have that layer above the pure tables. If a table changes in the future, once your entity CFC is updated to reflect that the column behind the scenes has changed, your HQL still will work fine.

I hope this answers your question, and again, I'm definitely open to some discussion here. If I'm completely off base, school me!

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Mark Mandel posted on 10/22/2009 at 7:38 AM

I think Ray has done a really good job of explaining what is going on here, but there are a couple of things I would like to emphasise, in regards to your question of "how different HQL is from SQL".

* As Ray mentioned, HQL will return an array of Objects. This is a fairly large distinction, as you still have access to your business logic that is contained in your Objects, as opposed to a query result set.

* Since HQL is Object based, you can traverse inheritence hierarchies much easier than you would with native SQL. Something like 'from com.Workers' HQL statement is very powerful when you have 'RedWorkers' and 'BlueWorkers' that inherent from 'Workers'

* You generally have to do less work in HQL than you would have to do in SQL, because HQL knows about the relationships between Objects. So really, all you need to know about is Object relationships. This means you spend less time concept shifting between Object-think and Relational-think, which is a Good Thing.

* If you don't want to get back Objects from HQL, you can request just properties as well (although I've never had the need).

Hopefully that is some other interesting points to consider.

That all being said - there are times when native SQL IS the better choice, it's just a pro/con game, like anything else.

Comment 2 by Barney posted on 10/22/2009 at 7:46 AM

I'd say you're pretty much right on the money. entityLoad (and derivatives) is for a very specific, all-or-nothing request. HQL is when you need more flexibility, either with ORed constraints ("from person p where p.sex = 'male' or p.age > 60"), joins ("from person p where p.address.city = 'Sherwood'"), or you don't actually want entities ("select avg(age) from person p").

Using raw Hibernate, I believe projections and criteria will give you 100% coverage over HQL's functionality, but to my knowledge CF9 doesn't expose the Critera API. You can certainly hit it through the Session if you wanted, however that's not very CF-ish, so I'd say HQL is the way to go for anything complex.

You can actually do EVERYTHING with HQL, if you want. "from person p where p.id = 23" is identical to entityLoad("person", 23) functionality-wise. However, the latter is a bit more direct and allows Hibernate to bypass some of it's internal machinery, making it marginally faster (if your stopwatch has a nanosecond hand).

Aside from retrieve-entity-by-id queries, I tend to use HQL for everything. I find it very expressive, especially with dot-traversal; you can very clearly describe very complicated queries.

Unfortunately, Adobe sort of screwed the pooch and didn't give us dbtype="orm" on the CFQUERY tag so you could nest your HQL and use conditional logic to create it inline. You can fake it with a CFSAVECONTENT/ormExecuteQuery() pair, or build a string in a variable, but neither approaches the smoothness of CFQUERY.

Comment 3 by Raymond Camden posted on 10/22/2009 at 7:50 AM

What about simply making a hqlquery custom tag?

Comment 4 by Mark Mandel posted on 10/22/2009 at 7:54 AM

Now Ray, you're talking way too sensible! ;o)

Comment 5 by Barney posted on 10/22/2009 at 8:05 AM

A custom tag will do it, but whomever uses the custom tag has to manually deal with CFOUTPUT semantics. To put that another way, the tag will *sometimes* require CFOUTPUT tags to be used around it's body based on the context it's invoked in. It's a pretty fundamental issue with custom tags. I've requested fixing it, but I've heard nothing.

So ultimately a built-in feature would provide a WAY better experience, not to mention a more consistent one.

Comment 6 by Raymond Camden posted on 10/22/2009 at 4:56 PM

Even with that - I may still whip up a custom tag. I mean shoot - Transfer has it - and I think it works well there too. Plus - I just like writing custom tags. :)

Comment 7 by Shannon Hicks posted on 10/22/2009 at 7:46 PM

Ray,

If you're going to do a custom tag, don't forget to implement the caching features that ormExecuteQuery() has. Also, please implement filters (http://groups.google.com/gr...

Comment 8 by Richard posted on 2/17/2010 at 3:56 PM

Hi Ray,
on this subject of ORM/Hibernate in CF9, i have experimented in a small project and I really like it. After testing a little bit of OO in CF, I would like to use a proper MVC in my future applications - where does ORM/HIBERNATE fit in with Model-Glue or any other frameworks and what would be the starting set up...please assume am newbie to the MVC?
Cheers

Comment 9 by Raymond Camden posted on 2/18/2010 at 5:24 PM

To me, this is an apples and oranges question. ORM helps abstract your SQL. It does not help/hinder MVC. You can write an MVC app that makes use of ORM at the model layer and you can write an ORM-enabled app that is old school procedural. One does not imply the other.

Comment 10 by Mark Ireland posted on 2/20/2011 at 4:52 PM

What is your impression of cfScaffold?
cfscaffold.riaForge.com

Comment 11 by Raymond Camden posted on 2/20/2011 at 9:59 PM

I haven't used it. It looks like it would be useful if you did NOT have ORM baked in. Before CF9 I was primarily a Transfer user.

Comment 12 by Mark Ireland posted on 2/21/2011 at 3:35 AM

If you get a chance please check it out. I think it needs CF9 with ORM on.

Comment 13 by Raymond Camden posted on 2/21/2011 at 7:13 AM

Sorry - are you saying it only works with CF9/ORM? Or are you asking me if it requires that? If so - it makes sense to contact the project owner and ask him/her if that's a requirement. Or am I misreading you?

Comment 14 by Mark Ireland posted on 3/4/2011 at 5:27 AM

Yes, it needs CF9/ORM and I am interested in your opinion on the demo.

Comment 15 by Raymond Camden posted on 3/4/2011 at 2:13 PM

I haven't used it - so I can't speak to it. Maybe someone else in the thread.