Grails Tutorial for Beginners - HQL Queries (executeQuery and executeUpdate)

Jan 26, 2015 Beginner comments

This Grails tutorial will teach the basics of using HQL. Grails supports dynamic finders which makes it convenient to perform simple database queries. But for more complex cases, Grails provides both Criteria API and HQL. This tutorial will focus on the latter.

Introduction

It is well known that Grails sits on top of Spring and Hibernate - two of the most popular Java frameworks. Hibernate is used as the underlying technology for the object-relational mapping of Grails (GORM).

Hibernate is database agnostic. It means that since Grails is based on it, we could write applications that is compatible with most popular databases. We don't need to write different queries for each possible database.

The easiest way to perform database queries is through dynamic finders. It's simple and very intuitive. Check my previous post for a tutorial on this topic. Dynamic finders however are very limited. It may not be suitable for complex requirements and cases where the developer needs a lower level of control. HQL is a very good alternative as it is very similar to SQL.

HQL is fully object oriented and understands inheritance, polymorphism and association. Using it will provide a very powerful and flexible API yet preserving your application to be database agnostic. In Grails, there are two domain methods to use to invoke HQL

executeQuery - Executes HQL queries (SELECT operations)
executeUpdate - Updates the database with DML-style operations (UPDATE and DELETE)

executeQuery

Here is a sample Domain class that we will query from:

package asia.grails.test
class Person {
    String firstName
    String lastName
    int age
    static constraints = {
    }
}

Retrieve all domain objects

This is the code to retrieve all Person objects from the database:

def listOfAllPerson = Person.executeQuery("from Person")

Notice that:

executeQuery is a method of a Domain class and used for retrieving information (SELECT statements)
Similar to SQL, the from identifier is required
Instead of specifying the table, we specify the domain class right after the from keyword. We could also write the query like this
```
def listOfAllPerson = Person.executeQuery("from asia.grails.test.Person")
```
It is valid not to specify select clause. By default, it will return the object instances of the specified Domain class. In the example, it will return a list of all Person objects.

Here is a sample code of how we could use the result

listOfAllPerson.each { person ->
    println "First Name = ${person.firstName}"
    println "Last Name = ${person.lastName}"
    println "Age = ${person.age}"
}

Since listOfAllPerson is a list of Person instances, we could iterate over it and print the details.

Select clause

When the select clause is explicitly used, HQL will not return a list of domain objects. Instead, it will return a 2 dimensional list. Here is an example assuming that at least 1 record is in the database:

def list = Person.executeQuery("select firstName, lastName from Person")
def firstPerson = list[0]
def firstName = firstPerson[0]
def lastName = firstPerson[1]
println "First Name = ${firstName}"
println "Last Name = ${lastName}"

The variable list will be assigned a list of items. Each item is a list that corresponds to the value as enumerated in the select clause.

The code can also be written like this to help visualize the data structure:

def list = Person.executeQuery("select firstName, lastName from Person")
def firstName = list[0][0]
def lastName = list[0][1]
println "First Name = ${firstName}"
println "Last Name = ${lastName}"

Where clause

Just like SQL, we can filter query results using where clause. Here are some examples:

People with surname Doe

def peopleWithSurnameDoe = Person.executeQuery("from Person where lastName = 'Doe'")

People who are at least 18 years old

def adults = Person.executeQuery("from Person where age >= 18")

People having first name that contains John

def peopleWithFirstNameLikeJohn = Person.executeQuery("from Person where firstName like '%John%'")

Group clause

Group clause is also permitted. The behavior is similar to SQL. Here is an example:

def list = Person.executeQuery("select age, count(*) from Person group by age")
list.each { item ->
    def age = item[0]
    def count = item[1]
    println "There are ${count} people with age ${age} years old"
}

This will print all ages found in the table and how many people have that age.

Having clause

The having clause is useful to filter out the result of a group by. Here is an example:

def list = Person.executeQuery(
    "select age, count(*) from Person group by age having count(*) > 1")
list.each { item ->
    def age = item[0]
    def count = item[1]
    println "There are ${count} people with age ${age} years old"
}

This will print all ages found in the table and how many people have that age, provided that there are more than 1 person in the age group.

Pagination

It is not good for performance to retrieve all records in a table all at once. It is more efficient to page results. For example, get 10 records at a time. Here is a code sample on how to do that:

def listPage1 = Person.executeQuery("from Person order by id", [offset:0, max:10])
def listPage2 = Person.executeQuery("from Person order by id", [offset:10, max:10])
def listPage3 = Person.executeQuery("from Person order by id", [offset:20, max:10])

The parameter max informs GORM to fetch a maximum of 10 records only. The offset means how many records to skip before reading the first result.

On page 1, we don't skip any records and get the first 10 results
On page 2, we skip the first 10 records and get the 11th to 20th records.
On page 3, we skip the first 20 records and get the 21st to 30th records.

GORM/Hibernate will translate the paging information to it's proper SQL syntax depending on the database.

Note: It is usually better to have an order by clause when paginating results, otherwise most database offers no guarantee on how records are sorted between each query.

List Parameters

HQL statements can have parameters. Here is an example:

def result = Person.executeQuery(
    "from Person where firstName = ? and lastName = ?", ['John', 'Doe'])

The parameters can be passed as a list. The first parameter (John) is used in the first question mark, the second parameter (Doe) is used in the second question mark, and so on.

Results can also be paginated

def result = Person.executeQuery(
    "from Person where firstName = ? and lastName = ?", ['John', 'Doe'], [offset:0, max:5])

Named Parameters

Providing list parameters is usually hard to read and prone to errors. It is easier to use named parameters. For example:

def result = Person.executeQuery(
    "from Person where firstName = :searchFirstName and lastName = :searchLastName", 
    [searchFirstName:'John', searchLastName:'Doe'])

The colon signifies a named parameter variable. Then the values can be passed as a map of values.

Results can also be paginated:

def result = Person.executeQuery(
    "from Person where firstName = :searchFirstName and lastName = :searchLastName",
    [searchFirstName:'John', searchLastName:'Doe'], [offset:0, max:5])

Here is a shorter version:

def result = Person.executeQuery(
    "from Person where firstName = :searchFirstName and lastName = :searchLastName",
    [searchFirstName:'John', searchLastName:'Doe'], [offset:0, max:5])

How to perform JOINs

Here is an example one to many relationship domain classes:

package asia.grails.test
class Purchase {
    static hasMany = [items:PurchaseItem]
    String customer
    Date dateOfPurchase
    double price
}

package asia.grails.test
class PurchaseItem {
    static belongsTo = Purchase
    Purchase parentPurchase
    String product
    double price
    int quantity
}

Here is a sample code that joins the two tables:

def customerWhoBoughtPencils = Purchase.executeQuery(
    "select p.customer from Purchase p join p.items i where i.product = 'Pencil' ")

This returns all customers who bought pencils

executeUpdate

We can update or delete records using executeUpdate. This is sometimes more efficient specially when dealing with large sets of records.

Delete

Here are some examples of how to delete records using executeUpdate.

Delete all person records in the database

Purchase.executeUpdate("delete Person")

Here are different ways to delete people with first name John

Person.executeUpdate("delete Person where firstName = 'John'")
Person.executeUpdate("delete Person where firstName = ? ", ['John'])
Person.executeUpdate("delete Person where firstName = :firstNameToDelete ", [firstNameToDelete:'John'])

Update

Here are some examples of how to delete records using executeUpdate.

Here are different ways on how to make all people have the age 15

Person.executeUpdate("update Person set age = 15")
Person.executeUpdate("update Person set age = ?", [15])
Person.executeUpdate("update Person set age = :newAge", [newAge:15])

Here are different ways to set John Doe's age to 15.

Person.executeUpdate(
    "update Person set age = 15 where firstName = 'John' and lastName = 'Doe'")
Person.executeUpdate(
    "update Person set age = ? where firstName = ? and lastName = ?", [15, 'John', 'Doe'])
Person.executeUpdate(
    "update Person set age = :newAge where firstName = :firstNameToSearch and lastName = :lastNameToSearch",
    [newAge:15, firstNameToSearch:'John', lastNameToSearch:'Doe'])

List of Tutorials for Beginners

Beginners Tutorial

Example Applications

If you like my posts, like my Facebook page please :)

Links