Grails on Groovy | From within a grails HQL, how would I use a MySQL / Oracle Native Function | HQL Query use MySQL Native Functions | Register Functions for MySQL Native Support

To call a MySQL native function in HQL query builder, the SQL dialect must be aware of it. You can add your function at runtime in BootStrap.groovy like this:

import org.codehaus.groovy.grails.commons.GrailsApplication import org.codehaus.groovy.grails.web.servlet.GrailsApplicationAttributes import org.hibernate.dialect.function.SQLFunctionTemplate import org.hibernate.type.StringType import org.springframework.context.ApplicationContext class BootStrap { def init = { servletContext -> ApplicationContext applicationContext = servletContext.getAttribute(GrailsApplicationAttributes.APPLICATION_CONTEXT) GrailsApplication application = (GrailsApplication) applicationContext.getBean("grailsApplication") def dialect = applicationContext.sessionFactory.dialect def MyFunction = new SQLFunctionTemplate(StringType.INSTANCE, "MyFunction(?1)") dialect.registerFunction('MyFunction', MyFunction) } def destroy = { } }

MySQL native function registered with our Grails project, now we are able to call the function in our HQL queries like below:

package com.pkm import grails.transaction.Transactional @Transactional class HomeService { void callMe() { List result = Table1.executeQuery("select id,name,roll,MyFunction(id) from Table1") println(result) } }

MySQL > Create Function > Call MySQL Function > Execution MySQL Function > MysQL Show Listed Functions