![Hibernate选择groupProperty]( "Hibernate选择groupProperty")
本文介绍了Hibernate选择groupProperty，rowCount与rowCount> N +的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
对不起，如果这是一个愚蠢的问题，但我坚持了整个下午的这个问题，但无法找到解决方案，因为我不熟练使用复杂的SQL：
我想找到来自msg发送次数>阈值的表的前n个发送消息的用户，这是我的标准：
Criteria c = session.createCriteria（Message.class）;
ProjectionList plist = Projections.projectionList（）;
plist.add（Projections.groupProperty（user））;
plist.add（Projections.rowCount（），count）;
c.setProjection（plist）;
c.addOrder（Order.desc（count））;
c.setFirstResult（0）;
c.setMaxResults（count）;
这是我可以编写的内容，但它缺少过滤rowCount低于一些门槛。如何使用标准实现它？非常感谢！
--------------更新---------------- --------
谢谢@TheStijn，我试过了。我现在可以使用子查询来实现我的目标，但生成的查询不是很聪明！查看生成的SQL：
select
this_.fromUser as y0_，
count（*）as y1_
from
Message this_
where
this_.fromUser不为空
和this_.created> ;?
和this_.created 和？ < =（
从
中选择
count（*）作为y0_
消息msg_
其中
msg_.fromUser = this_.fromUser
和msg_.fromUser不为空
和msg_.created> ;?
和msg_.created ）
分组由
this_.fromUser
命令按
y1_ desc限制？
也就是说，子查询重复了大部分主查询，其中I认为这有点多余。是否有任何标准构建这样的SQL查询：
select
this_.fromUser as y0_，
count （*）as y1_
from
Message this_
where
this_.fromUser is not null
and this_.created> ;?
和this_.created 和y1_> ？ // threshold
group by
this_.fromUser
order by
y1_ desc limit？
非常感谢！
似乎使用HQL更容易做到这一点，但我对Criteria方式感到好奇）
你需要一个额外的子查询如：
DetachedCriteria subQuery = DetachedCriteria.forClass（Message.class，msg）;
subQuery.add（Restrictions.eqProperty（msg.user，mainQuerymsg.user））;
subQueryEntriesCount.setProjection（Projections.rowCount（））;
c.add（Subqueries.lt（1L，subQuery））;
mainQuerymsg<你的主要标准，所以你需要创建这些标准的别名 createCriteria（MEssage.class，别名）
Sorry if it is a dumb question but I've stuck with this problem for a whole afternoon but cannot find a solution because I'm not skilled with complicated SQL :
I want to find "Top n message-sending users from a table with msg sent count > threshold" , this is my criteria :
Criteria c = session.createCriteria(Message.class);
ProjectionList plist = Projections.projectionList();
plist.add(Projections.groupProperty("user"));
plist.add(Projections.rowCount() , "count");
c.setProjection(plist);
c.addOrder(Order.desc("count"));
c.setFirstResult(0);
c.setMaxResults(count);
This is what I can write , but it lacks of "filtering rows with rowCount lower than some threshold". How to implement it with criteria ? Thanks a lot !
-------------- updated ------------------------
Thanks @TheStijn , I tried . I now can use subquery to achieve my goal , but the generated query is not so clever ! See the generated SQL :
select
this_.fromUser as y0_,
count(*) as y1_
from
Message this_
where
this_.fromUser is not null
and this_.created>?
and this_.created<?
and ? <= (
select
count(*) as y0_
from
Message msg_
where
msg_.fromUser=this_.fromUser
and msg_.fromUser is not null
and msg_.created>?
and msg_.created<?
)
group by
this_.fromUser
order by
y1_ desc limit ?
That is , the subquery repeats most of the main query , which I think it is a little redundant . Is there any criteria that builds such SQL queries :
select
this_.fromUser as y0_,
count(*) as y1_
from
Message this_
where
this_.fromUser is not null
and this_.created>?
and this_.created<?
and y1_ > ? // threshold
group by
this_.fromUser
order by
y1_ desc limit ?
Thanks a lot !
(It seems much easier to use HQL to do this , but I am curious about the Criteria way)
解决方案
You'll need an additional subquery like:
DetachedCriteria subQuery = DetachedCriteria.forClass(Message.class, "msg");
subQuery.add(Restrictions.eqProperty("msg.user", "mainQuerymsg.user"));
subQueryEntriesCount.setProjection(Projections.rowCount());
c.add(Subqueries.lt(1L, subQuery));
mainQuerymsg < your main criteria so you'ill need to create these criteria with an alias createCriteria(MEssage.class, "alias")
这篇关于Hibernate选择groupProperty，rowCount与rowCount> N +的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！