looping through a recordset and displaying results

[welshone]

hello all, I am new to java and am trying to do a simple thing.

I can connect to a database and display the first row in a jsp page, but I need to display all rows.
so far I have this .

// Create a Statement Object
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(queryString);
int numCols = rs.getMetaData().getColumnCount();

while (rs.next()) {

for (int i=1; i<=numCols; i++)
{
String RS_Name = rs.getString(&quot;name&quot;
String RS_Postcode = rs.getString(&quot;Postcode&quot;
String RS_County = rs.getString(&quot;County&quot;

ServletContext context = getServletContext();
req.setAttribute(&quot;RS_Name&quot;, RS_Name);
req.setAttribute(&quot;RS_Postcode&quot;, RS_Postcode);
req.setAttribute(&quot;RS_County&quot;, RS_County);
context.getRequestDispatcher(&quot;Result.jsp&quot.forward(req, resp);
}

and my jsp page has the following :
<%


String name = (String) request.getAttribute(&quot;RS_Name&quot;
String postcode = (String) request.getAttribute(&quot;RS_Postcode&quot;
String county = (String) request.getAttribute(&quot;RS_County&quot;

out.println(name + &quot; &quot; + postcode + &quot; &quot; + county);

%>


how can I display all data ? do I need to create the data as a vector or something ?

thank you very much for your help.

 

[welshone]

one problem I've just spotted, I'm trying to loop through the column count. how can I get a recordset count ?

 

[sedj]

>>>>> one problem I've just spotted, I'm trying to loop through the column count. how can I get a recordset count ?

Yoo don't need to as you call

while (rs.next()) {

.

Your main problem is that as you loop the ResultSet, you set request scope parameters thus :

req.setAttribute("RS_Name", RS_Name);
req.setAttribute("RS_Postcode", RS_Postcode);
req.setAttribute("RS_County", RS_County);

What this is doing is setting your SAME request attributes on every loop - so when you display them, you will only ever get to see the last one.

The easiest way to get around this (if you continue down the scriptlet path) - is to print out the ResultSet values as you loop the result set - rather than save them as request attributes.

 

[welshone]

chees for the info sedj, but how do I do that ?

 

[welshone]

ok,
I've finally managed to do this but its really ugly.
I am creating the recordset like:

while(rs.next()) {
RS_Name += rs.getString(&quot;name&quot + &quot;|&quot;;
RS_Postcode += rs.getString(&quot;Postcode&quot + &quot;|&quot;;
RS_County += rs.getString(&quot;County&quot + &quot;|&quot;;
}

then in my JSP page I am using StringTokenizer to split the values and diaply in a table.

does anybody know of a 'nicer' way of doing this ?

thanks for all the help guys.

 

[sedj]

Hi,

The problem you are seeing is because you are trying to split business logic (ie data from a database, and how to deal with it) with presentation (how to display it to a user) - this is because you are using scriptlets within HTML code ... try looking into tag libraries and using JavaBeans to try to seperate business logic from presentation.

Anyway, as I said earlier, if you want to do the job witth scriptlets, then when you want to display you ResultSet data, then loop it, with a println - ie

<table>
<%
while (rs.next()) {
  out.println(&quot;<tr><td>&quot; +rs.getString(&quot;Postcode&quot;) +&quot;</td>&quot;);
  out.println(&quot;<td>&quot; +rs.getString(&quot;County&quot;) +&quot;</td></tr>&quot;);

}
%>
</table>

 

[welshone]

Hello Sedj,

all of my code is not is scriplets, I have a class which the search jsp calls , passes parameters, runs sql, then opens the results jsp to display the results.

I am looking into using beans, or more classes to sort he data so its easier to retrieve.
but Im having problems displaying more than one record at a time.
I know I need to use arrays or something but don't really know how to.

when looping through my recordset I want to set up the bean.

while(rs.next()) {

RS_Name = rs.getString(&quot;name&quot;
RS_Postcode = rs.getString(&quot;Postcode&quot;
RS_County = rs.getString(&quot;County&quot;
RS_Telephone = rs.getString(&quot;Telephone&quot;


EstablishmentResultBean theBean = new EstablishmentResultBean(RS_Name, RS_Postcode, RS_County, RS_Telephone);

but this only puts one value into the bean.
what would the array code look like to deal with all rows ?

thanks again.

 

[sedj]

see your post &quot;ArrayLists&quot; - Thread269-649629