How do you bulk delete records in Grails/GORM?

hibernate grails gorm
Simon picture Simon · Feb 9, 2010 · Viewed 30.2k times · Source

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

Answer

JesperSM picture JesperSM · Apr 23, 2012

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.

Related questions

Found shared references to a collection org.hibernate.HibernateException

I got this error message: error: Found shared references to a collection: Person.relatedPersons When I tried to execute addToRelatedPersons(anotherPerson): person.addToRelatedPersons(anotherPerson); anotherPerson.addToRelatedPersons(person); anotherPerson.save(); person.save(); My domain: Person { static hasMany = [relatedPersons:Person]; } any idea …

Read More
StaleObjectStateException: Row was updated or deleted by another transaction?

I do the following: def currentUser = springSecurityService.currentUser currentUser.name = "test" currentUser.save(flush: true) // some other code currentUser.gender = "male" currentUser.save(flush: true) // Exception occurs This is the exception I get: ERROR events.PatchedDefaultFlushEventListener - Could not synchronize …

Read More
How to fix StaleObjectStateException with JPA and Hibernate

Controller Logic: def updateObject() { Object o = Object.get(params.id as Long) o.otherObjects.clear() objectDataService.saveObject(o.id) OtherObject newObject = new OtherObject; o.addToOtherObjects(newObject) objectDataService.saveObject(o.id) } ServiceLogic def saveObject(long profileId) { o.save(flush:true) } what …

Read More