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

Related posts

  • Hibernate Annotations 3.2.1 – Lucene sort
    Keeping database and Lucene index in sync is easily done using the new Lucene-Hibernate integration. Just place the default org.hibernate.search.event.FullTextIndexEventListener on Hibernate's lifecycle...
  • 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 looking into a problem generated by migration from Hibernate-Lucene integration in 3.2.1.GA to the...
  • Hibernate annotations – default value
    If you wanted to set the default value using hibernate annotations, you've probably had some difficulties, as it was the case for me. Some posts on the web talk about default values to the members of the...
  • Hibernate schema maintenance
    Hibernate provides a nice way to keep your DB schema in sync with the model. At least 2 choices are available: - have the hibernate.hbm2ddl.auto set to auto during development - use various utilities:...
  • iBatis O/R mapping
    After some years of Hibernate or Spring+Hibernate it was time to try something new. Not for the sake of trying something new, but more out of the need. The problem Think of a legacy database, very...
.

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.