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
package com.emmanuelrosa.gormtut

Product.withNewSession { session ->
    session.createSQLQuery('SELECT * FROM Product').list()
}

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.

output
1
2
3
4
5
6
7
8
9
10
11
[
    [1, 0, '8 inch teddy bear, comes with cap and jacket', '8 inch teddy bear', 5.99, 1]
    [2, 0, '12 inch teddy bear, comes with cap and jacket', '12 inch teddy bear', 8.99, 1]
    [3, 0, '18 inch teddy bear, comes with cap and jacket', '18 inch teddy bear', 11.99, 1]
    [4, 0, 'Fish bean bag toy, complete with bean bag worms with which to feed it', 'Fish bean bag toy', 3.49, 3]
    [5, 0, 'Bird bean bag toy, eggs are not included', 'Bird bean bag toy', 3.49, 3]
    [6, 0, 'Rabbit bean bag toy, comes with bean bag carrots', 'Rabbit bean bag toy', 3.49, 3]
    [7, 0, '18 inch Raggedy Ann doll', 'Raggedy Ann', 4.99, 3]
    [8, 0, '12 inch king doll with royal garments and crown', 'King doll', 9.49, 5]
    [9, 0, '12 inch queen doll with royal garments and crown', 'Queen doll', 9.49, 5]
]

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.

session.createSQLQuery('SELECT * FROM product').addEntity(Product).list()

The 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.

output
1
2
3
4
5
6
7
8
9
10
11
[
    com.emmanuelrosa.gormtut.Product : 1, 
    com.emmanuelrosa.gormtut.Product : 2, 
    com.emmanuelrosa.gormtut.Product : 3,
    com.emmanuelrosa.gormtut.Product : 4,
    com.emmanuelrosa.gormtut.Product : 5,
    com.emmanuelrosa.gormtut.Product : 6, 
    com.emmanuelrosa.gormtut.Product : 7, 
    com.emmanuelrosa.gormtut.Product : 8, 
    com.emmanuelrosa.gormtut.Product : 9
]

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.

UNION 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:

Domain class diagram

I'll start by adding a convenience method to Collection using Groovy's meta-class:

1
2
3
4
5
6
7
8
Collection.metaClass.collectWithIndex = {Closure transform ->
    def index = 0
    delegate.collect {
        def result = transform(it, index)
        index++
        result
    }
}

The method is a mash-up of Collection.collect(Closure) and 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
def dates = [
    '2004-05-01 00:00:00', 
    '2004-01-12 00:00:00'
].collect { Date.parse('yyyy-MM-dd HH:mm:ss', it) }

def sql = dates.collectWithIndex { date, index ->
    """
    SELECT p.* FROM purchase_order AS po
    INNER JOIN item AS it ON it.order_id = po.id   
    INNER JOIN product AS p ON it.product_id = p.id
    WHERE po.order_date = :date_$index
    """
}.join('UNION')

def query = session.createSQLQuery(sql)

The SQL generated from the two dates looks like this:

1
2
3
4
5
6
7
8
9
SELECT p.* FROM purchase_order AS po
INNER JOIN item AS it ON it.order_id = po.id   
INNER JOIN product AS p ON it.product_id = p.id
WHERE po.order_date = :date_0
UNION
SELECT p.* FROM purchase_order AS po
INNER JOIN item AS it ON it.order_id = po.id   
INNER JOIN product AS p ON it.product_id = p.id
WHERE po.order_date = :date_1

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
dates.eachWithIndex { date, index ->
    query.setParameter("date_$index", date)
}

query.addEntity(Product).list()

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
def dates = [
    '2004-05-01 00:00:00', 
    '2004-01-12 00:00:00'
].collect { Date.parse('yyyy-MM-dd HH:mm:ss', it) }

def sql = """
SELECT p.* 
FROM purchase_order AS po
    INNER JOIN item AS it ON it.order_id = po.id   
    INNER JOIN product AS p ON it.product_id = p.id        
    INNER JOIN (
        SELECT p.* 
        FROM purchase_order AS po
            INNER JOIN item AS it ON it.order_id = po.id   
            INNER JOIN product AS p ON it.product_id = p.id
        WHERE po.order_date = :date_1
    ) AS p2 ON p.id = p2.id
WHERE po.order_date = :date_0
"""

def query = session.createSQLQuery(sql)
    
dates.eachWithIndex { date, index ->
    query.setParameter("date_$index", date)
}

query.addEntity(Product).list()

And the beauty is that the output is a list of Product domain classes (a list of one, in this case):

output
[com.emmanuelrosa.gormtut.Product : 1]

So there you have it. Proof that it's certainly possible to write SQL queries which return your awesome domain classes :)