Styjun

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?

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

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

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]