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

You need to iterate over the ResultSet object in a loop, row by row, to pull out each column value:

ArrayList al = new ArrayList();
ResultSet rs = stmt.executeQuery("SELECT userid, username FROM USER");

// Fetch each row from the result set
while (rs.next()) {
  int i = rs.getInt("userid");
  String str = rs.getString("username");

  //Assuming you have a user object
  User user = new User(i, str);

  al.add(user);
}

For more info on getting data out of a ResultSet object, see this link.

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

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.