As great as GORM and Hibernate are, sometimes you need more raw power. When Criteria queries and HQL aren't enough, it's good to know that sticking your hands into the SQL abyss is possible. First, a very simple SQL query:
1 2 3 4 5
In GORM (actually Hibernate) you build a
SQLQuery instance using
org.hibernate.session.createSQLQuery(String). A session can be had by calling
Entity.withNewSession(Closure); a method available to all domain classes. Finally, Calling
SQLQuery.list() runs the query and returns the output.
1 2 3 4 5 6 7 8 9 10 11
Right away there's a problem. The result of the query represents each domain class instance as a list of column values. You get a pretty result set instead of domain class instances.
Returning domain classes
With a small change, the same query can be made to return domain class instances.
SQLQuery.addEntity(Class) method tells Hibernate to map the resulting rows into instances of the specified domain class. For this to work, the query must return all of the columns mapped to the domain class properties. To meet this requirement, I used SELECT *, selecting all of the table's columns. The output is what you'd expect.
1 2 3 4 5 6 7 8 9 10 11
It does get more involved when you attempt to return multiple domain class instances per row, which I'll skip in this article. Nevertheless, SQL becomes useful when you need to code a query beyond the capabilities of GORM. I'll introduce two examples: UNION queries and joining sub queries.
You should know up-front that the following is a pretty lousy excuse for a UNION query. Just when I needed a good example, I could not think of one. Since I'll be diving into more of the example domain classes, here's a diagram of the example domain model:
I'll start by adding a convenience method to
Collection using Groovy's meta-class:
1 2 3 4 5 6 7 8
The method is a mash-up of
Collection.withEachIndex(Closure) which I'll use to generate some SQL. The following UNION example joins the output of queries identical except for filtering on different dates.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
The SQL generated from the two dates looks like this:
1 2 3 4 5 6 7 8 9
The two dates result in two query parameters. So the next step is to add the parameters to the
SQLQuery instance. And finally, to execute the query.
1 2 3 4 5
Joining on sub queries
Say my impatient boss asks: I need to know which products were sold on May 1st, 2004 and also on Jan 12th, 2004. I need this for a meeting that starts in 10 minutes. You can do that right?
Such queries aren't as simple as they seem. To solve it I decide to join the
product table into itself via a sub-query so that I can compare the same
Product to itself but with different purchase dates. You can imagine it to look like this:
|PROD A||PROD A DATE||PROD B||PROD B DATE||MATCH ?|
|inch teddy bear||04/29/2004||inch teddy bear||05/01/2004||N|
|inch teddy bear||05/01/2004||inch teddy bear||01/12/2004||Y|
The INNER JOIN causes product A to be returned when there's a match.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
And the beauty is that the output is a list of
Product domain classes (a list of one, in this case):
So there you have it. Proof that it's certainly possible to write SQL queries which return your awesome domain classes :)