HQL Use MySQL Functions By Implementing Custom Dialect

Datasource.groovy File

hibernate {
    cache.use_second_level_cache = true
    cache.use_query_cache = true
    cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory'
    cache.provider_class = 'net.sf.ehcache.hibernate.EhCacheProvider'
    format_sql = false
    use_sql_comments = false
}
grails.cache.config = {
    cache {
        name 'org.hibernate.cache.UpdateTimestampsCache'
        eternal true
        maxEntriesLocalHeap 500
        persistence {
            strategy localTempSwap
        }
    }
}

/* environment specific settings */
environments {
    development {
        dataSource {
            pooled = true
            url = "jdbc:mysql://localhost/db_name?useUnicode=yes&characterEncoding=UTF-8"
            driverClassName = "com.mysql.jdbc.Driver"
            username = "root"
            password = ""
            dialect = "com.pkm.custom.CustomMySQLDialect"
            dbCreate = "update"
            properties {
                maxActive = 1000
                maxIdle = 100
                minIdle = 50
                initialSize = 1
                minEvictableIdleTimeMillis = 60000
                timeBetweenEvictionRunsMillis = 60000
                numTestsPerEvictionRun = 3
                maxWait = 10000
                testOnBorrow = true
                testWhileIdle = true
                testOnReturn = true
                validationQuery = "SELECT 1"
                minEvictableIdleTimeMillis = 1800000
                timeBetweenEvictionRunsMillis = 1800000
            }
            logSql = false
            loggingSql = false
        }
    }
    test {
        dataSource {
            
        }
    }
    production {
        dataSource {
            
        }
    }
}
log4j = {
    debug 'org.hibernate.SQL'
    trace 'org.hibernate.type.descriptor.sql.BasicBinder'
}

Custom Dialect (.groovy) Used In Datasource

import org.hibernate.dialect.MySQL5InnoDBDialect
import org.hibernate.dialect.function.SQLFunctionTemplate
import org.hibernate.dialect.function.StandardSQLFunction
import org.hibernate.type.IntegerType
import org.hibernate.type.StringType
import org.hibernate.type.TimestampType

class CustomMySQLDialect extends MySQL5InnoDBDialect {
    public CustomMySQLDialect() {
        super()

        /* convert_tz('2015-01-01', '+00:00', '+10:00') = '2015-01-01 10:00' */
        /* convert_tz('2015-01-01 00:00', '+00:00', '+10:00') = '2015-01-01 10:00' */
        /* convert_tz('2015-01-01 20:00', '+00:00', '-10:00') = '2015-01-01 10:00' */
        registerFunction("convert_tz", new StandardSQLFunction("convert_tz"))

        /* group_concat(name) = 'a,b,c,c,d' */
        registerFunction("group_concat", new StandardSQLFunction("group_concat", new StringType()))

        /* group_concat_unique(name) = 'a,b,c,d' */
        registerFunction("group_concat_unique", new SQLFunctionTemplate(new StringType(), "group_concat(DISTINCT ?1)"))

        /* date_add_interval('2015-01-01', DAY, 10) = '2015-01-11' */
        /* date_add_interval('2015-01-20', DAY, -10) = '2015-01-10' */
        registerFunction("date_add_interval", new SQLFunctionTemplate(TimestampType.INSTANCE, "date_add(?1, INTERVAL ?3 ?2)"))

        /* index_of('am', 'i am good') = 3 */
        /* index_of('asm', 'i am good') = 0 */
        registerFunction("index_of", new SQLFunctionTemplate(new IntegerType(), "LOCATE(?1, ?2)"))

        /* format(number, decimal_place, locale) = format(200.343, 2, 'en_AU') = '200.34' */
        registerFunction("format", new StandardSQLFunction("format", new StringType()))
    }

    @Override
    public String transformSelectString(String select) {
        select = super.transformSelectString(select);
        return select;
    }
}