Description
Grails is an open-source web application framework that leverages the Groovy language and complements Java Web development. Grails has the database access layer like the RubyOnRails's Active Record, it is very usefull and powerful, but sometimes we need to execute Native SQL Statement. We sometimes need to use the fulltext search statement(match/against statement) if we use mysql database as the backend server. We sometimes need to tune the sql performance and enter the QueryHint into the sql statenent.In case of ORACLE Database, we can use query hint like "select /*index hint */ .....", in case of using mysql, we can use "use/force index" statement.
But now, we can use these native sql statement on Grails framework, Grails System doesn't allow to use native statement.(We can use HQL Query Language by using executeQuery, findAll or other methods, but HQL doesn't allow to use native sql via these methods). I customized grails and added the "executeNativeQuery" method to run the native sql statement from the Grails framework.
Installation
(I tested this customization only on mysql database environment.)
- Download grails-1.0.1-dist-b4.tar file, this file is customized based on Grails-1.0.1 source code.
jar files: http://groups.google.com/group/taapps-sourcecode-libraries/web/grails-1.0.1-dist-b4.tar - Extract downloaded tar file
source files: http://taapps-sourcecode-libraries.googlegroups.com/web/grails-1.0.1-b4-src.tar - Copy 8 *.jar files existing under dist directory to the GRAILS_HOME/dist directory
- Restart application server, defailt server is jetty
While restarting the application server, System adds the executeNativeQuery method into the Domain Class object dynamically. The executeNativeQuery method has some parameters as follows and return the List object including some DomainClass objects fetched from the database.
Examples how to use this method are as follows.(the name "IndTest" means my Domain Test class name for the sample code)
- Type1, put the raw SQL statement into the grails hibernate layer.
Table alias name should be "tbl", the "tbl" alias will be mapped to the Domain Class.
def objList=IndTest.executeNativeQuery(
"select {tbl.*} from ind_test as {tbl} where user_id=10000 and match(description) against('aaaaaaa')")
objList.each{obj-> //executeNativeQuery will return the List object including the fetched domain objects
.....
} - Type2 executeNativeQuery has 3 arguments, SQL Statement String, Map object for mapping table alias, List object including the bind parameter values.
def objList=IndTest.executeNativeQuery(
"select {alias.*} from ind_test as {alias} where user_id=? and match(description) against(?)",
[alias:IndTest], --> table alias "alias" is mapped to the IndTest Domain Class.
[10000,"bbbbbb"]) --> 10000 is replaced with user_id's, bbbbbb is replaced with description's - Type3 executeNativeQuery has 3 arguments, SQL Statement String, Map object for mapping table alias, Map object including the bind parameter values.
def objList=IndTest.executeNativeQuery(
"select {alias.*} from ind_test as {alias} where user_id=:p1 and match(description) against(:p2)",
[alias:IndTest],
[p1:10000, p2:"cccccc"]) - Type4 executeNativeQuery has 4 arguments, SQL Statement String, Map object for mapping table alias, Map object including the bind parameter values, Map object for using pagination
def objList=IndTest.executeNativeQuery(
"select {alias.*} from ind_test as {alias} where user_id=:p1 and match(description) against(:p2)",
[alias:IndTest],
[p1:10000,p2:"dddddd"],
[max:10,offset:1]) - executeNativeQuery method has other parameter patterns as follows.
Query means the sql query statement string.
EntityMap means the Map object including the table alias mapping(alias name and Domain class name).
ParamList means the List object including the parameter values for the bind parameters.
NamedMap means the Map object including the parameter values for the bind parameters.
PageMap means the Map object for using pagination parameter.
Arg Number :0 1 2 3
Type 1 :String :Query
Type 2 :String Map :Query,EntityMap
Type 3 :String Map List :Query,EntityMap,ParamList
Type 4 :String Map Map :Query,EntityMap,NamedMap
Type 5 :String Map List Map :Query,EntityMap,ParamList,PageMap
Type 6 :String Map Map Map :Query,EntityMap,NamedMap,PageMap
