Grails Cookbook - A collection of tutorials and examples

Grails HQL Delete Examples

This post will show how to use delete records on a database table using HQL. Using Grails HQL delete is important on some cases because it is a very efficient solution. Bulk delete can be finished in one go using just one JDBC call.

Introduction

Bad Approach
Suppose that we need to perform a delete operation over a set of database rows. We can have a code like this:
def list = Person.executeQuery("from Person where lastName='Doe' ")
list.each { person ->
    person.delete()
}

This is not a very good solution if all we need to do is delete records with no other logic or processing. This is very inefficient specially when there are many records that needs to be deleted. The executeQuery will transfer all the matching data from the database and convert them to Person instances. Let's say 100,000 people have the last name Doe, 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.

All the Person objects (100,000) will be created and assigned to the list variable. These objects will consume considerable amount of memory on the heap and the creation of objects will take a very long time.

On each iteration using each, the invocation to the delete() method will cause a JDBC call each 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.

Good Approach

Now consider this code that uses HQL Delete

Person.executeUpdate("delete Person where lastName='Doe'")

This HQL code will be translated to SQL (delete from person) and will be sent to the database. This code will run so much 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 Delete

No Parameters

We can use HQL to delete all records in a table. Here is an example:

Person.executeUpdate("delete Person")
This will delete all records in the person table in one go. Here is the equivalent SQL translation of the HQL code above:
delete from person;

We can delete with hard-coded where clause values. For example:

Person.executeUpdate("delete Person where firstName = 'John' and lastName = '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("delete Person 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 Delete statements can have parameters. Here is an example:
Person.executeUpdate(
    "delete Person where 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 (firstName = ?) , the second parameter ('Doe') is used in the second question mark ( lastName = ?).

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(
    "delete Person where firstName = :firstNameToSearch and lastName = :lastNameToSearch",
    [firstNameToSearch:'John', lastNameToSearch:'Doe'])
The colon signifies a named parameter variable. Then the values can be passed as a map of values.