Hibernate and order by using column values

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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