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 <= columns; i++) {
       row.put(md.getColumnName(i), rs.getObject(i));
     }
  }
  return row;
}

Post portion of your code where you are creating hashmap and populating with resultset. — Usman Saleem, Sep 21 '11 at 21:52
@Deepak: What you do is generally OK, but you need to `rs.close()` before you return. Copying the data from result set to `HashMap` is correct way provided that you need to access the whole data set. — dma_k, Sep 21 '11 at 22:09
@dma_k: Yes i need to access the whole data set almost all the time. — Deepak, Sep 21 '11 at 22:11
Don't close the ResultSet in this method. The ResultSet is passed as a parameter, so it's the caller responsibility to close it. And the close should be done in a 'finally' block so it gets done even if an Exception is thrown. — Rick Goldstein, Sep 21 '11 at 22:20

RHT · Accepted Answer · 2011-09-21T22:10:12.243

96

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

edited Sep 21 '11 at 22:10

answered Sep 21 '11 at 22:00

RHT

4,974
3
26
32

and why list ?? why cant we use hashmap as it is ? – Deepak Sep 21 '11 at 22:14
1

@Deepak, becasue a List is a collection of records from the database, where each record (row) is represented as a HashMap, where each hashmap is a mapping from column name and the record data. – Zaki Sep 21 '11 at 22:15
Okay i got it!! If i just use HashMap i will get only one row all the time is it ?? – Deepak Sep 21 '11 at 22:17
@Deepak, yes, pretty much it. – Zaki Sep 21 '11 at 22:19
and then there was light! Thanks @Zaki. – RHT Sep 21 '11 at 22:24
1

I was posting my answer while you guys were hashing this out (no pun intended). In any recent Java compiler, you will get warnings for not using type safe collections. Also, the 'list' and 'row' variables should be declared using the type safe versions of List and Map, not ArrayList and HashMap. You then assign new ArrayList and HashMap instances to those variables. – Rick Goldstein Sep 21 '11 at 22:27
3

Returning a List> is a row-centric way of viewing things. If you want a column-centric view, return a Map> where the key is the column name. Sometimes it might be more useful to you. – duffymo Sep 21 '11 at 22:34
@duffymo That's a good point. Would you then want to make those Lists unmodifiable so data from corresponding rows remains aligned? – Rick Goldstein Sep 21 '11 at 22:40
@RHT Datetime field is converted into timestamp using this method is there any way to fetch it as datetime field – Pallav Chanana Jul 03 '20 at 05:20

score 38 · Answer 2 · answered Apr 18 '12 at 15:56

38

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

answered Apr 18 '12 at 15:56

Brad M

825
2
12
14

use while(rs.hasNext()) instead of .next()? – Alexander Mills Feb 13 '19 at 04:23
Thank you for cleaning it up, this works fine! – manudicri Apr 29 '21 at 11:15
@AlexanderMills if you use rs.hasNext() then you will have to add rs.next() inside the loop again anyway. This way its 2 birds 1 stone. – Muneeb Mirza Aug 06 '21 at 14:31
Many thanks @Brad M ! It can even be slightly more simplified by omitting the types for the constructors of ArrayList and HashMap in the corresponding lines: ```List> list = new ArrayList<>();``` and ```HashMap row = new HashMap<>(columns);``` – Alan Z. Feb 03 '22 at 12:37

score 17 · Answer 3 · edited Jul 03 '13 at 19:42

17

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

edited Jul 03 '13 at 19:42

Michael Allan Jackson

4,217
3
35
45

answered Sep 21 '11 at 22:05

Dave Newton

158,873
26
254
302

score 6 · Answer 4 · answered Apr 16 '14 at 09:49

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

I really like this solution. I tried using ResultSet.getArray(int columnIndex) to get whole columns at once, but at least for com.mysql.cj.jdbc.Driver, that resulted in a SQLFeatureNotSupportedException — William, Jan 09 '18 at 21:21

score 4 · Answer 5 · answered Sep 21 '11 at 22:17

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).

score 1 · Answer 6 · answered Oct 02 '19 at 00:08

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<Map<String, Object>> resultAsListMap() throws SQLException
    {
        var md = rs.getMetaData();
        var columns = md.getColumnCount();
        var list = new ArrayList<Map<String, Object>>();

        var currRowIndex = rs.getRow();
        rs.beforeFirst();

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

        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<String, List<Object>> resultAsMapList() throws SQLException
    {
        var md = rs.getMetaData();
        var columns = md.getColumnCount();
        var tmp = new ArrayList[columns];
        var map = new HashMap<String, List<Object>>(columns);

        var currRowIndex = rs.getRow();
        rs.beforeFirst();

        for (int i = 1; i <= columns; ++i)
        {
            tmp[i - 1] = new ArrayList<>();
            map.put(md.getColumnName(i), tmp[i - 1]);
        }

        while (rs.next())
        {
            for (int i = 1; i <= columns; ++i)
            {
                tmp[i - 1].add(rs.getObject(i));
            }
        }

        rs.absolute(currRowIndex);

        return map;
    }

masvel · Answer 7 · 2019-11-04T20:09:30.257

Here is the code little modified that i got it from google -

 List data_table = new ArrayList<>();
    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<String, String> data_map = new HashMap<>();
                if (row_count == 240001) {
                    break;
                }
                for (int i = 1; i <= 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();

it returns the query output in the for of ArrayList> – masvel Nov 04 '19 at 20:08 — masvel, Nov 04 '19 at 20:08
Hi, please be more descriptive in your answers. – ashwin agrawal Nov 04 '19 at 20:20 — ashwin agrawal, Nov 04 '19 at 20:20

score 0 · Answer 8 · answered Dec 07 '19 at 04:37

public static List<HashMap<Object, Object>> GetListOfDataFromResultSet(ResultSet rs) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        int count = metaData.getColumnCount();
        String[] columnName = new String[count];
        List<HashMap<Object,Object>> lst=new ArrayList<>();
        while(rs.next()) {
            HashMap<Object,Object> map=new HashMap<>();
            for (int i = 1; i <= count; i++){
                   columnName[i-1] = metaData.getColumnLabel(i);
                   map.put(columnName[i-1], rs.getObject(i));
            }
            lst.add(map);

        }
        return lst;
    }

score 0 · Answer 9 · answered Aug 15 '23 at 13:29

You can use below example to run and understand it's basics. Very easy fully operational code/example to understand.

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class DynamicRows {

    public static void main(String[] args) {
        DynamicRows dr = new DynamicRows();
        List<Map<String, Object>> rows = dr.getSearchRecords();
    }

    public List<Map<String, Object>> getSearchRecords() {
        ResultSet rs = null;
        Connection conn = null;
        List<Map<String, Object>> rows = null;
        try {
            conn = openConnection();
            if (conn != null) {

                java.sql.PreparedStatement ps = conn.prepareStatement("select * from online_users");
                rs = ps.executeQuery();

                if (rs != null) {
                    rows = new ArrayList<Map<String, Object>>();
//Code get resultset metadata information
                    ResultSetMetaData metaData = rs.getMetaData();
//To get column count in result set
                    int columnCount = metaData.getColumnCount();

                    while (rs.next()) {
                        Map<String, Object> columns = new LinkedHashMap<String, Object>();
//System.out.println("=======Row Start Here===========");
                        for (int i = 1; i <= columnCount; i++) {
                            // To get Column Name
                            // System.out.println(metaData.getColumnLabel(i)+"->"+rs.getObject(i));
                            columns.put(metaData.getColumnLabel(i), rs.getObject(i));
                        }

                        rows.add(columns);
                    }
                }
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            closeConnection(conn);
        }
System.out.println(rows);
        return rows;
    }

    private Connection openConnection() throws SQLException {
        Connection conn = null;
        try {
            System.out.println("Load JDBC Driver");
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {

            System.out.println("Fail to Load JDBC Driver ");
            e.printStackTrace();
            return null;
        }

        System.out.println("JDBC Driver Registered .");

        conn = employee.DbConnection.getDatabaseConnection();

        if (conn != null) {
            System.out.println("JDBC connection is Successful !");
        } else {
            System.out.println("JDBC Connection failed !");
        }
        return conn;
    }

    private void closeConnection(Connection conn) {
        if (conn != null) {
            try {

                conn.close();
                System.out.println("Connection closed successfully");
            } catch (SQLException ex) {
                System.out.println("Failed to close JDBC Connection !");
                ex.printStackTrace();
            }
        } else {

            System.out.println("No JDBC connection to close !");
        }
    }

}

Output :

Load JDBC Driver
JDBC Driver Registered .
JDBC connection is Successful !
Connection closed successfully
[{USERNAME=test, ISACTIVE=False, LOGIN_TIME=2023-08-15 19:01:30.0, LOGOUT_TIME=2023-08-15 19:01:46.0}]

Efficient way to Handle ResultSet in Java

9 Answers9

Linked