How to map a ResultSet with unknown amount of columns to a List and display it in a HTML table?
Solution 1
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>
Solution 2
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.
alessandro
Updated on July 09, 2022Comments
-
alessandro almost 2 years
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 ?
-
alessandro about 12 yearsThanks. 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 ?
-
BalusC about 12 yearsJust 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 stackoverflow.com/questions/3177733/… and stackoverflow.com/questions/3106452/… -
alessandro about 12 yearsSorry but I add Object rows = request.getAttribute("${als}"). however it showing rows variable not used. And problem remains same.
-
BalusC about 12 yearsThat'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<c:forEach items="${als}">
, 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).