Grails HQL Update Examples

Feb 27, 2015 Snippet comments

This post will show examples on how to use Grails HQL Update operations on a database. Using Grails HQL for bulk update operations is very efficient because a task can be completed in one go.

Introduction

A Bad Solution

Suppose that we need to perform an operation over a set of database rows. For example, we need to update the age of all people that have their birthday today. We can have a code like this:

def list = Person.executeQuery(
    "from Person where date(dateOfBirth) = date(:today)", [today:new Date()])
list.each { person ->
    person.age = person.age + 1
    person.save()
}

Unfortunately, this solution is very inefficient specially when there are many records that needs to be updated. The executeQuery will transfer all the matching data from the database and convert them to Person instances. Let's say 100,000 people have their birthday today, then all those details will be loaded from the db going to your application and this will consume network overhead. Even when your app and database resides on the same machine, the inter-program transfer will still consume considerable time.

Then 100,000 Person objects will be created and collectively assigned to the list variable. These instances will consume considerable amount of memory on the heap and the creation of objects will take a very long time due to CPU processing.

After that, all the invocation to the save() method will cause a JDBC call to the database. This will be 100,000 calls to the database consuming a lot of time to process. Your application and the database needs to talk 100,000 times through JDBC calls. Even when the underlying calls uses Prepared Statements, this will still be very inefficient.

A Good Alternative

Now consider this code that uses HQL Update

Person.executeUpdate(
    "update Person set age = age + 1 where date(dateOfBirth) = date(:today) ", 
    [today:new Date()])

The HQL will be translated to it's equivalent SQL code depending on your specific dialect. This code will run magnitudes faster than the first example above. This is because you are only having 1 JDBC call and you fulfill the same task. No details of each matching record is transferred between the database and your app.

This solution will use less network, memory and CPU processing because just one simple message will be passed and all processing will be done inside the database.

Examples

Here are some examples on how to perform Grails HQL Update

No Parameters

There are cases where you can hard code your HQL with no need of parameters. Here is an example:

Person.executeUpdate(
    "update Person set age = 15 where firstName = 'John' and lastName = 'Doe'")

This statement will find all persons with first name John and last name Doe and set their age to 15. In SQL, this is roughly translated to:

update person set age = 15 where first_name = 'John' and last_name = 'Doe';

Here is an example when the first name and last name of the person is not fixed but are contained in variables:

def firstName = "John";
def lastName = "Doe";
Person.executeUpdate(
    "update Person set age = 15 where firstName = '${firstName }' and lastName = '${lastName }'")

This is not a very good solution specially for web based applications. This could make your application prone to SQL injection.

List Parameters

HQL Update statements can have parameters. Here is an example:

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

The parameters can be passed as a list. The first parameter (15) is used in the first question mark (age = ?) , the second parameter ('John') is used in the second question mark ( firstName = ?), and so on.

Named Parameters

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

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

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