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: http://stackoverflow.com/questions/430004/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

Answer 2

It's perfectly possible to map a domain class to a view, just treat it like a regular table. I think Grails will print some log messages about not being able to do inserts, deletes, etc. but it will not throw any errors unless you actually try to do something other than query with the domain class.

Answer 3

You can put this in your domain class mappings:

static mapping = { cache 'read-only' }

But I'm not sure if it helps Hibernate understand it's a view... http://docs.jboss.org/hibernate/stable/core/reference/en/html_single/#performance-cache-readonly

Anyway, we use database views a lot as grails domain classes in our current project, because HQL is a pain in the ass and it's simpler to use SQL to join tables.

One thing you need to be careful about though, is the Hibernate batching of queries (and the whole flush business). If you insert something in a table, and then in the same transaction you select a view that depends on that table, you will not get the latest rows you inserted. This is because Hibernate will not actually have inserted the rows yet, whereas if you selected the table you inserted rows in, Hibernate would have figured out it needed to flush its pending queries before giving you the result of your select.

One solution is to (flush:true) when saving a domain instance that you know you will need to read through a view thereafter in the same transaction.

It would be cool however to have some kind of way to tell Hibernate that a view/domain depends on which other domain classes, so that the Hibernate flushing works well seemlessly.

Answer 4

Siegfried - thanks, nice clear example that helped me.