Grails中的SQL /数据库视图 [英] SQL/Database Views in Grails

问题描述

有人知道什么是通过Grails访问sql视图的最好方法（或者如果这是可能的）？看起来一个显而易见的方法是对视图使用executeQuery来从视图​​中选择一个行集合，我们不会将其视为一个域对象列表。然而，即使在这种情况下，不明显哪个域类运行executeQuery反对，因为我们真正使用该域类为了对一个完全不相关的实体（视图）运行查询。

最好创建一个表示视图的域类，然后我们可以对该类使用list（）。这似乎会有问题，因为Grails可能希望能够插入，更新，删除和修改任何域类的表模式。

> Grails无ID字段类或部分NULL复合字段

解决方案

在Grails中使用纯SQL，这是访问视图的首选方法（IMO）：

例如在您的控制器中：

import groovy.sql.Sql

类MyFancySqlController {

def dataSource // Spring-Bean dataSource是自动注入的

def list = {
def db = new Sql（dataSource）//使用Grails应用程序的DB创建一个groovy.sql.Sql的新实例

def result = db.rows（SELECT foo，bar FROM my_view）//执行查询

[result：result] //将结果返回为模型
}

}


和视图部分：

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

我希望源代码是不言自明的。 文档可在此处找到

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

解决方案

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

这篇关于Grails中的SQL /数据库视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！