Build map with column name and column values from java.sql.ResultSet

Question

I have the following snippet:

    Connection connection = getConnection(schemaName);
    Statement stmt = connection.createStatement();
    String sql = "SELECT * FROM " + tableName;
    ResultSet rs = stmt.executeQuery(sql);

now what I want to achieve is building a

Map<String , List<String>>

where key is the columnName and value is list of columnValues. Here is my code:

    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();

    List<String> columnNames = new LinkedList<>();
    for (int i = 1; i <= columnCount; i++) {
        columnNames.add(rsmd.getColumnName(i));
    }

    Map<String, List<String>> columnNameToValuesMap = new HashMap<String, List<String>>();

    for (String columnName : columnNames) {
        List<String> values = new ArrayList<>();
        try {
            while (rs.next()) {
                values.add(rs.getString(columnName));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        columnNameToValuesMap.put(columnName, values);
    }

The issue is that after the first columnName iteration, rs.next() is empty. So basically The result map contains the values for only the first column. How can I reuse the same resultSet for each iteration ? Why its empty after the first one ?

Why do you want to do this? It looks like you're reinventing parallel arrays for no particularly good reason. — chrylis -cautiouslyoptimistic-
– chrylis -cautiouslyoptimistic-, Commented Nov 19, 2016 at 19:38
Not related to your problem, but you should be using getColumnLabel instead of getColumnName — Mark Rotteveel
– Mark Rotteveel, Commented Nov 20, 2016 at 10:15

Vasu · Accepted Answer · 2016-11-19 19:45:35Z

2

You can follow the below steps:

(1) Initialise the columnNameToValuesMap with empty list objects with column name as key

(2) Iterate the resultset using rs.next()

(3) Get each column data using a for loop and add to list values object

(4) Add the list of columns data to columnNameToValuesMap object

You can refer the below code with comments:

List<String> columnNames = new LinkedList<>();
Map<String,List<String>> columnNameToValuesMap=new HashMap<String, List<String>>();

 for (int i = 1; i <= columnCount; i++) {
        String columnName = rsmd.getColumnName(i);
        columnNames.add(columnName);

        //Load the Map initially with keys(columnnames) and empty list
        columnNameToValuesMap.put(columnName, new ArrayList());
 }

 try {
    while (rs.next()) { //Iterate the resultset for each row

      for (String columnName : columnNames) {
         //Get the list mapped to column name
         List<String> columnDataList = columnNameToValuesMap.get(columnName);

          //Add the current row's column data to list
          columnDataList.add(rs.getString(columnName));

          //add the updated list of column data to the map now
          columnNameToValuesMap.put(columnName, columnDataList);
        }
     }
 } catch (SQLException e) {
    e.printStackTrace();
}

edited Nov 19, 2016 at 19:45

answered Nov 19, 2016 at 19:25

Vasu

22.6k11 gold badges54 silver badges71 bronze badges

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

2 Comments

Alexander Nikolov Over a year ago

You put in the map outside of the columnNames loop..columnName is out of scope there.

Vasu Over a year ago

Updated the code now, you can have a look, basically you need multiple list objects each mapped to the column name

Collectives™ on Stack Overflow

Build map with column name and column values from java.sql.ResultSet

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related