In our current project we are using Spring + Hibernate Annotations.
Today I needed to use something like:

1
2
3
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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:

1
2
3
4
5
6
7
8
9
10
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:

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

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

  1. hi man
    i encountered an issue where i need to append cast function to the generated query and i worked around it for almost two days but of no use , finally your approach helped me .
    thanks man

Leave a Comment:

Your email address will not be published. Required fields are marked *