Hibernate Query Language

I've recently become interested in the Hibernate Query Language (HQL) and some of its capabilities. This post summarizes some of my initial experiences for my future reference.


Background:


Until recently, my Data Access Objects (DAOs) would pretty much just use the most basic HQL queries to load an object from my database by its ID:


Query q = session.createQuery("from Clazz c where c.id = 1");
List result = q.list();


or alternatively, load a complete list of all database objects for a particular table:


List result = session.createQuery("from Clazz").list();


If I needed to retrieve a list of objects using a more complex criteria, I could always load the full list, as above, and then return the appropriate objects programmatically, but this is terribly inefficient.


In the past I have used SQL queries (rather than HQL) to more efficiently retrieve just the data I was after. Eg:


session.createSQLQuery("select {c.*} from Clazz {c} where NAME like 'searchName'","c",Clazz.class);


For simple mappings this works fine and uses plain old SQL. However, once you begin to use the more powerful abilities of Hibernate (such as mapping Polymorphic associations), the SQL you need to write becomes more and more difficult and convoluted.


That's when it's finally time to bite the bullet and find out that HQL is actually surprisingly easy to use and very powerful.


HQL Statements - structure:

The structure of a general HQL statement takes the form:

[select ...] from ... [where ...] [group by ... [having ...]] [order by...]


Seeing as the only required clause is from, the simplest HQL query (to retrieve a list of all the items in the table mapped by the class clazz) is:


from clazz


To build on this to create much more complex queries (even when you have Polymorphic associations) is very easy. To describe the queries I've been working with lately, I'm only going to use a very small subset of HQL query options, namely:

• select clazz - tells Hibernate to restrict the objects it returns to the specific class you are interested in.
• select distinct clazz - tells Hibernate to remove duplicates in the list returned.
• join - performs an inner join.'
• left join fetch - performs a left outer join (for eager fetching).

Example:


Suppose we have a class, ParameterValue. There are two different types of ParameterValues, which are subclasses of ParameterValue. Namely "ElementParameterValue" and "UnitParameterValue". We use the Hibernate "Table per class hierarchy" mapping strategy to map this to a single table in our database.

All ParameterValues have a Unit class reference (so each ParameterValue belongs to one and only one Unit). In addition, a ParameterValue has a SaveTag reference (so each ParameterValue also has a unique SaveTag).

In my example, I needed to find a list of SaveTags for a given Unit. That is, the question was "for a particular Unit what SaveTags were represented amongst its ParameterValues?"

The simple HQL that achieves this is:

select distinct saveTag from ParameterValue pv join pv.unit u join pv.saveTag saveTag where u.id = :unitId

The select distinct saveTag tells HQL that the result you want sent back is a List of SaveTag objects with no single SaveTag object represented more than once in the list.

The joins perform inner joins with the required Unit data and the SaveTag data, allowing us to reference (in the where clause) or retrieve these objects by name, respectively.

The where clause defines the search criterion.

All pretty easy stuff - nothing too profound - but I've been playing around with a range of other HQL queries, and they're all as easy as this - even for quite complex datasets, and polymorphic mappings.

The take-home message: Have a look at HQL and don't assume that just sticking with SQL (even if you know it well) is going to be easier.

[NB: This post is a placeholder - I might put some more information about useful HQL queries in future].