Retrieving values column wise from database using jdbc

Question

I want to retrieve data from database column wise. Say if I have Column 'a' then I want all values of that column and then I have another column say 'b' then I want to get all values of that column and so on for all columns.

My code is giving me row wise value. First it will retrieve data from first row and then second and so on. My code is as follows.

while (rs.next()) {
 for(inti=1;i<=rsMetaData.getColumnCount();i++) {
 System.out.println(rs.getString(i)); 
}
 }

Please suggest how to get all values of first column then second and so on.

instead of printing append each value of each column to a List — Scary Wombat
– Scary Wombat, Commented Sep 21, 2017 at 5:24
@slippsryseal while Iterating over result set I am not getting any option to iterate over column 1 then column 2 and so on every time it's giving row wise results — user8646082
– user8646082, Commented Sep 21, 2017 at 5:27
@scary it would be very helpful if you can suggest an example code — user8646082
– user8646082, Commented Sep 21, 2017 at 5:28

Bharat Karamur · Accepted Answer · 2017-09-21 06:07:54Z

Iterate Data Row wise and convert to Column wise. Something Like this :

        ResultSet rs = con.createStatement().executeQuery("SELECT * FROM table");
        ResultSetMetaData rsMetaData = rs.getMetaData();
        Map<String, List<Object>> resultMap = new LinkedHashMap<>();
        while (rs.next()) {
            for(int i=1;i<=rsMetaData.getColumnCount();i++) {
                String strColumnName = rsMetaData.getColumnName(i);
                Object columnValue = rs.getObject(i);
                if(resultMap.containsKey(strColumnName)){
                    resultMap.get(strColumnName).add(columnValue);
                }else{
                    List<Object> resultList = new ArrayList<>();
                    resultList.add(columnValue);
                    resultMap.put(strColumnName,resultList);
                }
            }
        }
        // Iterate Data Column Wise
        for(Iterator<Map.Entry<String, List<Object>>> iterator = resultMap.entrySet().iterator();iterator.hasNext();){
            Map.Entry<String, List<Object>> entry = iterator.next();
            System.out.println("Column Name: "+entry.getKey());
            System.out.println("Column Values: "+entry.getValue());
        }

dsp_user · Accepted Answer · 2017-09-22 08:37:56Z

1

A more flexible solution might use a HashMap instead of a List. This way you can store the column names as keys, which makes retrieving values easier and more transparent.

//declare a HashMap

Map<String,Object> hMap = new HashMap<String,Object>();

    public void populateColumns(ResultSet rs) { 
            try{  
                    while(rs.next()){           
            for (int i=1;i<=rs.getMetaData().getColumnCount();i++) { 
                    Object obj=rs.getObject(i); //get the value for whatever column the result has   
                    hMap.put(rs.getMetaData().getColumnName(i), obj); 
                } }
            }catch (Exception e) { 
            //handle the exception
            } 
        }

Note that if you want special handling for some database types /Java types, you can use the instanceof operator.

for (int i=1;i<=rs.getMetaData().getColumnCount();i++) { 
            Object obj=rs.getObject(i); /
            if(obj instanceof String) //if obj is a String
                hMap.put(rs.getMetaData().getColumnName(i), new String(((String)obj).getBytes("UTF-8"),"UTF-8"));
            else //for every other objects
                hMap.put(rs.getMetaData().getColumnName(i), obj);

UPDATE:

To get the output like you want try this

public void populateColumns(ResultSet rs) {
            List<Object> list = new ArrayList<Object>(); 
            int colCount;
            try{   
            while(rs.next()){
            colCount =  rs.getMetaData().getColumnCount();      
            for (int i=1;i<=colCount;i++) { 
                    Object obj=rs.getObject(i); //get the value for whatever column the result has   
                    list.add(obj); 
                } 
            }
            }catch (Exception e) { 
            //handle the exception
            } 

            //
            List<Object> sortedByCol = new ArrayList<Object>();

            for(int j=0; j<colCount;j++){
            for(int i=j; i<list.size(); i+=colCount){

            sortedByCol.add(list.get(i));
            }

        }

For input like

     col a    col b   col c

1 row   1         2       3
2 row   1         2       3

your output list (sortedByCol) will have

1, 1, 2, 2, 3, 3

edited Sep 22, 2017 at 8:37

answered Sep 21, 2017 at 6:24

dsp_user

2,1332 gold badges18 silver badges27 bronze badges

3 Comments

user8646082 Over a year ago

Sir I tried this it is giving only some wise values

dsp_user Over a year ago

What do mean by wise values? Update your original post.

user8646082 Over a year ago

Sorry my mistake.. It is giving only row wise values means like this suppose I have three columns a, b and c and their values for first and second rows are as follows 1,2,3 and 1,2,3.. I want first values of column a that is 1,1 and then for second column 2,2 and so on..but with result set I am getting only row wise that is 1,2,3 and 1,2,3

Scary Wombat · Accepted Answer · 2017-09-21 05:35:34Z

-1

have comma delimitered String value for each column which is in an ArraList

Something like (Sorry not in front of a computer)

ArrayList<String> columnValList = new ArrayList<>();
while (rs.next()) {
   for(inti=0;i<rsMetaData.getColumnCount();i++) {
       // get element for this col - create if doesnt exist
       String cols = columnValList.get (i);   // error checking needed
       cols = cols + "," + rs.getString (i + 1);
       // put it back
       columnValList.set (i, cols);
   }
}

after this you will have element(0) containing all values for the first columns etc

answered Sep 21, 2017 at 5:35

Scary Wombat

44.9k6 gold badges38 silver badges67 bronze badges

Collectives™ on Stack Overflow

Retrieving values column wise from database using jdbc

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related