等同于SQL的Gorm [英] Gorm equivalent for SQL

问题描述

简单的一种-在Grails/Gorm中获得与此SQL查询相同的效果的最佳方法是什么:

Simple one - what is the best way in Grails/Gorm to get the same effect as this SQL query:

选择YEAR(日期)作为SalesYear， MONTH(日期)为SalesMonth， 总和(价格)AS TotalSales 从销售 按年份(日期)，月份(日期)分组 按YEAR(日期)，MONTH(日期)排序

SELECT YEAR(date) as SalesYear, MONTH(date) as SalesMonth, SUM(Price) AS TotalSales FROM Sales GROUP BY YEAR(date), MONTH(date) ORDER BY YEAR(date), MONTH(date)

推荐答案

executeQuery 方法允许您运行 HQL 查询.

The executeQuery method of a domain class allows you to run HQL queries.

如果查看一些示例，您会发现休眠查询语言记住了很多SQL，但是它是面向对象的.

If you look at some examples, you will notice that the Hibernate Query Language remembers a lot SQL, but is object-oriented.

来自Grails文档:

From the Grails docs:

executeQuery方法允许执行任意HQL查询. HQL查询可以返回域类实例或指定的数组 查询选择单个字段或计算值时的数据.

The executeQuery method allows the execution of arbitrary HQL queries. HQL queries can return domain class instances, or Arrays of specified data when the query selects individual fields or calculated values.

因此，在您的情况下，由于与域类不匹配，查询将返回指定数据的数组，但是您可以对这些数据进行迭代.

So in your case, the query will return an array of the specified data, since don't matches with a Domain Class, but you will be able to iterate over this data.

假设您将销售表映射为销售"域类:

Assuming that you mapped your sales table as Sales domain class:

class Sales {
  Date date
  BigDecimal price
  ...
  static mapping = {
    ...
  }
}


def result = Sales.executeQuery("SELECT YEAR(date) as SalesYear, MONTH(date) as SalesMonth, SUM(Price) AS TotalSales FROM Sales GROUP BY YEAR(date), MONTH(date) ORDER BY YEAR(date), MONTH(date)")

//iterate over the result
result.each { sales ->
  println sales[0] //year
  println sales[1] //month
  println sales[2] //total
}

这篇关于等同于SQL的Gorm的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！