I'm using a ResultSet in Java, and am not sure how to properly close it. I'm considering using the ResultSet to construct a HashMap and then closing the ResultSet after that. Is this HashMap technique efficient, or are there more efficient ways of handling this situation? I need both keys and values, so using a HashMap seemed like a logical choice.
If using a HashMap is the most efficient method, how do I construct and use the HashMap in my code?
Here's what I've tried:
public HashMap resultSetToHashMap(ResultSet rs) throws SQLException {
ResultSetMetaData md = rs.getMetaData();
int columns = md.getColumnCount();
HashMap row = new HashMap();
while (rs.next()) {
for (int i = 1; i <= columns; i++) {
row.put(md.getColumnName(i), rs.getObject(i));
}
}
return row;
}
- Iterate over the ResultSet
- Create a new Object for each row, to store the fields you need
- Add this new object to ArrayList or Hashmap or whatever you fancy
- Close the ResultSet, Statement and the DB connection
Done
EDIT: now that you have posted code, I have made a few changes to it.
public List resultSetToArrayList(ResultSet rs) throws SQLException{
ResultSetMetaData md = rs.getMetaData();
int columns = md.getColumnCount();
ArrayList list = new ArrayList(50);
while (rs.next()){
HashMap row = new HashMap(columns);
for(int i=1; i<=columns; ++i){
row.put(md.getColumnName(i),rs.getObject(i));
}
list.add(row);
}
return list;
}
I just cleaned up RHT's answer to eliminate some warnings and thought I would share. Eclipse did most of the work:
public List<HashMap<String,Object>> convertResultSetToList(ResultSet rs) throws SQLException {
ResultSetMetaData md = rs.getMetaData();
int columns = md.getColumnCount();
List<HashMap<String,Object>> list = new ArrayList<HashMap<String,Object>>();
while (rs.next()) {
HashMap<String,Object> row = new HashMap<String, Object>(columns);
for(int i=1; i<=columns; ++i) {
row.put(md.getColumnName(i),rs.getObject(i));
}
list.add(row);
}
return list;
}
RHT pretty much has it. Or you could use a RowSetDynaClass and let someone else do all the work :)
A couple of things to enhance the other answers. First, you should never return a HashMap
, which is a specific implementation. Return instead a plain old java.util.Map
. But that's actually not right for this example, anyway. Your code only returns the last row of the ResultSet as a (Hash)Map. You instead want to return a List<Map<String,Object>>
. Think about how you should modify your code to do that. (Or you could take Dave Newton's suggestion).
this is my alternative solution, instead of a List of Map, i'm using a Map of List.
Tested on tables of 5000 elements, on a remote db, times are around 350ms for eiter method.
private Map<String, List<Object>> resultSetToArrayList(ResultSet rs) throws SQLException {
ResultSetMetaData md = rs.getMetaData();
int columns = md.getColumnCount();
Map<String, List<Object>> map = new HashMap<>(columns);
for (int i = 1; i <= columns; ++i) {
map.put(md.getColumnName(i), new ArrayList<>());
}
while (rs.next()) {
for (int i = 1; i <= columns; ++i) {
map.get(md.getColumnName(i)).add(rs.getObject(i));
}
}
return map;
}