Getting started with HQL
November 05, 2009
Hibernate has it's only query language called HQL. I thought I'd put together some simple examples of how you can use it in your ColdFusion 9 applications.
These examples use the cfartgallery database that comes with ColdFusion 9.
To run the code you'll need to create an Application.cfc
component
{
this.ormenabled = true;
this.datasource = "cfartgallery";
}
Artist.cfc
component persistent="true" entityname="Artist" table="ARTISTS"
{
// identifier
property name="ArtistID" fieldtype="id" generator="increment";
// properties
property name="Firstname";
property name="Lastname";
property name="Address";
property name="City";
property name="State";
property name="PostalCode";
property name="Email";
property name="Phone";
property name="Fax";
property name="Password" column="ThePassword";
}
Once those have been created, we can now start to write some code to use them. Below are examples using EntityLoad and the HQL equivalent.
<cfscript>
// get an array of all artists
artists = EntityLoad( "Artist" );
WriteDump( artists );
// here is the HQL version
artists = ORMExecuteQuery( "from Artist" );
WriteDump( artists );
</cfscript>
Pretty simple eh and it looks a lot like SQL. Well, there is a gotcha to look out for. Hibernate is written in Java and is case sensitive. This means that if we type in "artist" instead of "Artist", hibernate will throw an error. This only applies to HQL, EntityLoad("artist") will work fine.
Let's make it a bit more complicated and add a filter and order the result.
<cfscript>
// get an array of all artists with the state of "CO" and order by city then lastname properties
artists = EntityLoad("Artist", {state="CO"}, "city, lastname");
WriteDump( artists );
// here is the HQL version
artists = ORMExecuteQuery("from Artist as artist where artist.State = :state order by city, lastname", {state="CO"});
WriteDump( artists );
</cfscript>
Again, we need to be careful about using the correct case for the Entityname and the properties in the where clause. Strangely, the property names used in the order by clause are not case sensitive.
The other thing you will note is that I've aliased the Artist as artist. We need to do this so that we can reference the State property correctly as artist.State. Using the lowercase version of the actual classname is not necessary but has been adopted as standard practice. You can also miss out the "as" keywork if you want.
Although HQL is a lot like SQL you can also do some pretty amazing things with it. One of the features is that you can retrieve a combination of properties and objects. I hope to blog some more complicated examples later but hopefully you've seen enough to realise that HQL isn't really that scary.
- Posted in:
- SQL
- ColdFusion
- OOP
- Hibernate
No comments
Leave a comment
If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)