Display a result set in Grails / GORM

Question

I want to match the result of my own SQL query with a simple bean in grails, similar to what the @SqlResultSetMapping annotation does.

For example, given the request

select x.foo, y.bar, z.baz from //etc...

display result in

 class FooBarBaz { String foo String bar String baz } 

Can someone provide an example of how to do this in the grail? Thanks in advance.

Answer 1

I successfully tested this on the Grails console

 import groovy.sql.Sql class FooBarBaz { String foo String bar String baz } // Initialising the Sql object like this ensures that the SQL statement // will participate in the current transaction (if one exists) // 'ctx' refers to the Spring ApplicationContext when using the Grails console def sessionFactory = ctx.getBean('sessionFactory') Sql sql = new Sql(sessionFactory.currentSession.connection()) def query = 'select email, user_real_name, phone from user' def results = [] sql.eachRow query, {row -> results << new FooBarBaz(foo: row.email, bar: row.user_real_name, baz: row.phone)}