It's not a secret anymore!*
The moose likes Object Relational Mapping and the fly likes probleme with order by Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "probleme with order by" Watch "probleme with order by" New topic
Author

probleme with order by

emmanuel morisseau
Greenhorn

Joined: Nov 30, 2011
Posts: 5
Hello everyone, it seem to be a little difference between HQL and the oracle for sorting (AZ) asc.

here is the query generated

with a order by TITRE asc

We want the number appear first a then le letter .
If I make the request with oracle. Actual numbers will appear first.

But with java



letters appear first and after the number.

ps I use Seam Framework for how to manage hibernate.

Can someone tell me why in java the letters appear in first
thank
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Do you mean to say that your TITRE column includes values which are numbers (like "42") and also values which are letters (like "NON") and that different environments cause these values of the TITRE column to appear in different sequences?
emmanuel morisseau
Greenhorn

Joined: Nov 30, 2011
Posts: 5
yes example with oracle

the title
1940 : album anniversaire / Paul Villatoux, Frédéric Guelton, Jean Delahaye.
Algérie, les romans de la guerre / textes choisis et présentés par Guy Dugas.

1940 appear firs

but in java with the code

we got .
Algérie, les romans de la guerre / textes choisis et présentés par Guy Dugas.
1940 : album anniversaire / Paul Villatoux, Frédéric Guelton, Jean Delahaye.

this is the problem.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Can you run some tests with more than two books? It might be simply that your "Oracle" example is sorted and your "Java" example is not sorted. With only two rows you can't tell.
emmanuel morisseau
Greenhorn

Joined: Nov 30, 2011
Posts: 5
In my test , I got 28 row.
18 start with a number a 10 with a letter

In oracle the 18 first is the number

In java after


the 10 first is the letter and after the title who begin with a number.
lavnish lalchandani
Ranch Hand

Joined: Feb 28, 2007
Posts: 79
seems like there is some additional sorting happening in java side
( to verify above may be you can write a simple java code seperately that fetches the records using same HQL )
did you try adding debug statements just after running HQL , or the results you are telling us are the one you see on UI
can you post exact java code , SQL create table command ... i will like to make a small app and run it my self


lavnish.blogspot.com
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

It could also mean that Java and Oracle sort using different collation rules.

Oracle databases have some default collation order, and that might be different from the default ordering of Strings in Java. The default Java ordering of Strings is not always what you might want, as various lexicographical rules are not honored by it. This is what Java collators are for, as these perform locale-sensitive String comparison.

In Oracle, instead of collators you can use the NLSSORT function or NLS_SORT session parameter to use different collation rules, either in one query, or in your entire session. (Be aware that using NLS_SORT different form the database's default can affect query plans and hinder performance, as indexes are obviously usable only for one collation order. You may need to create indexes for other collations, if you want to use them.)

It's possible that either Oracle and Java default sorting order are not compatible with each other, or that there is a Collator used in Java and/or NLS_SORT in Oracle that are not compatible. You may verify the value of Oracle's NLS_SORT parameter by executing the query:

I don't know Hibernate, but I'd be surprised if it didn't offer a way to set collation order to use (and it probably does, as googling Hibernate collation yields some interesting results). You might try to set the Hibernate collation order to be compatible with Oracle's (or the other way around, of course, but that could make the indexes unusable, see above).

Another way of going around it would be to always perform sort in Hibernate, that way it would always be consistent.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: probleme with order by