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

Rociorock

Post portion of your code where you are creating hashmap and populating with resultset. – Scull

Misleading method name...I see no ArrayList. – Homoeroticism

@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. – Tarp

@Zaki: Sorry for that :P – Rociorock

@dma_k: Yes i need to access the whole data set almost all the time. – Rociorock

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. – Pollinate

Answer 1

1. Iterate over the ResultSet
2. Create a new Object for each row, to store the fields you need
3. Add this new object to ArrayList or Hashmap or whatever you fancy
4. 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;
}

Rm

and why list ?? why cant we use hashmap as it is ? – Rociorock

@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. – Homoeroticism

Okay i got it!! If i just use HashMap i will get only one row all the time is it ?? – Rociorock

@Deepak, yes, pretty much it. – Homoeroticism

and then there was light! Thanks @Zaki. – Rm

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. – Pollinate

Returning a List<Map<String, Object>> is a row-centric way of viewing things. If you want a column-centric view, return a Map<String, List<Object>> where the key is the column name. Sometimes it might be more useful to you. – Goddard

@Goddard That's a good point. Would you then want to make those Lists unmodifiable so data from corresponding rows remains aligned? – Pollinate

@Rm Datetime field is converted into timestamp using this method is there any way to fetch it as datetime field – Ep

Answer 2

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

Daze

use while(rs.hasNext()) instead of .next()? – Recrimination

Thank you for cleaning it up, this works fine! – Franky

@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. – Splore

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<HashMap<String,Object>> list = new ArrayList<>(); and HashMap<String,Object> row = new HashMap<>(columns); – Perrins

Answer 3

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

Mendymene

Answer 4

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

Freeze

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 – Mizzen

Answer 5

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

Pollinate

Answer 6

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

Overbite

Answer 7

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

Dogvane

it returns the query output in the for of ArrayList<HashMap<String,String>> – Dogvane

Hi, please be more descriptive in your answers. – Potaufeu

Answer 8

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

Glamorous

Answer 9

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

Paleozoic