JPQL query: use ORDER BY case insensitively

Question

Using this thread (which is very helpful) so I do the same to may jpql query(very simple, just select all record but need to sorted by 'NAME' field):

SELECT o, LOWER(o.name) AS nameInOrder FROM UserGroup o ORDER BY nameInOrder ASC

I am using openjpa, and unfortunately the application gives me

 java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to com.myname.app.UserGroup

The code running the whole jpql is like this:

public static List<UserGroup> findAllUserGroups(){
    return entityManager().createQuery("SELECT o, LOWER(o.name) AS nameInOrder FROM UserGroup o ORDER BY nameInOrder ASC", UserGroup.class).getResultList();
}

My guessing, is seems entityManager take o as an object but LOWER(o.name) as another?

Please help, really dont see any problem but OpenJPA(v2.2) doesn't coorperate.

Answer 1

As per the thread you linked to, the return type from this query is List<Object[]> and not List<UserGroup>.

One otion is to leave the query as-is and pull out the UserGroups from the List by looping over it:

List<Object[]> results = entityManager().createQuery("SELECT o, LOWER(o.name) AS nameInOrder FROM UserGroup o ORDER BY nameInOrder ASC", UserGroup.class).getResultList();
List<UserGroup> userGroups = new ArrayList<UserGroup>();
for(Object[] result : results) {
    userGroups.add( result[0] );
}
return userGroups;