John regularly blogs about ColdFusion, JavaScript and other web technologies and contributes to several FOSS projects. His hobbies include writing in the third person. John is a DZone MVB and is not an employee of DZone and has posted 32 posts at DZone. You can read more from them at their website. View Full User Profile

View ORM queries in page debugging

06.22.2011
| 7012 views |
  • submit to reddit

One of the things that I really love about Coldfusion 9 is the hibernate integration (ORM). The only thing that irriates me about using the ORM features is that the generated SQL is hidden away from you the developer. You can set up your ColdFusion application to log the generated SQL to a file or to the console (using this.ormsettings.logSQL), but I really like having it in the ColdFusion debugging template like my straight SQL queries. Also, if I have to track down a wierd issue on a production server, then turning on hibernate logging is not really practical.

I use HQL a lot in my applications. Using HQL instead of EntityLoad provides me with much more functionality and is very SQL like to read. The EntityLoad method is one level of abstraction that I'm happy to skip (but that doesn't mean I'm anti it!) One of the benefits of using HQL is that I can see my HQL in the ColdFusion debugging output in my browser (or the excellent ColdFire).

ColdFusion provides the ORMExecuteQuery method for use with HQL, but this doesn't let you view the HQL. Instead I use a good old cfquery (you'll need CF9.01 to do this).

// using EntityLoad to get published Articles ordered by date
result = EntityLoad( "Article", { published=true }, "date desc" );

// HQL version of above
result = ORMExecuteQuery( "from Article where published=true order by date desc" );

// cfquery version of above
<cfquery name="result" dbtype="hql">
    from Article where published=true order by date desc
</cfquery>

Now I know what you're thinking; but I don't want to use tags! No problem - just use the cfscript version:

q = new Query();
q.setDBType( "hql" );
q.setSQL( " 
    from Article where published=true order by date desc
" );
result = q.execute().getResult();

If you want to know how to use parameters then you might want to check out two of my previous blog posts: View HQL in your ColdFusion debugging info and cfquery in cfml with parameters


 

References
Published at DZone with permission of John Whish, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)