def list = Person.executeQuery( "from Person where date(dateOfBirth) = date(:today)", [today:new Date()]) list.each { person -> person.age = person.age + 1 person.save() }
Unfortunately, this solution is very inefficient specially when there are many records that needs to be updated. The executeQuery will transfer all the matching data from the database and convert them to Person instances. Let's say 100,000 people have their birthday today, 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.
Then 100,000 Person objects will be created and collectively assigned to the list variable. These instances will consume considerable amount of memory on the heap and the creation of objects will take a very long time due to CPU processing.
After that, all the invocation to the save() method will cause a JDBC call 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.
Now consider this code that uses HQL Update
Person.executeUpdate( "update Person set age = age + 1 where date(dateOfBirth) = date(:today) ", [today:new Date()])
The HQL will be translated to it's equivalent SQL code depending on your specific dialect. This code will run magnitudes 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.
There are cases where you can hard code your HQL with no need of parameters. Here is an example:
Person.executeUpdate( "update Person set age = 15 where firstName = 'John' and lastName = 'Doe'")This statement will find all persons with first name John and last name Doe and set their age to 15. In SQL, this is roughly translated to:
update person set age = 15 where first_name = 'John' and last_name = '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( "update Person set age = 15 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.
Person.executeUpdate( "update Person set age = ? where firstName = ? and lastName = ?", [15, 'John', 'Doe'])The parameters can be passed as a list. The first parameter (15) is used in the first question mark (age = ?) , the second parameter ('John') is used in the second question mark ( firstName = ?), and so on.
Person.executeUpdate( "update Person set age = :newAge where firstName = :firstNameToSearch and lastName = :lastNameToSearch", [newAge:15, firstNameToSearch:'John', lastNameToSearch:'Doe'])The colon signifies a named parameter variable. Then the values can be passed as a map of values.