Efficient way to Handle ResultSet in Java

Question

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  lt   columns  i             row put md getColumnName i   rs getObject i                 return row

User · Accepted Answer

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;
}

User · Answer

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 lt String  List lt Object gt  gt  resultSetToArrayList ResultSet rs  throws SQLException       ResultSetMetaData md   rs getMetaData        int columns   md getColumnCount        Map lt String  List lt Object gt  gt  map   new HashMap lt  gt  columns       for  int i   1  i  lt   columns    i            map put md getColumnName i   new ArrayList lt  gt                while  rs next              for  int i   1  i  lt   columns    i                map get md getColumnName i   add rs getObject i                         return map

User · Answer

i improved the solutions of RHTs Brad Ms and of Lestos answer   i extended both solutions in leaving the state there  where it was found  So i save the current ResultSet position and restore it after i created the maps   The rs is the ResultSet  its a field variable and so in my solutions-snippets not visible   I replaced the specific Map in Brad Ms solution to the gerneric Map       public List lt Map lt String  Object gt  gt  resultAsListMap   throws SQLException               var md   rs getMetaData            var columns   md getColumnCount            var list   new ArrayList lt Map lt String  Object gt  gt              var currRowIndex   rs getRow            rs beforeFirst             while  rs next                          HashMap lt String  Object gt  row   new HashMap lt String  Object gt  columns               for  int i   1  i  lt   columns    i                                row put md getColumnName i   rs getObject i                               list add row                      rs absolute currRowIndex            return list          In Lestos solution  i optimized the code  In his code he have to lookup the Maps each iteration of that for-loop  I reduced that to only one array-acces each for-loop iteration  So the program must not seach each iteration step for that string-key       public Map lt String  List lt Object gt  gt  resultAsMapList   throws SQLException               var md   rs getMetaData            var columns   md getColumnCount            var tmp   new ArrayList columns           var map   new HashMap lt String  List lt Object gt  gt  columns            var currRowIndex   rs getRow            rs beforeFirst             for  int i   1  i  lt   columns    i                        tmp i - 1    new ArrayList lt  gt                 map put md getColumnName i   tmp i - 1                       while  rs next                          for  int i   1  i  lt   columns    i                                tmp i - 1  add rs getObject i                                     rs absolute currRowIndex            return map

User · Answer

RHT pretty much has it  Or you could use a RowSetDynaClass and let someone else do all the work

User · Answer

Here is the code little modified that i got it from google -   List data table   new ArrayList lt  gt         Class forName  oracle jdbc driver OracleDriver                con   DriverManager getConnection conn url  user id  password               Statement stmt   con createStatement                System out println  query string    query string               ResultSet rs   stmt executeQuery query string               ResultSetMetaData rsmd   rs getMetaData                  int row count   0              while  rs next                      HashMap lt String  String gt  data map   new HashMap lt  gt                     if  row count    240001                        break                                    for  int i   1  i  lt   rsmd getColumnCount    i                          data map put rsmd getColumnName i   rs getString i                                      data table add data map                   row count   row count   1                            rs close                stmt close                con close

User · Answer

I just cleaned up RHT s answer to eliminate some warnings and thought I would share   Eclipse did most of the work   public List lt HashMap lt String Object gt  gt  convertResultSetToList ResultSet rs  throws SQLException       ResultSetMetaData md   rs getMetaData        int columns   md getColumnCount        List lt HashMap lt String Object gt  gt  list   new ArrayList lt HashMap lt String Object gt  gt          while  rs next              HashMap lt String Object gt  row   new HashMap lt String  Object gt  columns           for int i 1  i lt  columns    i                row put md getColumnName i  rs getObject i                      list add row              return list

User · Answer

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 lt Map lt String Object gt  gt   Think about how you should modify your code to do that   Or you could take Dave Newton s suggestion

User · Answer

public static List lt HashMap lt Object  Object gt  gt  GetListOfDataFromResultSet ResultSet rs  throws SQLException           ResultSetMetaData metaData   rs getMetaData            int count   metaData getColumnCount            String   columnName   new String count           List lt HashMap lt Object Object gt  gt  lst new ArrayList lt  gt             while rs next                  HashMap lt Object Object gt  map new HashMap lt  gt                 for  int i   1  i  lt   count  i                        columnName i-1    metaData getColumnLabel i                      map put columnName i-1   rs getObject i                              lst add map                      return lst

[java] Efficient way to Handle ResultSet in Java

Examples related to java

Examples related to hashmap

Examples related to resultset

Examples related to sqlresultsetmapping