Result set mapping in Grails / GORM

Question

I want to map the result of a native SQL query to a simple bean in grails, similar to what the @SqlResultSetMapping annotation does.

For example, given a query

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

map the result to

class FooBarBaz {
  String foo
  String bar
  String baz
}

Can anyone provide an example of how to do this in grails? Thanks in advance.

Duplicate of http://stackoverflow.com/questions/2088641/mapping-result-of-a-native-sql-query-to-grails-domain-class — ataylor, Mar 12 '10 at 18:16
Hi Taylor, the post you're linking to involves mapping to a domain class - a different case entirely. — armandino, Mar 12 '10 at 19:57

Dónal · Answer 1 · 2010-03-12T18:18:56.240

I tested this successfully in 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)}

Result set mapping in Grails / GORM

1 Answers1