Search code examples
mysqldatetimegrailsdomain-object

How to use mysql time functions inside grails executeupdate

I am trying to delete 1 month old records from my table using domain.executeUpdate as follows

Bugrerun.executeUpdate("delete Bugrerun b where b.complete = 1 and b.date 
< date_sub(curdate(), INTERVAL 1 MONTH) ")

i am trying to use a MySQL date function inside the query.

But this fails with the error

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: 1 near line 1
, column 97

How can we use the My SQL date time functions inside executeUpdate statements

Note that this table has lot of data so fetch and delete individual records will not work

Solution

  • You could implement your own Database dialect to include that functionality.

    Another option is to do this:

    Calendar cal = Calendar.getInstance();
cal.setTime(new Date());
cal.add(Calendar.MONTH, -1);

Bugrerun.executeUpdate("delete Bugrerun b where b.complete = 1 and b.date 
< :oneMonthAgo", [oneMonthAgo: cal.time])