ORDER BY problem, when String starts with language specific character

Dear all,

I have a problem with ORDER BY clause. I have a simple query:

select u from User u order by u.name

When the name starts with "normal/english" character, records are ordered correctly. But when it starts with language specific character like 'Č', these records are placed after records with normal charaters. So records with 'Č' are after 'Z', but this is not correct, because 'Č' is between 'C' and 'D'.

Is there any way, how to sort the records correctly?

Thank you

Michael

ObjectDB uses the ordinal Java String lexicographical order, which is based on the order of the characters in Unicode. In Unicode, ordinary English letters are located separated from other language letters.

Future versions of ObjectDB should support also other ordering methods.

As a workaround you may use the Java Normalizer class. Because ObjectDB supports method invocation in queries (as an extension to JPQL and JDOQL) you can use the Normalizer expression directly in the query ORDER BY clause.

However, if you have to sort a large result list - it might be more efficient to add a persistent normalizedName field to the entity class and maybe even an index on that field (this will also keep your code JPA portable).

On a second thought, maybe you don't need 2 fields - name and normalizedName, and just keeping strings in name using the decomposed form rather than the composed form will solve the problem.

Thank you for reply.

I've tried to add the normalizer expression into order by clause, but it thorws this exception:

com.objectdb.o._PersistenceException: Unexpected query token 'java'

for query:

select u from User u order by java.text.Normalizer.normalize(
  u.name, java.text.Normalizer.Form.NFD)

or

com.objectdb.o._PersistenceException: Unexpected query token 'Normalizer'

for query:

select u from User u order by Normalizer.normalize(u.name, Normalizer.Form.NFD)

How should the correct query look like?

Michael

It seems that Java standard methods are not supported by the current version, but this should work:

package com.objectdb.forum;

import java.text.*;
import java.util.*;

import javax.persistence.*;


public final class T352
{
    public static void main(String[] args)
    {
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory("$objectdb/db/test.odb");
        EntityManager em = emf.createEntityManager();
       
        em.getTransaction().begin();
        em.persist(new MyEntity("a"));
        em.persist(new MyEntity("b"));
        em.persist(new MyEntity("c"));
        em.getTransaction().commit();

        Query query = em.createQuery(
            "SELECT e.name FROM MyEntity e ORDER BY e.getNormName()");
        query.setParameter("names", Arrays.asList("a", "c"));
        List resultList = query.getResultList();
        System.out.println(resultList);

        em.close();
        emf.close();
    }
   
    @Entity
    public static final class MyEntity {
        private String name;
        public MyEntity(String name) {
            this.name = name;
        }
        String getNormName() {
            return Normalizer.normalize(name, Normalizer.Form.NFD);
        }
    }
}

Thank you, this works...