Displaying ResultSet in JSP/GSP

Question

Does anyone know where to find documentation on displaying ResultSet results in a JSP/GSP page?

The results are in a GroovyRowResult object being returned to the GSP.

Here is how I'm displaying it now.

<g:if test="${results.size() > 0}">
<table>
    <g:each in="${results}" var="GroovyRowResult" status="index">
        <g:set var="result" value="${GroovyRowResult}" />
        <g:if test="${index == 0}">
            <tr>
                <g:each in="${result.keySet()}" var="key" status="key_index">
                    <g:if test="${key_index == 0}">
                        <th id="t${key_index}" class="staticTHcol">${key.trim()}</th>
                    </g:if>
                    <g:else>
                        <th id="t${key_index}">${key.trim()}</th>
                    </g:else>
                </g:each>
            </tr>
        </g:if>
        <tr>
        <g:each in="${result.values()}" var="value" status="index2">
            <g:if test="${index2 == 0}">
                <td headers="t${index2}" class="staticTDcol">${value}</td>
                <g:set var="record_id" value="${value}" />
            </g:if>
            <g:else>
                <td headers="t${index2}" class="nonStaticTDcol">
                    <g:textField name="${record_id}" value="${value}" onfocus="setDefaultValue('${value}');" onblur="updateValue(this,'${record_id}')" />
                </td>
            </g:else>
        </g:each>
        </tr>
    </g:each>
</table>

No Results

I am not using any ORM technologies, only pure JDBC. I have the basics down (limiting rows returned, pagination and basic layout of the results). Now I need to get into more advanced features like sorting and so forth.

I'm using the latest version of Grails and wonder if there is a plugin that might be able to get me some of this functionality. Or maybe someone knows of a good JS extension or library that might be able to work for this.

No idea about Grails/GSP, but for JSP/Servlet the general idea can be found [here](http://stackoverflow.com/questions/384189/how-do-i-make-a-java-resultset-available-in-my-jsp) (plain JSP/Servlet) and [here](http://stackoverflow.com/questions/4407861/how-to-send-a-resultset-object-in-jsp-back-to-html-javascript) (with help of JS/jQuery). It may lead to new insights. Note that you'd like to do paging and sorting at DB level rather than at Java level. Again, see [here](http://stackoverflow.com/questions/1986998/resultset-to-pagination) for the general idea. — BalusC, Feb 01 '11 at 02:38

score 3 · Answer 1 · answered Feb 01 '11 at 02:42

3

I don't think a ResultSet should get anywhere near a JSP/GSP. It should never emerge from your persistence tier.

A ResultSet is a database cursor - a scarce resource. Responsibility for closing it should remain inside the method that created it.

The right way to do it is to map the ResultSet into an object or collection, close it in scope, and then pass the object or collection to the JSP/GSP to sort/paginate/display as you see fit.

answered Feb 01 '11 at 02:42

duffymo

305,152
44
369
561

Sorry I should have been more clear. The results are sent to the view in a List of GroovyRowResult objects. These are pretty much a collection of Maps with a KVP association. I guess the question should have been ask about displaying the results of a ResultSet object, in whatever converted form (i.e. List, Collection, Set ...) on a JSP/GSP page. I'm looking more into the control of that data on the view once I have it. But I do completely agree with your view of separating the persistence layer from the view. Sorry to lead you away from my true issue. – jonsinfinity Feb 01 '11 at 15:17

score 0 · Answer 2 · answered Nov 20 '12 at 05:06

<table  class="main_grid_table" width="100%">
<thead>
<h3>Number of items found are ${numberofrows}</h3>
<th class="main_grid_column"><b>Factory Id</b></th>
<th class="main_grid_column"><b>Applicant Name</b></th>
<th class="main_grid_column"><b>Designation</b></th>
<th class="main_grid_column"><b>Factory_name</b></th>
<th class="main_grid_column"><b>Situation of Dist</b></th>
<th class="main_grid_column"><b>Police Station</b></th>
</thead>
<% while(resultset.next())
{%>
<tr>
<td align="center" class="main_grid_column"><a href="printAmmendmendFormOne.htm?facid=<%=resultset.getString("Fac_id")%>"><%=resultset.getString("Fac_id")%></a></td>
<td align="center" class="main_grid_column"><%=resultset.getString("Appname")%></td>
<td align="center" class="main_grid_column"><%=resultset.getString("Designation")%></td>
<td align="center" class="main_grid_column"><%=resultset.getString("Factory_name")%></td>
<td align="center" class="main_grid_column"><%=resultset.getString("Situation_f_district")%></td>
<td align="center" class="main_grid_column"><%=resultset.getString("nearest_police_station")%></td>
</tr>
<%}%>
<tbody>
</tbody>
</table>

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

0

Totally agree with duffymo regarding the design of your solution. In Grails you could encapsulate your data access in a Service class and then wrap your presentation in a taglib which can use the Service class to retrieve the data.

If you are using straight SQL via JDBC then do your sorting (ordering in SQL). Add a little Grooviness to your SQL by using GSQL, the docs are a good starting place for the background of using Groovy SQL.

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 01 '11 at 09:31

Paul

2,543
3
26
36

Thanks Paul for the reply. I've responded to duffymo and clarified some issues. I am using Groovy SQL to retrieve my results. The only issue that I have with doing my sorting via SQL is that I would have to make repeated calls to the DB. I'm trying to avoid that. O'm pretty much looking for a solution to be able to control the data that I have in the current GSP page once it's there. Currently I'm putting the data into a table structure and am wondering if there are any solutions probably via JavaScript or something that can control the sorting of the table once it has been created. – jonsinfinity Feb 01 '11 at 15:26

score 0 · Answer 4 · answered Sep 27 '11 at 22:19

Question was about some way to sort data on JSP, suggesting some sort of JS.

So... here it is: http://code.google.com/apis/ajax/playground/#motion_chart and other google charts and tables - this things are realy cute, and of course, sortable.

To provide data to them you just need to pass list / array / JSON to JSP and use some loop inside JS provided as a sample.

Displaying ResultSet in JSP/GSP

4 Answers4