• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

probleme with order by

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Tomorrow is the first day of the new metric calendar. Comfort me tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic