SQL/Database Views in Grails

Question

Does anybody know what is the best approach to accessing a sql view through Grails (or if this is even possible)? It seems an obvious way of doing this would be to use executeQuery against the view to select a collection of rows from the view which we would not treat as a list of domain objects. However, even in this case it is not obvious which domain class to run executeQuery against, since really we are just using that domain class in order to run the query against a completely unrelated entity (the view).

Would it be preferred to create a domain class representing the view and we could then just use list() against that domain class? It seems like there would be problems with this as Grails probably expects to be able to insert, update, delete, and modify the table schema of any domain class.

[Edit:
Follow up question here: Grails Domain Class without ID field or with partially NULL composite field

Answer 1

You can use plain SQL in Grails which is in the case of accessing a view the preferable way (IMO):

For example in your controller:

import groovy.sql.Sql

class MyFancySqlController {

    def dataSource // the Spring-Bean "dataSource" is auto-injected

    def list = {
        def db = new Sql(dataSource) // Create a new instance of groovy.sql.Sql with the DB of the Grails app

        def result = db.rows("SELECT foo, bar FROM my_view") // Perform the query

        [ result: result ] // return the results as model
    }

}

and the view part:

<g:each in="${result}">
    <tr>
        <td>${it.foo}</td>
        <td>${it.bar}</td>
    </tr>
</g:each>

I hope the source is self-explanatory. The Documentation can be found here