Grails sql 查询

Grails sql queries

提问by VictorArgentin

Imagine I have something like this:

想象一下我有这样的事情：

def example = {
   def temp = ConferenceUser.findAllByUser(User.get(session.user))
   [temp: temp]
}

Explaining my problem: Although dynamic finders are very easy to use and fast to learn, I must replace dynamic finders of my website for sql queries because it is a requirement. As I don't understand SQL that much, my main questions are:

解释我的问题：虽然动态查找器非常易于使用且易于学习，但我必须将我网站的动态查找器替换为 sql 查询，因为这是一项要求。由于我不太了解SQL，我的主要问题是：

a) I am using an SQLS database, with the drivers and datasource good configured and my website works as it is right now. If I want to replace the "findAllByUser" for an sql statement, should i do something like this:

a) 我正在使用 SQLS 数据库，驱动程序和数据源配置良好，我的网站现在可以正常工作。如果我想替换 sql 语句的“findAllByUser”，我应该这样做：

def dataSource
...
def db = new Sql(dataSource)
def temp = db.rows("SELECT ... ")

b) And that will work? I mean, the temp object will be a list as it is if I use "findAllByUser", and do I need to open a connection to the database =?

b) 那会起作用吗？我的意思是，临时对象将是一个列表，就像我使用“findAllByUser”一样，我是否需要打开与数据库的连接=？

采纳答案by hvgotcodes

yes, with grails you can do both plain sql and hql queries. HQL is 'hibernate query language' and allows you to write sql-like statements, but use your domain classes and properties instead of the table names and column names. To do an hql query, do something like

是的，使用 grails，您可以执行普通的 sql 和 hql 查询。HQL 是“休眠查询语言”，允许您编写类似 sql 的语句，但使用域类和属性而不是表名和列名。要进行 hql 查询，请执行以下操作

def UserList = ConferenceUser.executeQuery('from ConferenceUser cu where cu.user = ?', [user]),  

what you have here is a parameterized query -- executeQuery sees the ? in the hql string and substitutes the arguments in the array that is the second parameter to the method([user]in this case) for you.

你这里有一个参数化查询——executeQuery 看到 ? 在 hql 字符串中，并为您替换数组中的参数，该数组是该方法的第二个参数（[user]在本例中）。

See http://grails.org/doc/latest/ref/Domain%20Classes/executeQuery.html

请参阅 http://grails.org/doc/latest/ref/Domain%20Classes/executeQuery.html

and you can see this on how to do sql queries with Grails

你可以看到这个关于如何使用 Grails 进行 sql 查询

Sql query for insert in grails

用于在 grails 中插入的 Sql 查询

回答by James Allman

With Grails you can use Dynamic Finders, Criteria Builders, Hibernate Query Language (HQL), or Groovy SQL.

通过 Grails，您可以使用Dynamic Finders、Criteria Builders、Hibernate Query Language (HQL)或Groovy SQL。

To use Groovy SQL:

要使用Groovy SQL：

1. import groovy.sql.Sql
2. Request a reference to the datasource with def dataSourceor def sessionFactoryfor transactions
3. Create an Sqlobject using def sql = new Sql(dataSource)or def sql = new Sql(sessionFactory.currentSession.connection())
4. Use Groovy SQLas required

1. import groovy.sql.Sql
2. 请求对数据源的引用与事务def dataSource或def sessionFactory事务
3. Sql使用def sql = new Sql(dataSource)或创建对象def sql = new Sql(sessionFactory.currentSession.connection())
4. 根据需要使用Groovy SQL

Grails will manage the connection to the datasource automatically.

Grails 将自动管理与数据源的连接。

Sql.rowsreturns a list that can be passed to your view.

Sql.rows返回一个可以传递给您的视图的列表。

For example:

例如：

import groovy.sql.Sql

class MyController {
    def dataSource
    def example = {
        def sql = new Sql(dataSource)
        [ temp: sql.rows("SELECT . . .") ]
    }
}

And within a transaction:

在交易中：

import groovy.sql.Sql

class MyController {
    def sessionFactory
    def example = {
        def sql = new Sql(sessionFactory.currentSession.connection())
        [ temp: sql.rows("SELECT . . .") ]
    }
}

I recommend the book Grails Persistence with GORM and GSQLfor a lot of great tips and techniques.

我推荐Grails Persistence with GORM and GSQL这本书，里面有很多很棒的技巧和技巧。

回答by chim

Going Further / Tips

更进一步/提示

• Use Spring beans

• 使用 Spring bean

You can make the groovy.sql.Sqlinstance a Spring bean in your Grails application. In grails-app/conf/spring/resources.groovydefine the Sql bean:

您可以groovy.sql.Sql在 Grails 应用程序中将该实例设为 Spring bean。在grails-app/conf/spring/resources.groovy定义 Sql bean 中：

// File: grails-app/conf/spring/resources.groovy

beans = {

    // Create Spring bean for Groovy SQL.
    // groovySql is the name of the bean and can be used
    // for injection.
    sql(groovy.sql.Sql, ref('dataSource'))

}

Next inject the Sql instance in your your class.

接下来在你的类中注入 Sql 实例。

package com.example

import groovy.sql.GroovyRowResult

class CarService {

   // Reference to sql defined in resources.groovy.
   def sql

   List<GroovyRowResult> allCars(final String searchQuery) {
      final String searchString = "%${searchQuery.toUpperCase()}%"

      final String query = '''\
         select id, make, model
         from car
         where ...
         '''

        // Use groovySql bean to execute the query.
        final results = sql.rows(query, search: searchString)
        results
   }
}

• Multiple Datasources

  adminSql(groovy.sql.Sql, ref("dataSource_admin"))

  userSql(groovy.sql.Sql, ref("dataSource_user"))

• 多个数据源

  adminSql(groovy.sql.Sql, ref("dataSource_admin"))

  userSql(groovy.sql.Sql, ref("dataSource_user"))

and inject the beans

然后注入豆子

def userSql
def adminSql

Into the services that need them.

进入需要它们的服务。

or without injection

或不注射

import groovy.sql.Sql
// ...
// inject the datasource bean
def dataSource_admin

// ...
// in a method
Sql sql = new Sql(dataSource_admin)

Early Grails Version

早期的 Grails 版本

Looping through GORM result sets in early grails versions can cause needless queries in the middle of template loops. Using groovy SQL can help with this.

在早期 grails 版本中循环遍历 GORM 结果集可能会在模板循环中间导致不必要的查询。使用 groovy SQL 可以帮助解决这个问题。