How to order by a custom SQL formula/expression when using hibernate Criteria API

2 min read >

How to order by a custom SQL formula/expression when using hibernate Criteria API

Engineering Insights & Enterprise solutions

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 conditions
criteria.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/expression
criteria.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 sqlFormula 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 order, I included the following line:

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