Is there a way to use Query By Criteria to order the results without regard to case?

I want to return the results ordered but I'm getting upper case sorted before lower. I used the Criteria.addOrder( Order.asc( "field" ) ) method. With SQL I would use a lower( "field" ) statement to get the results I want. The only resolution I can see is to rewrite some code using straight sql.

I gotta believe that this can be done with hibernate but alas I couldn't find it on my own so this is a last resort.

Did you try HQL?
Or is it necessary for you to use Criteria API?

I know I could do it in HQL its just that I have a utility class that performs queries for me using QBC with java reflection. I don't think I could do the same sort of thing with HQL or if I could it would be a massive re-write. I prefer to use QBC 's flexibility. If nothing else I could do the sorting in java using comparators.

It just seems that this would be a common thing that the folks at hibernate would have accounted for some way or another.

Well it's kind-of a hack, but one way to do this is as follows.


1. Add a "fake" property to your class mapping with a formula that uses whatever your database's equivilent of the lower() function is.


2. Add dummy a dummy getter/setter to your entity class (or a real getter/setter if you prefer).


3. Now you can use a Criteria like this.

Then simply extend Order class.
It should be very simple.

I tried as you suggested alesj. I extended the class and overwrote the toString().

public String toString()
{
String superString = super.toString();
return "lower( " + superString.substring( 0, superString.indexOf( ' ') ) + " ) " + superString.substring( superString.indexOf( ' ' ) +1, superString.length() );
}

The toString() returns what I expected but hibernate must not be using it to generate the sql. It didn't fix my problem. Good idea though.

Ok thanks to alesj the solution is not within the toString() but to overwrite the toSqlString() method as follows (in case anyone cares).

public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery)
throws HibernateException {
String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, this.propertyName);
StringBuffer fragment = new StringBuffer();
for ( int i=0; i<columns.length; i++ ) {
fragment.append( " lower( " ).append( columns[0] ).append( " )" ).append( this.ascending ? " asc" : " desc" );
if ( i<columns.length-1 ) fragment.append(", ");
}
return fragment.toString();
}


the fragment.append inside the for loop is the only change to this method. Works great.

Use 3.1, or simply subclass Order yourself.

Criteria.addOrder( Order.asc( "field" ).ignoreCase())

I am using hibernate 3.2.

@rakesh2002

Have you tested this? I too am using 3.2 and it is NOT working... Items get sorted like this:

Aab
Baa
aab

Which is really annoying...