Return Map<Long, String> directly from CrudRepository

Say I have a table users with columns id, role, name etc.

There's UserRepository defined as:

public interface UserRepository extends CrudRepository<User, Long> {
    ...
}

I need to return all ids and names of users with role='ADMIN' as Map<Long, String> e.g.:

@Query("SELECT u.id, u.name FROM User u WHERE u.role = :role")
Map<Long, String> findIdAndNameByRole(String role);

But this call returns an error:

> javax.persistence.NonUniqueResultException: query did not return a unique result

Question: how (if possible at all) to map the response from CrudRepository directly to Map<Long, String> without using projections or other conversions? Perhaps using nativeQuery=true somehow?

According to the documentation repository methods basically only can single primitive values, a single objects of the repository type (User in your case) or various collections of it.

You will have to use a conversion method of some type in any case. Can you elaborate why you can't use any "projections or other conversions"? It's generally good practice for your business logic classes not to access repositories directly anyway, but use an intermediate service class that do exactly such conversions, since it's not an uncommon scenario.

The only alternative I can think of is to use a default method in the repository interface that does the conversion, but that may not work.

Example:

public interface UserRepository extends CrudRepository<User, Long> {
    // Normal repository method. No @Query needed.
    Stream<User> findByRole(String role);

    default Map<Long, String> findIdAndNameByRole(String role) {
         return findByRole(role).collect(toMap(User::getId, User::getName));
    }
}

You are getting error because the query is returning multiple rows.

So to fix this, you can create a list of Map instead of passing the Map.

@Query("SELECT u.id AS id, u.name AS name FROM User u WHERE u.role = :role")
List<Map<String, Object>> findIdAndNameByRole(String role);

And for iterating this list of map, you can create a method like below:

public Map<Long, String> getIdAndNameMapByRole(String role) {
    List<Map<String, Object>> resultList = userRepository.findIdAndNameByRole(role);
    Map<Long, String> idAndNameMap = new HashMap<>();

    
    for (Map<String, Object> result : resultList) {
        Long id = (Long) result.get("id");
        String name = (String) result.get("name");
        idAndNameMap.put(id, name);
    }

    return idAndNameMap;
}