Now that we've got our domain model set up, associations created, and relationships managed, I want to delve into the topic of HQL. In case you aren't aware, HQL is an "object-centric" query syntax that gives you very fine-grained control over what Hibernate is doing when you ask it to retrieve objects from the database.

I'll say up front that I am a proponent of using data access objects (DAOs) to encapsulate interaction with the database. In essence, that means the only place that really should know about the database, Hibernate, or HQL is within the DAOs. So the examples I'm going to show are created as methods within a DAO.

We'll start with some simple sorting and filtering. This is a common requirement, and HQL makes it very easy to do:

	
function allEntries()
{
	return OrmExecuteQuery( "FROM 
					BlogEntry b 
				 ORDER BY 
					b.dateCreated DESC" );
}
		

That simply gets all of the blog entries, sorted by the date they were created.

function mostRecentEntry()
{
	var results = OrmExecuteQuery( "FROM 
						BlogEntry b 
					ORDER BY 
						b.dateCreated DESC", 
					false, {maxResults=1} );
	return results[1];
}
		

As the name implies, the above HQL would get the most recent blog entry.

function entriesByUser( id )
{
	return OrmExecuteQuery( "SELECT b 
				FROM 
					BlogEntry b 
					JOIN b.createdBy u
				WHERE 
					u.id=:id",
				{id=arguments.id} );
}
		

Here, we start leveraging the object relationships. In this case, I'm getting all of the blog entries that were created by the specified user.

function entriesWithCommentsFromUserName( userName )
{
	return OrmExecuteQuery( "SELECT DISTINCT b 
				FROM 
					BlogEntry b 
					JOIN b.comments c 
					JOIN c.createdBy u 
				WHERE 
					u.name LIKE :userName",
				{userName='%#arguments.userName#%'} );
}
		

And finally, the above HQL loads all BlogEntries that have a comment which was posted by a user matching a user name search string. This is a fairly contrived example, since I'm not sure a blog application would actually need to do this. But on the ORM mailing list, I've seen several people with the need to filter the objects based on a property from a collection of related objects.

To make it more clear, this is the actual SQL that Hibernate would generate under the hood for this HQL:

09/15 00:58:17 [jrpp-2] HIBERNATE DEBUG - 
select
        distinct blogentry0_.id as id0_,
                blogentry0_.content as content0_,
                blogentry0_.dateCreated as dateCrea3_0_,
                blogentry0_.title as title0_,
                blogentry0_.createdBy as createdBy0_ 
        from
                BlogEntry blogentry0_
                inner join
                BlogComment comments1_ 
                	on blogentry0_.id=comments1_.entryId 
                inner join
                	`User` user2_
                	on comments1_.createdBy=user2_.id
        where
        		user2_.name like ?
            		
09/15 00:58:17 [jrpp-2] HIBERNATE DEBUG - binding '%Tom%' to parameter: 1
		

The SQL itself may seem obvious to anyone familiar with SQL. But since Hibernate is dealing with objects and properties, it can sometimes take a bit of trial and error to work out how traverse properties that are collections of objects.

I will stop here for today. In the next entry, we'll focus on using HQL to optimize the loading of larger object graphs.

Comments Comments (14) | del.ico.us del.icio.us | Digg It! Digg It! | Linking Blogs Linking Blogs | 18001 Views

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)

  • # Posted By Sam Farmer | 9/15/10 9:16 AM

    Great series.

    Out of interest, in your final example, you use DISTINCT. I'm curious as to why since in SQL I don't think I would use it there on a similar query.

  • # Posted By Brian | 9/15/10 9:42 AM

    Thanks, Sam. The DISTINCT is there to make sure I don't get multiple references to the same BlogEntry, in the event that the user posted more than one comment to a given entry. Make sense?

  • # Posted By Sam Farmer | 9/15/10 11:15 AM

    Yes, I think so! :)

  • # Posted By Rick O | 9/15/10 11:59 AM

    This may seem like a troll, but it's an honest question: What have you gained here?

    By moving from cfquery to ORM, you incurred the overhead of having to codify your tables and relationships as CFCs and their properties. This was in anticipation of having to write less SQL and fewer mutator (getter/setter) methods. This might be a net win, depending on the size and complexity of your DAOs. But you've now traded writing SQL for writing HQL, and are effectively back to writing getters.

    So ... why?

  • # Posted By Brian | 9/15/10 2:28 PM

    Rick, I'm not sure which area you're referring to.

    If you mean why am I using a DAO and HQL, it's because even though using Hibernate makes it very easy to do inserts, updates, and deletes, you usually need to help it when it comes to creating the objects. This will be more obvious in the next entry, but unless you specify what you need in a given situation, you're usually relying on lazy loading. That means you'll have a lot of queries to the underlying database that you could have avoided if you had specified what you want though HQL. Hibernate can't read your mind, you have to tell it what you want.

    If you mean why use an ORM, I'll give a brief answer, but if you want a more involved discussion on that general topic, I'd recommend posting the question to the ORM list. But its real purpose is to allow you to deal with objects and the business logic they should contain. Without objects, you're dealing with "dumb" data structures.

    Hibernate does indeed eliminate a huge amount of manual SQL. Even beyond its ability to do cascading inserts, updates, and deletes, most HQL is much more concise than the equivalent SQL. But that's not really the point. The goal of Hibernate isn't to "write less SQL" (even though it does let you write less SQL), it is to allow you to work with objects.

  • # Posted By Rick O | 9/15/10 6:00 PM

    "But its real purpose is to allow you to deal with objects and the business logic they should contain."

    That seems like misdirection -- whether your data access is done with SQL or ORM has nothing to do with whether your business logic is correctly encapsulated.

    "Without objects, you're dealing with "dumb" data structures."

    This seems like a truer answer -- if you are comfortable with and prefer an object-based approach to your application, then ORM is the obvious victor.

    I keep coming back to how I (should) frame this to my students when I show them ORM and they ask "but why would we use this?". Currently, I tell them that ORM can save time writing SQL, as we don't go into HQL as it is overkill for the queries we'll use in our projects. I also say that ORM is nice for the students who loved ActionScript (or have a Java background from outside of our degree), and feel more comfortable with objects and methods than with managing raw data. I point out that ORM is awesome for scaffolding and for very simple schemas, but that the benefits get fuzzy with complex queries and schemas.

    But none of those is a killer answer -- one that will make my students fall madly in love with ORM. I've found it difficult in class to show concrete, universal benefits to using ORM, so I keep hoping that it's just that I'm missing some critical piece that still needs to click into place.

  • # Posted By Brian | 9/15/10 10:59 PM

    "That seems like misdirection -- whether your data access is done with SQL or ORM has nothing to do with whether your business logic is correctly encapsulated."

    You misunderstood me. I'm not talking about whether the objects were populated from SQL queries or by an ORM framework. The point is that with an ORM framework, you're dealing with objects automatically, instead of having to do all the translation from persisted state back to objects manually.

    It's true that in simple applications the advantages are harder to demonstrate. As with MVC or IoC frameworks, until they smash into the wall that those frameworks are addressing, it may seem like they're not saving much work. And for very small or simple applications, none of these frameworks may offer much benefit.

    But in more complex systems, the only answer to someone who still asks "why would I use this" is to challenge them to build an equivalent system without ORM. Once they understand the gigantic amount of work that an ORM does for them, freeing them to work on actual business problems instead of mundane and repetitive boilerplate plumbing code, the benefits should be much more clear.

  • # Posted By Rick O | 9/16/10 9:24 AM

    That's fair enough. I tell them basically the same thing when we talk about IoC frameworks and DI -- it's hard to see the benefit for small apps, but for huge apps it's pretty much a requirement.

    I need to come up with an example of a system big enough to make all of the advanced architecture worth the time, but also understandable to BS-level students. I've tried talking about manufacturing and warehousing systems in the past, but most of my students have never had a retail job, and so have no real concept of what is required by ERP systems. And most "web scale" stuff doesn't get you outside of the range of a dozen tables. I need a middle ground.

    Thanks for your input, it's much appreciated.

  • # Posted By Ryan Ricard | 10/6/10 3:45 PM

    Is it possible to specify dynamic order by params? For instance:

    <cffunction name="listAuthors" access="public" output="false" returntype="array">
          <cfargument name="sort" required="false" default="lastName" />
          <cfargument name="order" required="false" default="asc"/>

          <cfreturn ORMExecuteQuery("from Author order by ? ?",[arguments.sort,arguments.order]) />

       </cffunction>

    I am starting to think this is a short-coming of ORMExecuteQuery.

  • # Posted By Brian Kotek | 10/6/10 4:21 PM

    Ryan, I'm not sure what you're passing as the sort and the order, but there's nothing wrong with building up the actual query dynamically. So if you need logic, or loops, or whatever to build up your HQL, that's fine.

    So assuming these two arguments are actually values you want to order by:

    <cfreturn ORMExecuteQuery("from Author order by #arguments.sort# #arguments.order#") />

  • # Posted By Ryan Ricard | 10/7/10 9:02 AM

    Thank you Brian,

    Your suggestion works. From my understanding dynamically generated HQL is susceptible to ORM injection: http://bit.ly/2jBiaN (Jason Dean post)

    I would like to limit risk by using bind parameters to sort and order dynamically; however, it appears bind parameters are reserved only for where clause parameters.

    In my example 'sort' is a column name and 'order' is asc/desc, they are passed via URL parameters on a grid sortable header click event.

    I would like to be able to write something like this:

    ORMExecuteQuery("from Author order by :sort :order",{sort=arguments.sort,order=arguments.order})

    OR

    ORMExecuteQuery("from Author order by ? ?",[arguments.sort,arguments.order])

    I'm receiving this error when attempting to run the code above: "org.hibernate.hql.ast.QuerySyntaxException: unexpected token"

  • # Posted By Brian Kotek | 10/7/10 9:16 AM

    Correct, bind variables only work in the WHERE clause. You can't use them for ORDER BY elements (whether you're using OrmExecuteQuery() or even plain SQL in CFQUERY).

    The blog entry you referenced is talking about injection into WHERE parameters, to try and return more records than intended. That can't happen in ORDER BY elements so that isn't a concern. But, as with any user-provided variable being used in code, you'll probably want to validate the value and strip out any suspicious characters (to stop them from trying to append an additional select statement or something).

  • # Posted By Ryan Ricard | 10/7/10 9:49 AM

    Thank you for the clarification Brian!

  • # Posted By Muhammad Tariq | 11/23/13 6:40 AM

    Please explain how I can add two different values like
    FROM Users
             WHERE
                confirmedEmailAddress = 0
             AND
                dateTimeRegistered <= :Today   
             AND   
                dateTimeRegistered >= :UserRegistrationDateTime   
             ",
             {Today = DateFormat(Now(), 'yyyy-mm-dd')}, {UserRegistrationDateTime = DateAdd('d',regdays,CreateODBCDateTime(now()))});
             );