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"));