Ordering results by computed value in Hibernate

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 or开发者_开发问答der 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"))

From the Hibernate docs -

SQL functions and aggregate functions are allowed in the having and order by clauses if they are supported by the underlying database.

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-ordering

Which means you need to do it based on your underlying database.

Late to the party, but this is possible with a current version of Hibernate (5.4 in my case):

var cb = entityManager.getCriteriaBuilder();
var query = cb.createQuery(Player.class);
var root = query.from(Player.class);

query.orderBy(cb.asc(cb.quot(root.get("wins"), root.get("gamesPlayed"))));

return entityManager.createQuery(query).getResultList();