How to get Map data using JDBCTemplate.queryForMap

queryForMap is appropriate if you want to get a single row. You are selecting without a where clause, so you probably want to queryForList. The error is probably indicative of the fact that queryForMap wants one row, but you query is retrieving many rows.

Check out the docs. There is a queryForList that takes just sql; the return type is a

List<Map<String,Object>>.

So once you have the results, you can do what you are doing. I would do something like

List results = template.queryForList(sql);

for (Map m : results){
   m.get('userid');
   m.get('username');
} 

I'll let you fill in the details, but I would not iterate over keys in this case. I like to explicit about what I am expecting.

If you have a User object, and you actually want to load User instances, you can use the queryForList that takes sql and a class type

queryForList(String sql, Class<T> elementType)

(wow Spring has changed a lot since I left Javaland.)

To add to @BrianBeech's answer, this is even more trimmed down in java 8:

jdbcTemplate.query("select string1,string2 from table where x=1", (ResultSet rs) -> {
    HashMap<String,String> results = new HashMap<>();
    while (rs.next()) {
        results.put(rs.getString("string1"), rs.getString("string2"));
    }
    return results;
});

I know this is really old, but this is the simplest way to query for Map.

Simply implement the ResultSetExtractor interface to define what type you want to return. Below is an example of how to use this. You'll be mapping it manually, but for a simple map, it should be straightforward.

jdbcTemplate.query("select string1,string2 from table where x=1", new ResultSetExtractor<Map>(){
    @Override
    public Map extractData(ResultSet rs) throws SQLException,DataAccessException {
        HashMap<String,String> mapRet= new HashMap<String,String>();
        while(rs.next()){
            mapRet.put(rs.getString("string1"),rs.getString("string2"));
        }
        return mapRet;
    }
});

This will give you a return type of Map that has multiple rows (however many your query returned) and not a list of Maps. You can view the ResultSetExtractor docs here: http://docs.spring.io/spring-framework/docs/2.5.6/api/org/springframework/jdbc/core/ResultSetExtractor.html

You can do something like this.

 List<Map<String, Object>> mapList = jdbctemplate.queryForList(query));
    return mapList.stream().collect(Collectors.toMap(k -> (Long) k.get("userid"), k -> (String) k.get("username")));

Output:

 {
  1: "abc",
  2: "def",
  3: "ghi"
}