displaying next record in DB using ResultSet

[Joseph Rudman]

I'm trying to display one record at a time in jsp using resultset but I'm finding this very hard to do.
I gain access to a connection generated in servlet and use this to access database as follows:
<FORM method="POST" action="/ProcessClients.jsp">
<%
Statement stmtClients;
ResultSet rsClients;
Connection connection = (Connection) session.getAttribute("connection");
String queryClients = "SELECT * FROM clients";
stmtClients = connection.createStatement();
rsClients = stmtClients.executeQuery(queryClients);
ResultSetMetaData rsmdClients = rsClients.getMetaData();


if( rsClients.next()){
System.out.println(rsClients.getString("clientID"));

%>

<TABLE border='1' bordercolor='#rrggbb' cellpadding='2' cellspacing='2'
width='100%'>
<TR>
<TD align='right'>Client ID</TD><TD align='left'><INPUT type='text' name='clientID' value='<%= rsClients.getString("clientID") %>'></TD>

<%
}
%>
<TR>
<TD ALIGN=CENTER><INPUT TYPE="SUBMIT" VALUE="Next" STYLE="font-family:sans-serif;font-size:small;
font-style:italic; background:FF9933 none; color:#000; width:4em"></TD>
</TR>
</TABLE>

But how do I the same jsp to display the next record in database in jsp when next button is selected, I'm quite new to this and I'm not getting very far so any help would be appreciated....

Cheers Joe

[Bear Bibeault]

Well, you're going about it a bit backwards. If you want to show only one record at a time, the best method would be to only fetch one record at a time.

Grabbing and holding onto a result set is a very poor idea. DB resources should be released as soon as possible.

[vishwanath nadimpally]

One way to do this would be to read the result set in to an ArrayList in a while loop and have a counter to display the record. Everytime you hit next be sure to update your counter. Remember you need a static counter ...

Something like this

 
  
ArrayList al = new ArrayList();
while (rs.next()) {
al.add(rsClients.getString("clientID"));
}
 
<TD align='right'>Client ID</TD><TD align='left'><INPUT type='text' name='clientID' value='<%= al.get(counter)%>'></TD>

[Bear Bibeault]

That doesn't make much sense. Retrieveing the list every time the JSP is displayed defeats the entire purpose of having a list in the first place.

[Joseph Rudman]

The problem is I cannot work out how to just store one record.
user asks to see next record.
jsp is redisplayed showing next record.

I can output everything but and the first record only, but not what I want to do...

Cheers Joe.

[Bear Bibeault]

To get more detailed requires more info on your data set. For example, given a single record, is it possible to know what the "next" record is? The previous?

If not, when you are fetching a record, is it possible to know the primary key of next/previous at that time?

[Joseph Rudman]

The jsp just needs to display the first record when first viewing the jsp, the second record from mysql database when viewing again and so on until there are no more records to show.

[srilatha kareddy]

hi,

you can do this

 
<%!
int rowid=0;
int rowStart=0;
int rowEnd=50;
%>           ... when jsp is first loaded
 
<% 
if(rowid==rowStart) ...get 50 records from database 
{
"SELECT * FROM clients where rowid between rowStart and rowEnd"
store these records in ArrayList
}
else
{
      if(rowid >rowEnd)
      {
      rowStart=rowStart+50 ;rowEnd=rowEnd+100;
      }
rowid++;
}
 
 

 
%> 
 
 

[ March 28, 2006: Message edited by: srilatha reddy ]

[Bear Bibeault]

<%!
int rowid=0;
int rowStart=0;
int rowEnd=50;
%>

NO! Do not do that! Never do that!

What this will do is make your JSP non-thread safe and when multiple users hit the page, they will over-write each others values.

Never, ever put variables in a declaration block.

[Bear Bibeault]

If you are not going to answers the questions I ask in my replies, I cannot help you.

[srilatha kareddy]

i did not think about thread safety...sorry..
can you suggest where we can add the above code

[Martin Simons]

Get the results and put them in a list, then store both the list and
a counter variable as session objects. When the JSP is hit, check to
see if these session objects exist, if so use them, incrementing or
decrementing the counter, if not perform the query and create the objects.

[Martin Simons]

Unfortunately, of course, this means that changes to the database will
not be seen during this session unless you allow some sort of "reset"
function for the user. Whether this is truely a problem depends largely
on the fluctuations of your data. If it is data that is only reteived
modified on a incremental basis (i.e. every couple of hours or so) no
problem. If it is info that other users can change using an application,
or that is continually updated from other sources, than this will not
be acceptable.

[Joseph Rudman]

Sorry I did answer questions:
To get more detailed requires more info on your data set. For example, given a single record, is it possible to know what the "next" record is? The previous?

the data stored in database will have an ID and you could use this to determine which is the next and previous records

I tried to use the following which allowed me to view one record at a time and when I hit the next button I alway viewed the next record:
if(request.getParameter("clientID") != null){
while(rsClients.next() && !rsClients.getString("clientID").equals(request.getParameter("clientID").toString()));
}//loops through the rs and when it finds the clientID from the previous page, it stops.
if( rsClients.next()){

But using this I could not go to the previous record.

Cheers Joe