Criteria order

gandalf0777 · **Joined:** Tue Sep 18, 2007 3:30 pm **Posts:** 17

Hi,

I would like to sort my address by a number of the building. This number may consist of digits and letters (letters only at the end).
I would like to sort this list using Criteria object. When I am using:

Code:

criteria.addOrder(Order.asc("address.firstNumber"));

I got:
22A, 23, 24B, 3, 31 and so on...
Of course I would like to have the list like that:
3, 22A, 23, 24B, 31...

Of course the problem is because my address.firstNumber field is a String instead of int. I can not use just an integer because this number may be for example 22A.

Is there an easy possibility to sort this list using Criteria object? The only solution of this problem which comes to my mind is to make an usual sql query and not a Criteria.

Regards,
Gandalf

VampBoy · **Posted:** Mon Nov 16, 2009 5:00 pm

I hate to tell you but even if you write it as simple sql (which criteria translates down into) you're going to get the same behavior.

gandalf0777 · **Joined:** Tue Sep 18, 2007 3:30 pm **Posts:** 17

It depends of the exact sql. I did something like that:

Code:

query += " order by address.street, cast(nullif(regexp_replace(address.firstNumber, '^([0-9]*).*$', '\\\\1'),'') as int), address.firstNumber ..."
...
return getHibernateTemplate().find(query, attrs);

This query orders the list by numeric value of the address.firstNumber and then - by the whole value of address.firstNumber (in case there would be the same number and different letters like 24A 24B 24D).

This query works. But of course it works only with the dedicated database platform (postgres in my case) so I was wondering if it could be done with the more universal Criteria object. I guess it is impossible.