HQL tip - use a HashMap

I've mentioned this before as part of a previous blog post, but as it came up on the cf-orm-dev list the other day, I thought I'd create a separate post.

When you use HQL, you can choose to select specific properties from an entity (for performance) and also if you want to get aggregate values (such as count and sum). The problem with doing this is that you end up with an array of arrays which isn't very nice to work with. To solve this probblem you can use a map which is similar to a good old ColdFusion struct.

Here's a quick example:

result = ORMExecuteQuery( "
    select Count(state), state 
    from Artist
    group by state
" );

If we dump the result we'll see this:

As you can see we have an array nested inside an array. This means we'd need to reference the values in our code by it's position in the array.

<table>
  <tr>
    <th>State</th>
    <th>Artists</th>
  </tr>
<cfloop array="#result#" index="state">
  <tr>
    <th>#state[2]#</th>
    <th>#state[1]#</th>
  </tr>
</cfloop>
</table>

To get hibernate to return each value as a name-value pair is to use a java HashMap in the HQL statement like so:

result = ORMExecuteQuery( "
    select new map( Count(state) as artistcount, state as name ) 
    from Artist
    group by state
" );

If we dump it out it'll look like:

Great, now we can reference the values like so:

<table>
  <tr>
    <th>State</th>
    <th>Artists</th>
  </tr>
<cfloop array="#result#" index="state">
  <tr>
    <th>#state.artistcount#</th>
    <th>#state.name#</th>
  </tr>
</cfloop>
</table>

Well, actually, no you can't. If you run this code you'll get a nasty Java error: "Element ARTISTCOUNT is undefined in a Java object of type class java.util.HashMap."

This is because although the dump of the result from the HQL looks like a normal ColdFusion struct, it is infact a java.util.HashMap and Java is a case-sensitive language. If you dump a normal ColdFusion struct you may notice that all the keys are uppercase. So when you type state.artistcount, ColdFusion is converts that to uppercase and we created lowercase variable names.

We have two ways to solve this problem, the first is to write the HQL to return uppercase variable names:

result = ORMExecuteQuery( "
    select new map( Count(state) as ARTISTCOUNT, state as NAME ) 
    from Artist
    group by state
" );

The second solution is to reference the variables using quotes. This tells ColdFusion that we want to preserve the case. Here's what the code would look like.

<table>
  <tr>
    <th>State</th>
    <th>Artists</th>
  </tr>
<cfloop array="#result#" index="state">
  <tr>
    <th>#state["artistcount"]#</th>
    <th>#state["name"]#</th>
  </tr>
</cfloop>
</table>

• Posted in:
• ColdFusion
• Hibernate

13 comments

1. Great tip - thank you!
   
   Comment by Raymond Camden – September 13, 2010
2. Great tip, John!
   
   Comment by Marc Esher – September 13, 2010
3. great tip indeed! cool!
   
   Comment by Henry Ho – September 13, 2010
4. Woah - did I post something useful? I think I should quit blogging now that I've reached my peak :D
   
   Comment by John Whish – September 13, 2010
5. excellent...very helpful
   
   Comment by Daniel Schmid – September 14, 2010
6. great tip, handy to know!
   
   Comment by Olly Jones – September 30, 2010
7. I just tried this on CF 9.01 and it works
   
   <cfquery dbtype="hql" name="users">
   select new map( FirstName as FName, LastName as LName ) from User
   </cfquery>
   
   Comment by Siddiq – October 01, 2010
8. John,
   
   Great tip. This is very useful.
   
   Thanks,
   Curt
   
   Comment by Curt Gratz – February 10, 2011
9. Thanks again John for your help. I have spent days trying to name specific properties from an entity and you have solved the issue within minutes.
   
   Comment by Tom Small – February 27, 2012
10. Thanks! Out of another pickle :).
    
    Comment by Robert Pailliotet – February 28, 2012
11. Another way to get a map() to act like a CF structure is to use duplicate().
    
    So, in your example, we would use
    
    result = duplicate(ORMExecuteQuery( "
    select new map( Count(state) as ARTISTCOUNT, state as NAME )
    from Artist
    group by state
    " ));
    
    Comment by Scott Stroz – April 26, 2012
12. Thanks again John for a great tip
    
    Comment by Tom Small – April 26, 2012
13. @Scott - that's pretty neat!
    
    For other readers, by using Duplicate you can have the aliased names in any case and ColdFusion will still be able to read them. For example, this works:
    
    <cfset foo = Duplicate( ORMExecuteQuery( "select new map( tagid as ID, title as title ) from Tag where id = 2", true ) )>
    <cfdump var="#foo.id# - #foo.TITLE#">
    
    Comment by John Whish – April 27, 2012