CODEWITHSUNDEEP
sqljakarta-eejpajpqlopenjpa
Dreamer
Dreamer

Reputation: 7549

JPQL query: use ORDER BY case insensitively

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.

Upvotes: 1

Views: 1486

Answers (2)

Dreamer
Dreamer

Reputation: 7549

For the record, here is my implementation following jonc's advice

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

Consider result is each recordset getting back from the query

resultArray[0] is `o`,
resultArray[1] is `LOWER(o.name) AS nameInOrder`, which can be ignored and only use for ordering purpose.

Upvotes: 0

jonc
jonc

Reputation: 795

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;

Upvotes: 1

Related Questions