Looking for software experts?
Need an expert advice on software development? Need consulting work done in time and at high standards? Tremend has the right solution for you.

We can provide expertise in:
  •    » high traffic and complex content website infrastructures using Java, PHP or .NET. More here ...
  •    » mobile applications for iPhone, Android or J2ME. More here ...

For an enquiry, send an email to contact [at] tremend [dot] ro.

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

June 10th, 2008 by spostelnicu in Java, General

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
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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"));
DZoneGoogle ReaderYahoo MessengerRedditEmailDelicious

You might also like

Hibernate Annotations 3.2.1 – Lucene sort Keeping database and Lucene index in sync is easily done using the new Lucene-Hibernate integration. Just...
Migrate Lucene annotations 3.2.1GA to Hibernate Search 3.0.0.Beta1: object removal Changes in Hibernate Search are quite frequent, as it is in continued development. Today I found myself...
Hibernate annotations – default value If you wanted to set the default value using hibernate annotations, you've probably had some difficulties,...
Hibernate schema maintenance Hibernate provides a nice way to keep your DB schema in sync with the model. At least 2 choices are available: -...
.

2 Responses

  1. Surender Says:

    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

  2. Gaurav Says:

    This works like a charm!! Thanks a lot!

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.