在 Hibernate 中按计算值对结果进行排序 [英] Ordering results by computed value in Hibernate

问题描述

我有一个表 Player，其中包含 id、name、wins、games_played 列.我将它映射到一个类 Player.我想在 Hibernate 中执行以下查询(最好使用 Criteria，如果不可能，使用 Criteria HQL 也有帮助)

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)

select * from Player order by (wins / games_played)

我希望得到 List按获胜率排序.

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

感谢您的回答

帕洛

推荐答案

Hibernate 不支持 order by 子句中的算术表达式.引用部分 14.12.Hibernate 文档的 group by 子句:

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

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

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

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

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

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

而且我认为你不能划分 org.hibernate.criterion.Property 所以 Criteria API 不会解决这个问题.

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;

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

This would allow the following query with the Criteria API:

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

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