12

I have created a Database Application using Netbeans, GlassFish and JavaDB. Now my controller Servlet code executes some dynamic SQL queries and get back a Result Set (or I can ahange toString). Now, how can I show the returned Result Set in a tabular format ( I have no idea about structure of result set). Can anybody help me about this ?

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
alessandro
  • 1,681
  • 10
  • 33
  • 54
  • what problem are you having exactly? gettings the rows from the result set? generating a HTML table? show us the code you have and tell us where you're stuck. – JB Nizet Feb 08 '12 at 21:04
  • @JB Nizet, I have a code like, - createStatement(); resultset rs = st.executeQuery(sql query). Now I got stuck here. How to show this result set in a HTML table. As I have no idea about query, I have no idea about result set structure. – alessandro Feb 08 '12 at 21:10
  • @ BalusC, I have seen your reffered page. But,to apply product.setId(resultSet.getLong("id")) - this I have to know how many colums and what table the statement is returnning. I have no idea of that. – alessandro Feb 08 '12 at 21:15
  • I posted an answer. Note, to reply others in comments, please do not put a space after the `@`. I did not see your comment reply. – BalusC Feb 08 '12 at 21:43

2 Answers2

40

You can use Map<String, Object> to represent a "dynamic" row, which is iterable in <c:forEach>. You can use ResultSetMetaData to collect information about the columns such as the column count and the column labels.

So, this mapping should do:

List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();

while (resultSet.next()) {
    Map<String, Object> columns = new LinkedHashMap<String, Object>();

    for (int i = 1; i <= columnCount; i++) {
        columns.put(metaData.getColumnLabel(i), resultSet.getObject(i));
    }

    rows.add(columns);
}

You can display it in JSP as follows:

<table>
  <thead>
    <tr>
      <c:forEach items="${rows[0]}" var="column">
        <td><c:out value="${column.key}" /></td>
      </c:forEach>
    </tr>
  </thead>
  <tbody>
    <c:forEach items="${rows}" var="columns">
      <tr>
        <c:forEach items="${columns}" var="column">
          <td><c:out value="${column.value}" /></td>
        </c:forEach>
      </tr>
    </c:forEach>
  </tbody>
</table>
Community
  • 1
  • 1
BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
  • Thanks. However, in JSP page I can't see the result set. What I did is as follows: request.setAttribute("als",rows) in my servlet and in JSP, rows = session.getAttribute("als"); - Is anything missing ? – alessandro Feb 09 '12 at 17:29
  • Just use `${als}`. Don't write Java code in JSP file. Your mistake is by the way that you tried to get it as a session attribtue instead of as a request attribute. Read on http://stackoverflow.com/questions/3177733/how-to-avoid-java-code-in-jsp-files and http://stackoverflow.com/questions/3106452/how-do-servlets-work-instantiation-session-variables-and-multithreading – BalusC Feb 09 '12 at 17:45
  • Sorry but I add Object rows = request.getAttribute("${als}"). however it showing rows variable not used. And problem remains same. – alessandro Feb 09 '12 at 17:52
  • That's a warning, not an error. You seem to be not using the *scriptlet* variable anywhere at all. Just remove those nonsensicial *scriptlet* `<% ... %>` lines altogether and use `${als}` instead of `${rows}` as in ``, as said in previous comment (and please take some time to read the given links as well before you make more unnecessary mistakes in the future). – BalusC Feb 09 '12 at 17:55
3

Use the resultset metadata to know the number of columns returned by the query, the type of these columns, etc.

See the javadoc, which also has an example.

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255