Grails createCriteria 和executeQuery

发布于 2024-12-21 09:44:21 字数 840 浏览 2 评论 0原文

假设我有这个类被映射到 SQLServer：

class Statistic {
    Servicos servico
    int totalTime
    Date date

    static constraints = {
    }
}

class Servicos {

    String name
    String description

    static constraints = {
    }
}

使用这样的 SQL 查询：

select name, description, media,frequencia from TvMagazinePlus.dbo.servicos as t1 join (

    SELECT TOP 1000 
          [servico_id]
          ,avg([total_time]) as 'media'
          ,COUNT([servico_id]) as 'frequencia'
      FROM [TvMagazinePlus].[dbo].[statistic] where date between now and yesterday  group by [servico_id]) as t2 on t1.id = t2.servico_id

我得到这个表：

在此处输入图像描述

我现在需要以 JSON 形式呈现结果。我的问题是，我发现执行查询太难完成这个任务。有什么方法可以使用像 createCriteria 这样的 HQL 来做到这一点吗？

原文

let's say i have this classes which are being mapped to SQLServer:

class Statistic {
    Servicos servico
    int totalTime
    Date date

    static constraints = {
    }
}

class Servicos {

    String name
    String description

    static constraints = {
    }
}

Using SQL Queries like this:

select name, description, media,frequencia from TvMagazinePlus.dbo.servicos as t1 join (

    SELECT TOP 1000 
          [servico_id]
          ,avg([total_time]) as 'media'
          ,COUNT([servico_id]) as 'frequencia'
      FROM [TvMagazinePlus].[dbo].[statistic] where date between now and yesterday  group by [servico_id]) as t2 on t1.id = t2.servico_id

I get this table:

enter image description here

I now need to render the result in JSON. My problem is, i find executeQuery too difficult to accomplish this. Is there any way i can do this using HQL like createCriteria?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

聊慰 2024-12-28 09:44:21

您可以轻松地将结果设置为 bean 列表，然后使用 Jackson 等任何框架将它们序列化为 JSON...

我不知道 grails，但根据 hibernate 文档，使用 Hibernate 您可以使用条件来执行子查询：

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html

DetachedCriteria avgWeight = DetachedCriteria.forClass(Cat.class)
    .setProjection( Property.forName("weight").avg() );
session.createCriteria(Cat.class)
    .add( Property.forName("weight").gt(avgWeight) )
    .list();

这显然会返回体重大于普通猫的猫'体重，使用子查询来获取猫的平均体重。

要将结果映射到 XML/JSON 可序列化 bean，您只需根据该条件使用 AliasToBeanResultTransformer，然后序列化您的对象即可；)

You could easily set that result in a list of beans and then serialize them in JSON with any framework like Jackson or anything...

I don't know grails but with Hibernate you can use criteria to execute subqueries, according to the hibernate documentation:

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html

DetachedCriteria avgWeight = DetachedCriteria.forClass(Cat.class)
    .setProjection( Property.forName("weight").avg() );
session.createCriteria(Cat.class)
    .add( Property.forName("weight").gt(avgWeight) )
    .list();

This obviously return the cats that have a weight greater than the average cat' weight, using a subquery to get that average cat's weight.

To map the result into an XML/JSON serializable bean you can simply use a AliasToBeanResultTransformer on that criteria and then serialize your objects ;)

回复收藏 0 原文

~没有更多了~