HOW TO ORDER BY A CUSTOM SQL FORMULA/EXPRESSION WHEN USING HIBERNATE CRITERIA API

In our current project we are using Spring + Hibernate Annotations.

Today I needed to use something like:

SELECT DISTINCT t.id FROM MyClass t WHERE ..... ORDER BY (a + b) DESC

where a and b are properties of MyClass (columns in the “my_class” table).

The “where …” expression must be generated dynamically based on the user input, so we are using Criteria API to generate the query as such:

Criteria criteria = getSession().createCriteria(MyClass.class);criteria.setProjection(Projections.distinct(Projections.id())); // Some custom dynamic conditionscriteria.add(Restrictions.gt("createdDate", afterDate));criteria.add(Restrictions.in("state", approvedStates));criteria.add(Restrictions.isNull("deletedDate"));if (includedCategories != null || excludedCategories != null) {    Criteria categoryCriteria = criteria.createCriteria("category");    if (includedCategories != null) {        for (String categoryPrefix : includedCategories) {            categoryCriteria.add(Restrictions.like("path", categoryPrefix + "%"));        }    }    if (excludedCategories != null) {        for (String categoryPrefix : excludedCategories) {            categoryCriteria.add(Restrictions.not(Restrictions.like("path", categoryPrefix + "%")));        }    }} criteria.add(Restrictions.sqlRestriction("(a + b) > 1")); // Custom ordering by some SQL formula/expressioncriteria.addOrder(Order.desc("a + b")); return criteria.list();

Now, the problem is that the class Order does not support custom SQL formula/expression…

So my solution was to derive my own class as such:

package ro.tremend.util.hibernate; import org.hibernate.criterion.Order;import org.hibernate.criterion.CriteriaQuery;import org.hibernate.Criteria;import org.hibernate.HibernateException; /** * Extends {@link org.hibernate.criterion.Order} to allow ordering by an SQL formula passed by the user. * Is simply appends the <code>sqlFormula</code> passed by the user to the resulting SQL query, without any verification. * @author Sorin Postelnicu * @since Jun 10, 2008 */public class OrderBySqlFormula extends Order {    private String sqlFormula;     /**     * Constructor for Order.     * @param sqlFormula an SQL formula that will be appended to the resulting SQL query     */    protected OrderBySqlFormula(String sqlFormula) {        super(sqlFormula, true);        this.sqlFormula = sqlFormula;    }     public String toString() {        return sqlFormula;    }     public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {        return sqlFormula;    }     /**     * Custom order     *     * @param sqlFormula an SQL formula that will be appended to the resulting SQL query     * @return Order     */    public static Order sqlFormula(String sqlFormula) {        return new OrderBySqlFormula(sqlFormula);    }}

Now, to use the custom ordering, I included the following line:

 criteria.addOrder(OrderBySqlFormula.sqlFormula("(a + b) desc"));