I am using Oracle 8i
and using "order by c.fieldName ASC " in HQL


How can I construct the HQL query to get the sored result field (VARCHAR2) nnot case sensitive?

for example:
Currently it returns order like this: A,B,C,D,a,b,c,d

how can I get the result like A,a,B,b,C,c,D,d?


Thanks

try using "order by lower(c.fieldName) ASC" or whatever sql function Oracle uses for lowercase

Its not HQL its the database. Try an SQL query in sqlplus to confirm.
You can choose to not use the order by clause and use Java to sort the result anyway you like.

Hi,
By any chance we can sort the list in case insensitive without using HQL? Its just that we are using the paging functionality (setFirstResult+setmaxResults) as well. So, sorting at the java end is not possible.

Thanks.

How do you accomplish case-insensitive ordering in the Criteria API?

Use the Expression.ilike() method.

For eg
Criteria crit = // Create a criteria obj
crit.add(Expression.ilike("username", anObj.getUsername(), matchMode));

The ordering clause as shown by Mike will order in the database. The HQL is being translated to SQL and executed at the database. Thus you will be fine with the paging.

[quote="david"][quote="pearljam118"]Hi,
By any chance we can sort the list in case insensitive without using HQL? Its just that we are using the paging functionality (setFirstResult+setmaxResults) as well. So, sorting at the java end is not possible.

Thanks.[/quote]

The ordering clause as shown by Mike will order in the database. The HQL is being translated to SQL and executed at the database. Thus you will be fine with the paging.[/quote]


Thanks David,

I kinda work that one out :)

Just more info. I basically have a "PageCritieria" object takes in a page number (if applicable) which simply append the neccessary "criteria" to the Criteria or Query object. The object will also calculate the first and max result as well. It's essential 4 lines

int blockSize = getBlockSize() + 1; // Add the 1 for next block
int firstElement = calcStartingPageElementForBlock(); // Starts from 0 and not 1

// Now we can add the extra criteria for paging
aQuery.setMaxResults(blockSize);
aQuery.setFirstResult(firstElement);

At the moment, I have to pass the param to the "PageCriteria" object. What it would be "nice" is in the Query and Criteria object, it provide the methods for me (as a standardise method)! :P

I could've overloading the Query and Criteria object and called it "PJQuery" and "PJCriteria", but I chose not to... just in case a common functionality is available from Hibernate in the future. :)