I’m not sure if I just like HQL (Hibernate Query Language) because it is so close to SQL or because I don’t get the otherwise fabulous Grails HibernateCriteriaBuilder well enough to be sure of some of the more tricky queries. I do enjoy using the criteria builder but seem to hit limitations every so often, perhaps it is just that the type of queries that I happen to be writing lend themselves well to HQL.
Regardless I generally have these basic requirements when building a query:
- Easy to read and understand in code.
- Easy to read and understand when printed (e.g when displayed in a report).
- Easy to execute with correct paginateParams and namedParams.
- Easy to change a clause and execute again.
Of course HQL is just a string and can be simply built and handled with standard string manipulation. But this is repetitive and not very elegant when trying to satisfy all of the above conditions. Since Groovy is so good at creating DSL’s I thought that a HqlBuilder was a worthy concept.
The builder allows HQL to be written like this:
def q = new HqlBuilder().query { select 'count(distinct book)' from 'Book as book' where 'book.id > 100' } |
and executed in Grails like this:
GrailsDomainClass.executeQuery(q.query, q.namedParams, q.paginateParams) |
The code can be found here:
HqlBuilder.groovy
HqlBuilderTests.groovy
Real world comparison:
CriteriaVsHqlBuilderVsString.groovy
Usage:
Packages are used so place the files in $currentDir/net/kromhouts/ sub directories.
GroovyConsole:
– run from $currentDir or `groovyConsole -cp $currentDir`
– import net.kromhouts.*
– def q = new HqlBuilder(debug:true)
Groovy:
– from $currentDir simply run `groovy net/kromhouts/HqlBuilderTests.groovy`
– or from a Grails project root: `groovy -cp src/groovy/ test/unit/net/kromhouts/HqlBuilderTests.groovy`
Grails:
– place files in grails project dir structure (src/groovy & test/unit) and run `grails test-app unit: HqlBuilder`
– In application classes simply import net.kromhouts.HqlBuilder and use as per examples.
Gavin Kromhout:
Thank you for visiting.
Do look around.
Do leave a comment.
Groovy HqlBuilder…
I’m not sure if I just like HQL (Hibernate Query Language) because it is so close to SQL or because I don’t get the otherwise fabulous Grails HibernateCriteriaBuilder well enough to be sure of some of the more tricky queries. I do enjoy using the crite…
This is really bizarre. Are we the same person? I have also written a plugin called HqlBuilder which is very similar to yours. It started off from me being sick of the default Grails criteria builder not working for many-to-many pagination (it uses joins), so I wrote my own that would supported correlated exist subqueries. It then grew into a full-fledged replacement for criteria/hql in all of my Grails applications.
Also, your post on “Grails Service and Controller Interface” is nearly the *exact* way I structure my own services and controllers, right down to the map-as-multiple-return-value idiom complete with the “errors” entry, directly passing the request params to the service, and using returned error codes in the controller to determine redirect/render behavior. Amazing!
He-he, parallel universe?
I actually submitted my HqlBuilder to grails-dev and user lists:
http://grails.1312388.n4.nabble.com/Changing-the-default-behaviour-of-save-td2875400i40.html#a3005000 but there was not a massive amount of interest so I created a poll to see what percentage are using HQL:
http://grails.1312388.n4.nabble.com/HQL-in-Grails-poll-td3013605.html
Would you be interested in uploading your HqlBuilder plugin to github?
The Grails Service and Controller interface has received more attention and I think a few people are using this, something like this needs to become best practise in Grails.