SQL/Database Views in Grails

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.Sqlclass 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

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.

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.