ansaurus

Question

Problem with JPA Order By and swedish characters

Answer 1

+1 A:

You're running into what is called 'collation' in the Database world. I know that Oracle supports many different languages and will then correctly process your ORDER BY clause.

Look for information in the oracle documentation about possible values for the Oracle NLS_SORT session variable. I found this

Set your collation like this:

ALTER SESSION SET NLS_SORT = SWEDISH

Then run your query on the oracle prompt. If you make sure that you set this for every connection you should be fine.

So the problem does not lie with JPA/Hibernate or even JDBC.

Hans Westerbeek 2010-02-23 09:25:36

The order is correct when running the query in an sql-client. When I run it through the application (JPA) the order gets altered. The same problem exists when using HSQL (in-memory-mode) which default use UTF-8.

Jörgen Brutar 2010-02-23 10:07:56

The character encoding (UTF-8) is not really related to this problem.You have to remember that when using JPA, every connection is a 'oracle session'. every connection gets created with default environment var values. Therefore, NLS_SORT is probably not set to the value that you want (SWEDISH). So you need to make sure that NLS_SORT is set correctly before executing a query that requires swedish alpabetical collation.As far as HSQLDB is concerned, it has a different way of setting collation. See here: http://hsqldb.org/doc/guide/ch09.html#collation-section

Hans Westerbeek 2010-02-23 10:52:58

Ok, got it working now. Specifying the nls_sort and using the function nlssort did the trick.Select * from adress order by nlssort(name, 'nls_sort = SWEDISH'). I would however prefer if it sould be set att the database level as default or if that is not possible as a connection parameter on the jdbc-url. Thank you.

Jörgen Brutar 2010-02-23 13:14:42

I think that should be possible, but you'll have to look it up in the Oracle documentation. The use-case I had for it a few years ago was a bit different

Hans Westerbeek 2010-02-23 15:56:01

Answer 2

A:

Which hibernate dialect are you specifying? That might make a difference. Here's an article that touches on related issues (albeit for Mysql).

davek 2010-02-23 09:28:12

I am using Oracle10gDialect for Oracle and HSQLDialect for HSQL.

Jörgen Brutar 2010-02-23 10:05:22

ansaurus

tags:

views:

answers:

Problem with JPA Order By and swedish characters

related questions