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 |
Showing posts with label native-function. Show all posts
Showing posts with label native-function. Show all posts
Saturday, June 16, 2018
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
Subscribe to:
Posts (Atom)