Querying associations in Grails with HQL, Criteria and HibernateCriteriaBuilder

A thing I was playing with today was many-to-many relationships in Grails to create a Tag Cloud. To create a Tag Cloud, I must have a set of key/value pairs, each with a label and a value of the label, which could look like this:
['Java': 5, 'Grails': 16, 'Groovy': 12]
But to query this, I need to query a many to many relationship and produce the above result. This blog will describe how to do this with HQL, Criteria and the HibernateCriteriaBuilder.

Domain

My current domain looks like this:

(This picture is unfortunately missing a Tag attribute in the Snippet class).
As you can see, a Snippet can have multiple Tags, while a Tag can belong to multiple Snippets. How can we query this?

Options

We have three options in this case

• HQL
• Hibernate Criteria
• Hibernate Criteria using the HibernateCriteriaBuilder

HQL
The HQL version is not so hard (and neither are the other ones), but we have to start somewhere, so let’s start with this one.
To start at the end: the query looks like this:
select tag.text, count(snippet.id)
from Tag as tag
inner join tag.snippets as snippet
group by tag.text

To test this code, I find it easiest to start up the Grails console:
grails console
and run the HQL there. You can do so by obtaining the SessionFactory from the ApplicationContext, which is done by accessing the implicit ‘ctx’ variable in the console. The complete code looks like this:

def sessionFactory = ctx.sessionFactory
def session = sessionFactory.getCurrentSession()
def query = session.createQuery("select tag.text, count(snippet.id) from Tag as tag inner join tag.snippets as snippet group by tag.text")
def results = query.list()

Which, in my case, returns this (I have two snippets, one with 3 tags and one with 1 tag, which is a duplicate tag of the first snippet):
[["groovy", 1], ["io", 2], ["testing", 1]]
Hibernate Criteria
Since Grails is built on Groovy/Java, Hibernate and Spring, it’s easy to use that power while coming up with a solution to your problem. If you write Java code all day, and writing Hibernate Criteria is something you do daily, this won’t be a problem at all. Just copy and paste your existing Java code into Grails, and you’ll produce something like this:

def sessionFactory = ctx.sessionFactory
def session = sessionFactory.getCurrentSession()
List results = session.createCriteria(Tag.class)
  .setProjection ( Projections.projectionList()
     .add (Projections.groupProperty("text"))
     .add (Projections.rowCount() ) )
  .createCriteria("snippets")
  .list()

Hibernate Criteria using the HibernateCriteriaBuilder
If you, however feel that the above is lacking some style, you might want to check out the HibernateCriteriaBuilder, which is a DSL for building Hibernate Criteria.
When using the DSL, we first need to retrieve the HibernateCriteriaBuilder, which can be obtained from any domain class. After that, you’ll have the full power of Hibernate contained in an easy, readable DSL.

def c = Tag.createCriteria()
def results = c.list {
   projections {
      groupProperty("text")
      rowCount()
   }
   snippets { }
}

Conclusion

As you can see, Grails provides a lot of options to query your domain. Whether you prefer HQL, the safe and sound Criteria, or the new HibernateCriteriaBuilder, the choice is up to you. By leveraging the power of existing frameworks, Grails provides an easy and powerful way to quickly develop an application with a minimal learning curve!