How do you bulk delete records in Grails/GORM?

Simon_24039 0
Viewed 34046

I have a table which has records that need to be periodically cleared according to a set of criteria.

I was expecting that I could use the criteria builder to just delete the records, but that fails because there is no delete method on criteria...

def c = Agency.createCriteria()
c.delete
{
    eq("agency", "XXX")  
}

So I thought maybe I first query for the set and then delete that...

def c = Agency.createCriteria()
def deletions = c
{
    eq("agency", "XXX")  
}
deletions.delete

This also fails for the same reason, different object.

So what is the right way to do this? It seems excessive (perverse) that I would have to iterate through the entire result set calling delete() on each item.

I know I can form a query to execute directly either in HQL or SQL but that feels wrong too. Is the criteria builder only meant for retrieval?

Thanks

grails-orm grails hibernate
3 Answers

From the User Guide about deleting objects:

Note that Grails does not supply a deleteAll method as deleting data is discouraged and can often be avoided through boolean flags/logic.

If you really need to batch delete data you can use the executeUpdate method to do batch DML statements:

Customer.executeUpdate("delete Customer c where c.name = :oldName", [oldName:"Fred"])
user_1054920

If you want to avoid HQL I'd suggest using GORM list(), delete() and Groovy's spread operator:

def agencyList = Agency.createCriteria().list {
    eq("agency", "XXX")  
}
agencyList*.delete()
user_2670310
Related