How to use mysql time functions inside grails executeupdate

Question

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

Answer 1

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])

Answer 2

You can try with the below query, just need to validate whether the HQL functions are supported in MySQL dialect:

Bugrerun.executeUpdate("delete Bugrerun b \ 
                        where b.complete = 1 \
                        and month(current_date()) > month(b.date) \
                        or year(current_date()) > year(b.date)")

Answer 3

Not all mysql functions are available. You can take a look at MySQLDialect that is used by hibernate (and grails) to see the functions you have available for you: http://grepcode.com/file/repository.springsource.com/org.hibernate/com.springsource.org.hibernate/3.3.1/org/hibernate/dialect/MySQLDialect.java#MySQLDialect

If you want, you can try to use Groovy SQL to execute a SQL statement instead of an HQL statement. If you do that, in your controller or service you should declare a dataSource attribute so you get DataSource injected:

class MyController {
    DataSource dataSource

    def execSql(){
            def sql = new Sql(dataSource)
            sql.execute("delete from bugrerun where complete = 1 and date < date_sub(curdate(), INTERVAL 1 MONTH) ")
            render "done"
    }
}