通过Hibernate中的计算值排序结果 [英] Ordering results by computed value in Hibernate

问题描述

我有一个表格播放器，列id，名称，胜，games_played。我将它映射到类Player。
我想在Hibernate中执行以下查询（最好使用Criteria，如果Criteria不可能的话，HQL也有帮助）

select * from Player order by（胜/
games_played）

我期望获得List< Player>按照他们的胜率进行排序。

感谢您的回答

Palo

<解决方案Hibernate不支持order by子句中的算术表达式。引用部分 14.12。 Hibernate文档中的group by子句：

group by子句和order by子句都不能包含算术表达式。

事实上，以下hql查询不会返回正确排序的结果：

 从玩家p选择p（p.wins / p.gamesPlayed）
  

我不认为你可以分割 org.hibernate.criterion.Property ，所以Criteria API不会解决这个问题。

因此，我建议使用计算属性（使用公式），例如注释：

 私人游戏获胜; 
私人浮动游戏玩法; 
 @Formula（value =WINS / GAMESPLAYED）
私人流通比率; 
  

这将允许使用Criteria API进行以下查询：

  session.createCriteria（Player.class）.addOrder（Order.desc（ratio））
  

I have a table Player with columns id, name, wins, games_played. I mapped it to a class Player. I want to do the following query in Hibernate (preferably with Criteria, if not possible with Criteria HQL will also help)

select * from Player order by (wins / games_played)

I expect to get List<Player> sorted by their win ratio.

Thanks for the answer

Palo

解决方案

Hibernate doesn't support arithmetics expressions in the order by clause. Quoting the section 14.12. The group by clause of the Hibernate documentation:

Neither the group by clause nor the order by clause can contain arithmetic expressions.

And indeed, the following hql query won't return properly ordered results:

select p from Player p order by (p.wins/p.gamesPlayed) 

And I don't think you can divide org.hibernate.criterion.Property so the Criteria API won't solve this.

So I'd suggest to use a calculated attribute (with a formula), for example with annotations:

private float wins;
private float gamesPlayed;
@Formula(value = "WINS/GAMESPLAYED")
private float ratio;

This would allow the following query with the Criteria API:

session.createCriteria(Player.class).addOrder(Order.desc("ratio"))

这篇关于通过Hibernate中的计算值排序结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！