Grails 中的 SQL/数据库视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/425294/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL/Database Views in Grails
提问by Chris King
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).
有谁知道通过 Grails 访问 sql 视图的最佳方法是什么（或者这是否可能）？这样做的一种显而易见的方法是对视图使用 executeQuery 从视图中选择行集合，我们不会将其视为域对象列表。但是，即使在这种情况下，也不清楚要针对哪个域类运行 executeQuery，因为实际上我们只是使用该域类来针对完全不相关的实体（视图）运行查询。
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.
创建一个代表视图的域类然后我们可以只对那个域类使用 list() 是不是更可取？看起来这会有问题，因为 Grails 可能希望能够插入、更新、删除和修改任何域类的表模式。
[Edit:
Follow up question here: Grails Domain Class without ID field or with partially NULL composite field
[编辑：
在此处跟进问题：没有 ID 字段或具有部分 NULL 复合字段的 Grails 域类
回答by Siegfried Puchbauer
You can use plain SQL in Grails which is in the case of accessing a view the preferable way (IMO):
您可以在 Grails 中使用纯 SQL，这是在以首选方式 (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
我希望来源是不言自明的。该文件可以在这里找到
回答by Chris King
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
但我不确定它是否有助于 Hibernate 理解它是一个视图...... 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.
无论如何，在我们当前的项目中，我们经常使用数据库视图作为 grails 域类，因为 HQL 很麻烦，而且使用 SQL 连接表更简单。
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.
不过，您需要注意的一件事是 Hibernate 批处理查询（以及整个刷新业务）。如果在表中插入某些内容，然后在同一个事务中选择依赖于该表的视图，则不会获得插入的最新行。这是因为 Hibernate 实际上还没有插入行，而如果您选择了插入行的表，Hibernate 会发现它需要在为您提供选择结果之前刷新其挂起的查询。
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.
一种解决方案是 ( flush:true) 在保存域实例时，您知道此后需要在同一事务中通读视图。
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.
然而，用某种方式告诉 Hibernate 视图/域依赖于哪些其他域类会很酷，这样 Hibernate 刷新就会无缝地工作。
回答by Ben Williams
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.
将域类映射到视图是完全可能的，只需将其视为常规表即可。我认为 Grails 会打印一些关于无法执行插入、删除等操作的日志消息，但它不会抛出任何错误，除非您实际上尝试执行除域类查询以外的其他操作。
