JDBCTemplate queryForMap for retrieving multiple rows

Question

Can I use queryForMap if there are multiple rows returned by the query.

For a single row, the below code works fine.

public Map<String, Object> retrieveMultipleRowsColumns(String deptName){
    return jdbcTemplate.queryForMap("SELECT DEPT_ID,DEPT_NAME FROM DEPT WHERE DEPT_NAME = ?", deptName);
}

How to modify this for multiple rows?

What database are you using? – John Farrelly Jun 14 '14 at 15:06 — John Farrelly, Jun 14 '14 at 15:06
I am using Oracle 10g ; is it database dependent? – user182944 Jun 14 '14 at 15:09 — user182944, Jun 14 '14 at 15:09

score 27 · Answer 1 · edited Jun 13 '16 at 20:56

I know this is really old, but there is a much simpler way to do this if you're looking for a 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/docs/current/javadoc-api/org/springframework/jdbc/core/ResultSetExtractor.html

score 25 · Accepted Answer · answered Jun 14 '14 at 15:16

25

Use queryForList see the javadoc for full details. It returns List<Map<String,Object>>

public List<Map<String, Object>> retrieveMultipleRowsColumns(String deptName){
    return jdbcTemplate.queryForList("SELECT DEPT_ID,DEPT_NAME FROM DEPT WHERE DEPT_NAME = ?", deptName);
}

answered Jun 14 '14 at 15:16

John Farrelly

7,289
9
42
52

7

A list of maps is not very useful. – Erica Kane Jun 13 '16 at 19:22
10

Incorrect, a list of maps can be very useful depending on the context. – Rex Sheridan Jun 30 '17 at 14:21
@EricaKane I'm intrigued as to why you would think that. – zb226 Nov 10 '21 at 11:31

score -3 · Answer 3 · edited Aug 12 '15 at 12:16

public <T> List<T> queryForList(String sql,
                            Class<T> elementType,
                            Object... args)
                 throws DataAccessException

Description copied from interface: JdbcOperations Query given SQL to create a prepared statement from SQL and a list of arguments to bind to the query, expecting a result list. The results will be mapped to a List (one entry for each row) of result objects, each of them matching the specified element type.

Specified by:queryForList in interface JdbcOperations , Parameters:sql - SQL query to execute elementType - the required type of element in the result list (for example, Integer.class)args - arguments to bind to the query (leaving it to the PreparedStatement to guess the corresponding SQL type); may also contain SqlParameterValue objects which indicate not only the argument value but also the SQL type and optionally the scale Returns:a List of objects that match the specified element type Throws: DataAccessException - if the query failsSee Also:JdbcOperations.queryForList(String, Class), SingleColumnRowMapper

JDBCTemplate queryForMap for retrieving multiple rows

3 Answers3