A bit more HQL

November 06, 2009

Yesterday I blogged the basics of HQL, by replicating the functionality of EntityLoad(), so you're probably wondering why you'd bother. Using the same set up as I had in my previous post let's look at some things you can't do with EntityLoad.

We can do "like" searches. Imagine we wanted to get all artists whose surname starts with the letter 'B'


result = ORMExecuteQuery("from Artist as artist Where artist.Lastname like :prefix", {prefix='B%'});
WriteDump(result);

We will get an array of Artist objects returned. It is worth noting that the like search is case sensitive so if you searched with a lowercase 'b', then you'll get no matches. Fear not, we can use the lower function to solve that problem.


result = ORMExecuteQuery("from Artist as artist Where lower(artist.Lastname) like :prefix", {prefix='b%'});
WriteDump(result);

What if we wanted to find out how many Artists resided in a state?


result = ORMExecuteQuery("select Count(artist), artist.State from Artist as artist group by artist.State");
WriteDump(result);

Now the result from the HQL above is different to all the other statements we've tried before, because it returns an array of arrays, not an array of objects.

array
1
array
1 2
2 CA
2
array
1 3
2 CO
3
array
1 2
2 DC
4
array
1 1
2 FL
5
array
1 1
2 GA
6
array
1 1
2 NM
7
array
1 2
2 NY
8
array
1 1
2 OK
9
array
1 1
2 SD

We can reference the values by doing something like:


<cfoutput>
<table>
    <tr>
        <th>State</th>
        <th>Count</th>
    </tr>
<cfloop array="#result#" index="row">
    <tr>
        <th>#row[2]#</th>
        <th>#row[1]#</th>
    </tr>
</cfloop>
</table>
</cfoutput>

Whilst that works it's not particularly nice to have to reference properties by the position in an array. Luckily hibernate has a map class which we can use in this situation.


result = ORMExecuteQuery("select new map(Count(artist) as count, artist.State as state) from Artist as artist group by artist.State");
WriteDump(result);

Our result now looks like:

array
1
struct
count 2
state CA
2
struct
count 3
state CO
3
struct
count 2
state DC
4
struct
count 1
state FL
5
struct
count 1
state GA
6
struct
count 1
state NM
7
struct
count 2
state NY
8
struct
count 1
state OK
9
struct
count 1
state SD

This means that we can write much more descriptive code:


<cfoutput>
<table>
    <tr>
        <th>State</th>
        <th>Count</th>
    </tr>
<cfloop array="#result#" index="row">
    <tr>
        <th>#row["state"]#</th>
        <th>#row["count"]#</th>
    </tr>
</cfloop>
</table>
</cfoutput>

Much better! You might be wondering why I'm using row["state"] instead of row.state. Well, this is because hibernate is written in Java, a case-sensitive language and the struct is actually a java.util.HashMap. When you use row.state, under the hood ColdFusion automatically changes this to an uppercase STATE. By using the square brackets we are telling coldfusion that we want to preserve the case.

1 comment

  1. hey neat trick embedding map() to the HQL query. gonna try that asap.

    Comment by mujimu – August 09, 2010

Leave a comment

If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)

Please note: If you haven't commented before, then your comments will be moderated before they are displayed.