Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do you bulk delete records in Grails/GORM?

Tags:

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

like image 427
Simon Avatar asked Feb 09 '10 21:02

Simon


2 Answers

With Grails 2.0 you can use a detached query like this:

Agency.where { }.deleteAll() 

Note that you don't get the listeners and whatnot executed, but it does execute through to the database, AND it is compatible with the mocked domain stuff, as in:

void testWhatever() {     mockDomain(Agency, [])     saveABunchOfAgencies() // saves 10 of 'em     assert Agency.count() == 10      Agency.where { }.deleteAll()      assert Agency.count() == 0   // Joy! } 

That being said the GORM unit test mocks have a bunch of gotchas but are in general pretty neat.

like image 84
JesperSM Avatar answered Oct 20 '22 04:10

JesperSM


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"]) 
like image 24
Colin Harrington Avatar answered Oct 20 '22 04:10

Colin Harrington