SQL/Database Views in Grails

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

Edit Report

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.

answered 2009-07-21T23:12:56.713

SQL/Database Views in Grails

1 Answer

Your Answer