I am using Criteria API in Hibernate 3 and need to perform case insensitive sort, I know how to do it with HQL but the requirement is to use Criteria API.

something like:

criteria.addOrder(Order.asc("propertyName") will translate into SQL:

order by column_name asc

what we need to achieve is SQL:

order by lower(column_name) asc

Hibernate's Order class will not understand something like
Order.asc("lower(property_name)") and there is no flag to pass to indicate case insensitive sorting.

Any suggestions/comments on how to do this with Criteria API.

Thank you,

Eugene

One way to do it would be to add a new property to your class mapping for your lowercase property.



You'll have to have at least dummy getters and setters in your Java class. If you don't care about always getting the property back as lowercase, you can add the formula directly to the real property itself.

Then in your code, you'd have something like this:

thanks for quick reply, i tried it and this works, the only negative side effect of this approach is the when doing Hibernate includes the formula in the select, even though update/insert is set to false and lazy is set to true, i.e

generated sql looks like:

select column1, column2, lower(column1) as formula0_0_ from table_name order by lower(column1) asc

so it does the job, but I am not sure what performance implications would be especially if you have a lot of those "formula" properties to provide different sorting options.

thanks again for your help, and if you know how to exclude that formula (via mapping) from selected columns while still using it for order by please let me know.

What about honoring jdl by rating his post as helpful. :)

already done :)