Hibernate Criteria按子记录数排序 [英] Hibernate Criteria order by count of child records

问题描述

我有两类：新闻和评论，它们之间有一对多的关联。
我使用Hibernate Criteria从数据库中获取新闻。

  session.createCriteria（News.class，n ）; 
 criteria.createAlias（n.comments，评论）; 
 criteria.setProjection（Projections.projectionList（）
 .add（Projections.groupProperty（comments.id））
 .add（Projections.count（comments.id）。as （ numberOfComments）））; 
 criteria.addOrder（Order.desc（numberOfComments））; 
列表<新闻> news = criteria.list（）; 
  

使用下面的代码，我得到的不是新闻列表，而是包含两个Long的对象列表在他们每个人。
我应该怎么做才能得到排序后的新闻对象列表？

解决方案

我找到了答案问题在这里：
Hibernate Criteria API - 如何订购通过集合大小？

我添加了新的hibernate Order实现：

  public class SizeOrder extends Order {
 
 protected String propertyName; 
 protected boolean ascending; 
 
 protected SizeOrder（String propertyName，boolean ascending）{
 super（propertyName，ascending）; 
 this.propertyName = propertyName; 
 this.ascending =升序; 
} 
 $ b $ public String toSqlString（Criteria criteria，CriteriaQuery criteriaQuery）throws HibernateException {
 String role = criteriaQuery.getEntityName（criteria，propertyName）+'。'+ criteriaQuery.getPropertyName（ propertyName的）; 
 QueryableCollection cp =（QueryableCollection）criteriaQuery.getFactory（）。getCollectionPersister（role）; 
 
 String [] fk = cp.getKeyColumnNames（）; 
 String [] pk =（（Loadable）cp.getOwnerEntityPersister（））
 .getIdentifierColumnNames（）; 
返回（select count（*）from+ cp.getTableName（）+其中
 + new ConditionFragment（）
 .setTableAlias（
 criteriaQuery.getSQLAlias（criteria， propertyName）
）.setCondition（pk，fk）
 .toFragmentString（）+）
 +（升序？asc：desc）; 
 
 
 public static SizeOrder asc（String propertyName）{
 return new SizeOrder（propertyName，true）; 
 
 public static SizeOrder desc（String propertyName）{
 return new SizeOrder（propertyName，false）; 
} 
} 
  

然后按照我的标准

criteria.addOrder（SizeOrder.desc（n.comments））;

现在一切正常，
非常感谢大家：）

I have two classes: News and Comments with one-to-many association between them. I am using Hibernate Criteria to fetch news from database. I would like my news to be ordered by the count of its comments.

session.createCriteria(News.class, "n");
criteria.createAlias("n.comments", "comments");
criteria.setProjection(Projections.projectionList()
    .add(Projections.groupProperty("comments.id"))
    .add(Projections.count("comments.id").as("numberOfComments")));
criteria.addOrder(Order.desc("numberOfComments"));
List<News> news = criteria.list();

With the following code I'm getting not the list of news but the list of objects with two Long's in each of them. What should I do to get the list of sorted news objects?

解决方案

I've found the answer to my question here: Hibernate Criteria API - how to order by collection size?

I've added the new hibernate Order implementation:

public class SizeOrder extends Order {

protected String propertyName;
protected boolean ascending;

protected SizeOrder(String propertyName, boolean ascending) {
    super(propertyName, ascending);
    this.propertyName = propertyName;
    this.ascending = ascending;
}

public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
    String role = criteriaQuery.getEntityName(criteria, propertyName) + '.' + criteriaQuery.getPropertyName(propertyName);
    QueryableCollection cp = (QueryableCollection) criteriaQuery.getFactory().getCollectionPersister(role);

    String[] fk = cp.getKeyColumnNames();
    String[] pk = ((Loadable) cp.getOwnerEntityPersister())
            .getIdentifierColumnNames();
    return " (select count(*) from " + cp.getTableName() + " where "
            + new ConditionFragment()
                    .setTableAlias(
                            criteriaQuery.getSQLAlias(criteria, propertyName)
                    ).setCondition(pk, fk)
                .toFragmentString() + ") "
            + (ascending ? "asc" : "desc");
}

public static SizeOrder asc(String propertyName) {
    return new SizeOrder(propertyName, true);
}
public static SizeOrder desc(String propertyName) {
    return new SizeOrder(propertyName, false);
}
}

And then applied that to my criteria as

criteria.addOrder(SizeOrder.desc("n.comments"));

Now everything works fine, thanks everyone a lot :)

这篇关于Hibernate Criteria按子记录数排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！