when to use executeUpdate in grails

Question

I'm looking at some grails gorm code in my current project and I'm wondering what are some pros and cons of the following HQL approach:

UploadUpsell.executeUpdate("update UploadUpsell set processedStatus=:newStatus, processedDate=:processedDate where processedDate is null and period=:period",
                            [newStatus: EXPIRED_UPSELL_STATUS, processedDate: new Date(), period: flow.period])

I would have gone for a more idiomatic approach along the lines

UploadUpsell.findAllByPeriodAndProcessedDate(flow.period, null).each { UploadUpsell uploadUpsell ->
                    uploadUpsell.with {
                        processedStatus = EXPIRED_UPSELL_STATUS
                        processedDate = new Date()
                        save()
                    }
                }

I believe that my approach would be more testable and is more readable but I'm wondering if my thinking is just dogmatic

HQL statements are not supported by grails mocking framework as far as I know.

Burt Beckwith Burt Beckwith · Accepted Answer · 2013-03-20T15:29:37

Your approach is more readable, but far less performant. You read all of the instances into memory, including data that you won't be changing, make the changes, and push everything back to the database, including unchanged values. If your domain classes and their values are small, this isn't a big deal (especially if the number of records isn't too large) but in general this approach is a scalability issue.

By running the query directly using executeUpdate, you let the database do what it's good at - it can find the records that need to be updated and do the work with out all that unnecessary traffic between the database and the web server.

You're right that HQL isn't supported in unit tests, but you shouldn't unit test domain classes. If you don't test persistence against a database, all you're testing is the mocking framework.

when to use executeUpdate in grails

1 Answers