Storing Multiple Rows from MySQL query in MultiDimensional HashMap

Question

So I'm trying to store a MySQL query result set into a multi dimensional HashMap as listed so:

  public HashMap<String, HashMap<String, String>> getData(String query)
  {
     Statement stmt = null;
     HashMap<String, HashMap<String, String>> results = new HashMap<String, HashMap<String, String>>();

     try
     {
        stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        ResultSetMetaData rsmd = rs.getMetaData();

        while (rs.next())
        {
           for (int i = 1; i < rsmd.getColumnCount() + 1; i++)
           {
              results.put(Integer.toString(i - 1), new HashMap<String, String>());
              results.get(Integer.toString(i - 1)).put(rsmd.getColumnLabel(i), rs.getString(i));
           }
        }
     }
     catch (SQLException ex)
     {
        ex.printStackTrace(System.out);
     }

     return results;
  }

However when using the function to print it out as so:

   public static void printMap(Map mp)
   {
       Iterator it = mp.entrySet().iterator();

       while (it.hasNext())
       {
           Map.Entry pair = (Map.Entry)it.next();
           System.out.println(pair.getKey() + " = " + pair.getValue());
           it.remove();
       }
   }

It is only storing a single row result and I can't wrap my head around why.

0 = {Date=2014-11-04}
1 = {Num=1256}
2 = {ATime=null}
3 = {ALocCode=null}
4 = {DTime=1:00 PM}
5 = {DLocCode=JFK}
6 = {EstATime=8:00 PM}
7 = {EstDTime=1:00 PM}
8 = {EId=7624}

My question is, and the only way I can put it is relating to PHP, is how can I make it store like this?

$result[0]['Date'] = '3214';
....
$result[1]['Date'] = '6426';

Since that is essentially what I'm trying to achieve?

Iłya Bursov · Accepted Answer · 2015-10-07 22:36:32Z

3

main problem that you've swapped "rows" and "columns", next one is that you're re-creating HashMap every time you put field, proper code will look like this:

public Map<String, Map<String, String>> getData(final String query) {
    final Map<String, Map<String, String>> results = new HashMap<>();

    try (final Statement stmt = this.conn.createStatement(); final ResultSet rs = stmt.executeQuery(query);) {
        final ResultSetMetaData rsmd = rs.getMetaData();
        long rId = 0;
        while (rs.next()) {
            final Map<String, String> record = new HashMap<>();
            for (int i = 1; i < (rsmd.getColumnCount() + 1); i++) {
                record.put(rsmd.getColumnLabel(i), rs.getString(i));
            }
            results.put(String.valueOf(rId++), record);
        }
    } catch (final SQLException ex) {
        ex.printStackTrace(System.out);
    }

    return results;
}

public static void printMap(final Map<?, ?> mp) {
    for (final Entry<?, ?> entry : mp.entrySet()) {
        final Object key = entry.getKey();
        final Object value = entry.getValue();
        if (value instanceof Map) {
            System.out.println(key);
            printMap((Map<?, ?>) value);
        } else {
            System.out.println(key + "=" + entry.getValue());
        }
    }
}

answered Oct 7, 2015 at 22:36

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rhododendron Over a year ago

Why Map instead of HashMap?

Rhododendron Over a year ago

Also final Entry wouldn't compile, changing it to final Map.Entry fixes it :D

Andreas Over a year ago

@EllisonPatterson Using Entry unqualified will work if you import it.

Andreas Over a year ago

@EllisonPatterson The reason for using Map instead of HashMap in variables is so you can change to another map easily, e.g. TreeMap if you want columns sorted by name, or LinkedHashMap if you want to retain column order of the query.

Rhododendron Over a year ago

Ah gotcha! Well thank you, now I just gotta disect it because how you're printing is more complex than I thought!

Andreas · Accepted Answer · 2015-10-07 23:21:20Z

1

The answer by Lashane is good for the errors you needed solving, however it can be improved:

You wanted numeric access ($result[0]['Date']) to the rows, not string.
print method should use fully typed parameter.
Rows should be stored in TreeMap or LinkedHashMap or ArrayList to retain row order. ArrayList is better for your case, actually.
Columns should be stored in LinkedHashMap to retain column order.
Do not catch exception and continue. Allow it to cascade up to caller.

Updated version:

public List<Map<String, String>> getData(final String query) throws SQLException {
    final List<Map<String, String>> results = new ArrayList<>();
    try (Statement stmt = this.conn.createStatement();
         ResultSet rs = stmt.executeQuery(query)) {
        ResultSetMetaData metaData = rs.getMetaData();
        while (rs.next()) {
            Map<String, String> record = new LinkedHashMap<>();
            for (int col = 1; col <= metaData.getColumnCount(); col++)
                record.put(metaData.getColumnLabel(col), rs.getString(col));
            results.add(record);
        }
    }
    return results;
}

public static void printMap(List<Map<String, String>> rows) {
    for (int rowNum = 0; rowNum < rows.size(); rowNum++)
        System.out.println(rowNum + " = " + rows.get(rowNum));
}

You can now access it like you did in PHP:

// PHP (for reference, the way you requested)
$result[0]['Date']

// Java
result.get(0).get("Date")

// Groovy
result[0]['Date']
result[0].Date

// JSP
<c:forEach var="row" items="${result}" varStatus="rowStatus">
  ${rowStatus.index} = <c:out value="${row.Date}"/>, ...
</c:forEach>

edited Oct 7, 2015 at 23:21

answered Oct 7, 2015 at 23:12

Andreas

160k13 gold badges164 silver badges262 bronze badges

3 Comments

Rhododendron Over a year ago

Hey @Andreas in the printMap, how would you be able to print rows.get(rowNum) then?

Andreas Over a year ago

@Rhododendron Print how? What output would you be expecting? Anyway, how to print a Map is a totally different question, so I suggest you create a new one.

Andreas Over a year ago

@Rhododendron The value of rows.get(rowNum) is a Map. What you see is the toString() output of the map.

Collectives™ on Stack Overflow

Storing Multiple Rows from MySQL query in MultiDimensional HashMap

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related