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?
A RowMapper is usually a simpler choice for ResultSet processing, mapping one result object per row instead of one result object for the entire ResultSet. ResultSetExtractor is suppose to extract the whole ResultSet (possibly multiple rows), while RowMapper is feeded with row at a time.
queryForList might be what you are looking for: List<Map<String, Object>> rows = jdbcTemplate. queryForList("SELECT name, middle, family FROM table"); Every Map in this List represents a row in the returned query, the key represents the column name, and the value is the value of that column for that row.
JdbcTemplate "queryForObject" and "query" is deprecated in Spring.
Explanation: In your DAO methods, you can simply call the getJdbcTemplate() method to retrieve the JDBC template. You also have to delete the dataSource and jdbcTemplate properties, as well as their setter methods, from your DAO class, because they have already been inherited.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With