使用mysql“逐个排序”在休眠条件

问题描述

我使用Hibernate 4.3.1 final，Mysql 5.5，并且我想在一些连接的实体上使用按大小写顺序逻辑。

我希望实现的sql表示形式如下所示：

  select adv.id，adv.published_date 
 from广告为adv 
加入帐户作为act.id = adv.act_id 
加入account_status as aas on aas.id = act.current_aas_id 
按大小写顺序aas.status 
 when 'pending'THEN 1 
'approved'THEN 1 
 else 2 
 end，adv.published_date; 
  

这样可以在挂起账户和已批准账户之前发送广告。

我已经设法使用hibernate标准完成所有select查询，但我不确定如何使用该api通过case语句指定order。

我发现这篇文章：

但我需要引用连接的实体类我不知道该怎么做。

任何帮助或建议都非常感谢。

最后，我创建了我自己的Order类，并覆盖了公共String toSqlString （Criteria，CriteriaQuery）我thod：

  @Override 
 public String toSqlString（Criteria criteria，CriteriaQuery criteriaQuery）{
 final String [ ] columns = criteriaQuery.getColumnsUsingProjection（criteria，getPropertyName（））; 
 final StringBuilder fragment = new StringBuilder（）; 
 fragment.append（case）.append（columns [0]）; 
 fragment.append（when'pending'then 1）; 
 fragment.append（when'approved'then 1）; 
 fragment.append（else 2 end）; 
 return fragment.toString（）; 
} 
  

重要的调用（我在订单类的原始实现中找到的）是

  criteriaQuery.getColumnsUsingProjection（criteria，getPropertyName（））; 
  

这使我可以访问我想要使用case语句命令的列的别名。 / p>

如果其他人正在查看此内容，如果您要对不在根对象上的属性进行排序，请确保您在标准联接中使用别名，并且然后您在实例化时在自定义Order propertyName中正确引用这些别名。

I'm using Hibernate 4.3.1 final, Mysql 5.5 and I want to use an "order by case" order logic on some joined entities.

A pure sql representation of what I wish to achieve would look something like:

select adv.id, adv.published_date
from advert as adv
  join account as act on act.id = adv.act_id
  join account_status as aas on aas.id = act.current_aas_id
order by case aas.status
when 'pending' THEN 1
when 'approved' THEN 1
else 2
end, adv.published_date;

This orders the adverts of pending and approved accounts before those of deactive accounts.

I've managed to do all the select query using hibernate criteria, but I'm not sure how to specify the order by case statement using that api.

I found this post:

http://blog.tremend.ro/2008/06/10/how-to-order-by-a-custom-sql-formulaexpression-when-using-hibernate-criteria-api/

but I need to reference joined entity classes in the order by case and I'm not sure how to do that.

Any help or suggestions much appreciated.

解决方案

I think I found a solution.

In the end I created my own subclass of Order and overrode the public String toSqlString(Criteria,CriteriaQuery) method:

@Override
public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) {
    final String[] columns = criteriaQuery.getColumnsUsingProjection( criteria, getPropertyName() );
    final StringBuilder fragment = new StringBuilder();
    fragment.append(" case ").append(columns[0]);
    fragment.append(" when 'pending' then 1 ");
    fragment.append(" when 'approved' then 1 ");
    fragment.append(" else 2 end");
    return fragment.toString();
}

the important call (which I found in the original implementation of the order class) is the

criteriaQuery.getColumnsUsingProjection(criteria, getPropertyName());

Which gave me access to the alias for the column I wanted to order on using the case statement.

If anyone else is looking at this, if you are ordering on a property that is not on the root object, then make sure that you use aliases in your criteria joins and that you then reference those aliases correctly in the your custom Order propertyName when you instantiate it.

这篇关于使用mysql“逐个排序”在休眠条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！