Is it possible to map the result of a native SQL query to a collection of Grails domain class instances?
import com.acme.domain.*
def sessionFactory
sessionFactory = ctx.sessionFactory // this only necessary if your are working with the Grails console/shell
def session = sessionFactory.currentSession
def query = session.createSQLQuery("select f.* from Foo where f.id = :filter)) order by f.name");
query.addEntity(com.acme.domain.Foo.class); // this defines the result type of the query
query.setInteger("filter", 88);
query.list()*.name;
Alternatively using Groovy SQL in the Grails app
import groovy.sql.Sql
class TestQService{
def dataSource //Auto Injected
def getBanksForId(int bankid){
def sql = Sql.newInstance(dataSource)
def rows = sql.rows(""" Select BnkCode , BnkName from Bank where BnkId = ?""" , [bankid])
rows.collect{
new Bank(it)
}
}
class Bank{
String BnkCode
String BnkName
}
}
You could map it yourself without too much trouble. Alternatively if using HQL, you could use select new map()
and then take query.list().collect { new MyDomainObject(it) }
to bind the parameters by hand.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With