Hibernate and order by using column values

Is there a way I can pull off the 开发者_如何学编程below query (order by followed by a like) using hibernate criteria api?

select * from table where first_name like 'bar%' order by first_name like 'bar%' desc

You can't easily map this query to Criteria API because Order class only supports property names / aliases and not expressions.

You can, however, try one of the following:

1. If the underlying database supports the expression you're trying to order by in SELECT clause, you can define a Projection with some alias (depending on expression you may have to use Projections.sqlProjection() to do so) and then order by that alias.

2. Otherwise, you can extend Order class and overwrite its toSqlString() method to generate SQL as you see fit. Be careful that you don't make it non-portable across different databases.

P.S. To address some of the above comments, I'm pretty sure that ORDER BY expression is ANSI SQL standard (can't be 100% sure, though, seeing as how it's not publicly available). It's certainly a de-facto standard - it's supported by latest MySQL / PostgreSQL / Oracle / SQL Server versions.

I believe your just looking for order by first_name desc...

this can be done using the addOrder method of the criteria API:

List<Customer> result=(List<Customer>) session.createCriteria(Customer.class)
                           .add(Restrictions.like("firstName", "bar%"))
                           .addOrder(Order.desc("firstName"))
                           .list();

check here:

Hibernate Criteria Api

You can do following trick:

cachedCriteria.add(Restrictions.sqlRestriction("1=1 order by " + your_expression_here);

The sqlRestriction works as following - it adds AND to the end of query and insert your expression.