Random result set with HQL

Being a night owl I was browsing stack overflow when I came across a post about how to generate a random ordered result set with entityLoad() in ColdFusion. I (as of writing this) still haven't come up with a logical reason why one would _want_ to do so, but to each their own.

So, after a bit of research and testing the short answer is: It's really difficult to do with entityLoad().

However, with HQL, it's super easy:

<cfscript>
 hql = " SELECT DISTINCT artName
   FROM art
   ORDER BY RANDOM() ";
   
 results = ormExecuteQuery(hql);
 
 for( art in results ) {
  writeOutput(art & "<br/>");
 }
</cfscript>

Who knew?

Basically, Hibernate will pass any functions it doesn't recognize to the underlying SQL engine. In this case, I am using Derby's 'random' function to get my random result set.

Neat, huh?