Win a copy of Cross-Platform Desktop Applications: Using Node, Electron, and NW.js this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

displaying next record in DB using ResultSet  RSS feed

 
Joseph Rudman
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Author and ninkuma
Marshal
Posts: 66142
141
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 116
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

 
Bear Bibeault
Author and ninkuma
Marshal
Posts: 66142
141
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Author and ninkuma
Marshal
Posts: 66142
141
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi,

you can do this

[ March 28, 2006: Message edited by: srilatha reddy ]
 
Bear Bibeault
Author and ninkuma
Marshal
Posts: 66142
141
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
<%!
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
Author and ninkuma
Marshal
Posts: 66142
141
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you are not going to answers the questions I ask in my replies, I cannot help you.
 
srilatha kareddy
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i did not think about thread safety...sorry..
can you suggest where we can add the above code
 
Martin Simons
Ranch Hand
Posts: 196
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 196
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!