'ORDER BY Not Working in JPQL Query

I have the following method that returns a HashMap based on a Users Department and Building. I want to ORDER BY ASC based on the user name

private HashMap<Long, String> getCriticalPde(long departmentId, long buildingId) {
    HashMap<Long, String> map = new HashMap<Long, String>();
    Query query = emf
        .createEntityManager()
        .createQuery(
            "SELECT parent FROM Table1 parent WHERE parent.id NOT IN "
            + "(SELECT chldQry.userId FROM Table2 chldQry "
            + "WHERE chldQry.departmentId = :departmentId "
            + "AND chldQry.buildingId = :buildingId)"
            + "ORDER BY parent.userName ASC");
        query.setParameter("departmentId", departmentId);
        query.setParameter("buildingId", buildingId);
        List<User> list= (List <User>) query.getResultList();
        for (User user : userList) {
            map .put(user.getId(), user.getName());
        }
    return map;
}

However when my map is returned it remains ordered by UserId. This script does ORODER BY correctly when ran in Toad/Squirrel/etc. Any ideas?

javasqljpql


Solution 1:[1]

You are loosing the order because you are using a HashMap, which does not guarantee the order of iteration. Try using a LinkedHashMap instead, it will preserve the original order.

Solution 2:[2]

The order by definately works, what doesn't is the HashMap. Hashmaps don't preserve insert order. Use a LinkedHashMap instead

Solution 3:[3]

You are retrieving an ordered resultlist and then storing in in an UNordered hashmap. Use an ordered collection to store the users.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Rudy Vissers
Solution 2 Roel Strolenberg
Solution 3 Buurman

Related Questions