Convert Resultset into Map

Question

I have query which will return two values(Column A and Column B) as below

A        B
------------
a   aaa
a   aaa
a   aaa
a   aaa
b   bbb
c   ccc
c   ccc
b   bbb
c   ccc
b   bbb

I am trying to create a java method(Java 7) which will fetch all these value in one go and store it in a collection variable(Map) like all the value in the below format

(a -> (aaa,aaa,aaa,aaa,aaa),
 b -> (bbb,bbb,bbb),
 c -> (ccc,ccc,ccc))

Below is the method I am trying, but I am not even able to fetch all the data in the first place:

import java.sql.*;
import java.util.ArrayList;

public class CollectionFrame {


    public static void main(String[] args) {

        try {
            // step1 load the driver class
            Class.forName("oracle.jdbc.driver.OracleDriver");

            // step2 create the connection object
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr");

            // step3 create the statement object
            Statement stmt = con.createStatement();

            // step4 execute query

            // Lists of Lists to store the values
            ArrayList<ArrayList<String>> listOLists = new ArrayList<ArrayList<String>>();
            ArrayList<String> obj = new ArrayList<String>();

            ResultSet rs = stmt.executeQuery("select * from t");
            while (rs.next()) {
                // System.out.println(rs.getString(1) + " " + rs.getString(2));
                obj.add(rs.getString(1));
                // obj.add(rs.getString(2));
                listOLists.add(obj);
                obj.removeAll(obj);

            }

            // step5 close the connection object
            con.close();

            System.out.println(listOLists.toString());

        } catch (Exception e) {
            System.out.println(e);
        }

    }

}

the above code gives the below result

[[a, a, a, a, b, c, c, b, c, b], [a, a, a, a, b, c, c, b, c, b], [a, a, a, a, b, c, c, b, c, b], [a, a, a, a, b, c, c, b, c, b], [a, a, a, a, b, c, c, b, c, b], [a, a, a, a, b, c, c, b, c, b], [a, a, a, a, b, c, c, b, c, b], [a, a, a, a, b, c, c, b, c, b], [a, a, a, a, b, c, c, b, c, b], [a, a, a, a, b, c, c, b, c, b]]

If I uncomment the line obj.removeAll(obj); I get the following:

[[], [], [], [], [], [], [], [], [], []]

I am stuck here. Can someone help me how proceed or suggest a better solution?

First to get the correct data, use `obj = new ArrayList()` inside the while loop — Youcef LAIDANI, Mar 24 '19 at 16:26
*"store it in a collection variable(Map)"* If you're trying to store values in a **Map**, how come there is no use of any `Map` in your code? Re-think what you're doing. You need a `Map>` in your code. — Andreas, Mar 24 '19 at 16:27
Second, I don't understand what you want to do exactly with your code? do you want to get all columns and group by column A in Java code? — Youcef LAIDANI, Mar 24 '19 at 16:28
Third, I would create an Object which hold A and B column it can be more easy — Youcef LAIDANI, Mar 24 '19 at 16:29
I am trying to fetch data first like `((a , aaa) (a, aaa) (b, bbb), (c,ccc))`, inside result set , and then convert to Map later. This is what I am thinking — F0cus, Mar 24 '19 at 16:31
@YCF_L OP wants a `Map>` keys by column A, with list of values from column B. No need for an object for that, just: `Map> map = new HashMap<>(); while (rs.next()) { map.computeIfAbsent(rs.getString(1), k -> new ArrayList<>()).add(rs.getString(2)); }` --- It's curious that OP specifically said a Map is desired, but the code doesn't have any map in it. — Andreas, Mar 24 '19 at 16:32

score 3 · Accepted Answer · edited Feb 09 '21 at 18:26

You should use a map for this.

ResultSet rs = stmt.executeQuery("select * from t");
Map<String, List<String>> valueMap = new HashMap<>();

while (rs.next()) {
    String columnAstring = rs.getString(1);
    String columnBstring = rs.getString(2);
    
    valueMap.putIfAbsent(columnAstring, new ArrayList<>());
    valueMap.get(columnAstring).add(columnBstring);
   
}

EDIT: So putifabsent might be quite inefficient since I will be creating and discarding lot of arraylists. as pointed out by @Andreas. So this would be a tiny bit less cleaner but way more efficient way to do it!

Compatible with JAVA 7

ResultSet rs = stmt.executeQuery("select * from t");
Map<String, List<String>> valueMap = new HashMap<>();

while (rs.next()) {
    String columnAstring = rs.getString(1);
    String columnBstring = rs.getString(2);
    
    if(!valueMap.containsKey(columnAstring)){
        valueMap.put(columnAstring, new ArrayList());
    }
    valueMao.get(columnA).add(columnBstring);
   
}

With Java 8 Lambdas

@Mureinik's answer has pointed out an even cleaner way using computeIfAbsent.

while (rs.next()) {
    String columnAstring = rs.getString(1);
    String columnBstring = rs.getString(2);
    
    valueMap.computeIfAbsent(columnAstring, k -> new ArrayList<>())
    valueMap.get(columnAstring).add(columnBstring);
}

Don't use `putIfAbsent`. Use `computeIfAbsent`. Your code is unnecessarily creating and discarding a lot of `ArrayList` objects (only 2 r's, not 3). — Andreas, Mar 24 '19 at 16:35
No need to call `get` after calling `computeIfAbsent`. By just using the return value of `computeIfAbsent` in a chained call, you can save yourself a lookup. — k314159, Mar 15 '22 at 09:44

score 1 · Answer 2 · answered Mar 24 '19 at 16:39

I'd iterate over the ResultSet and apply the changes to a map Map<String, List<String>>. In each iteration, if the key (column A) doesn't exist, you need to add it with an empty list, and then once you're sure you have a list for that key, append the value from column B. Luckily, Java 8's improvements to the Map interface make this quite elegant:

Map<String, List<String>> result = new HashMap<>();
while (rs.next()) {
    String a = rs.getString("a");
    String b = rs.getString("b");

    result.computeIfAbsent(a, k -> new ArrayList<>()).add(b);
}

Unfortunately , I am doing this POC for Java 7 :( , Sorry I didn't mention that in the question. — F0cus, Mar 24 '19 at 16:53

score 1 · Answer 3 · answered Mar 24 '19 at 18:43

Maybe you can try this. This uses HashMap.

public class Main {
public static void main(String[] args) {

    //sample CSV strings...pretend they came from a file
    String[] csvStrings = new String[] {
      "a aaa","a aaa","a aaa","a aaa","b bbb","b bbb","b bbb",
      "b bbb","c ccc","c ccc","c ccc"
    };

    List<List<String>> csvList = new ArrayList<List<String>>();
    Map<String,ArrayList<String>> outVal 
        = new HashMap<String, ArrayList<String>>();

    for(String val:csvStrings){
        String outList[] = val.split(" ");
        if (outVal.containsKey(outList[0])){
            outVal.get(outList[0]).add(outList[1]);
        } else {
            ArrayList<String> inputList = new ArrayList<String>();
            inputList.add(outList[1]);
            outVal.put(outList[0],inputList);
        }
    }

   System.out.println(outVal.toString());  
}

}

Here's the output: {a=[aaa, aaa, aaa, aaa], b=[bbb, bbb, bbb, bbb], c=[ccc, ccc, ccc]}

Convert Resultset into Map

3 Answers3