Resultset To List

Question

I want to convert my Resultset to List in my JSP page. and want to display all the values. This is my query:

SELECT userId, userName 
  FROM user;

I have executed that using preparedstatement and got the Resultset. But how to convert it as a List and want to display the result like this:

userID  userName
------------------
1001    user-X 
1006    user-Y  
1007    user-Z

Answer 1

A ResultSet should never get as far as a JSP. It should be mapping into a data structure or object and closed inside the method scope in which it was created. It's a database cursor, a scarce resource. Your app will run out of them soon if you persist with such a design.

Answer 2

You could always use Commons DbUtils and the MapListHandler. From the doc:

ResultSetHandler implementation that converts a ResultSet into a List of Maps

so it'll take a lot of boilerplate code out of your hands.

Answer 3

        var rs = stmt.executeQuery();
        List<Map<String, Object>> result = new ArrayList<>();

        while (rs.next()) {
            Map<String, Object> resMap = new HashMap<>();
            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                resMap.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
            }
            result.add(resMap);
        }

Answer 4

You can make a list of lists.

   ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
   ResultSetMetaData rsmd = rs.getMetaData();
   int numberOfColumns = rsmd.getColumnCount();

   List row = null;
   List table = new List();

   while(rs.next())
   {
       for(int i = 0; i < n; i++)
          row.add(rs.next(i);

       tabla.add(row)
   }