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?

Answer 1

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