displaying 100 rows per page.

[aliya sharma]

Hello,
I have a controller servlet that gets the resultset from Javabean and sends it to JSP to display it. JSP hangs if the result is too big (which is expected), So, im trying to show 100 rows per page. So, my Jsp will have dynamic pages depending on the amount of rows(if its 250, it will have 3 pages etc), i have achieved this. The problem is i don't know how to send only partial data per page. For example : if my query returns 250 results. My JSP wil have 1, 2, 3 on top for number of pages(this is done). 1 will display 1-100 and second one 101-200 and third one 201-250. I don't know how to achieve this. I have searched this forum, JSP and JDBC forum for the answer but haven't found anything concrete. Could anyone please guide me how to do this.
I'm using SQLSERVER 2000 as a database. I would really appreciate any help. Again, i have searched all the forums for this topic and read that as well and am still unclear. THanks for your time.

Aliya

[Anupreet Arora]

Hi,

To give U a brief idea of how such a thing is implemented typically :
U need to put each record in a value object ( just a simple java bean). And as U keep on creating java bean objects of your record type, keep adding them to an arraylist. This arraylist is going to be in your http session.
So all you need to do in your jsp by default is to show the first 100 objects of that arraylist. There should be a next button on the jsp on click of which you will show 101 to 200 records and so on !!
How will U keep track of records index ?
Just keep two more variables in your session. startIndex and endIndex. The default values will be 1 and 100 respectively. So the JSP will refer to these variable to determine the which records from the arraylist to show. On click of Next add 100 to both the session variables. So the JSP should show the next hundred records.

Hope this helps...
Cheers
Anupreet

[Stan James]

Here are some notes on prior conversations about paging:

I. Repeat the query for each new request.
   Algorithm
      Client requests page 1
      Execute the query
      Return rows for page 1
      Client requests page 2
      Execute the query
      Skip rows for page 1
      Return rows for page 2
   Variation
      Most RDBMS vendors support returning a specified range of row numbers.
      Efficiency varies. May not help much when combined with "order by"
   Pro/Con
      Pro: Stateless. Nothing is cached between requests.
      Pro: Simple. Every request is the same.
      Pro: Up to date with any inserts or deletes in the database.
      Con: Database and time intensive. Might be repeating large,
           expensive queries - if users really do ever page fwd.
      Con: Not guaranteed consistent. If other processes are inserting and
           deleting rows, paging fwd or bkwd might skip rows. Fwd then Bkwd
           might not give the same rows.
   Notes   We have used this with mainframe requests where even the nastiest
           queries were fast and the server had no options for storing state.
           We have used row number schemes for client requests, and the highest
           key on a page for page forward only action.
II. Hold the query results on the server
   Algorithm
      Client requests page 1
      Execute query
      Cache complete result set or just PKeys
      Return rows for page 1
      Client requests page 2
      Get rows from cache or get PKeys from cache, rows from database
      Return rows for page 2
   Pros/Cons
      Pro: Does NOT repeat the query
      Pro: Self consistent - fwd then bkwd will give identical results
      Pro: Can share cached results if two users have identical requests
      Con: Big cache in memory or someplace close to it
      Con: Complexity for cache, time-out
      Con: Data may go stale
III. Hold a scrolling cursor on the server
   Algorithm
      Client requests page 1
      Execute query with a cursor
      Fetch & return rows for page 1
      Client requests page 2
      Fetch & return rows for page 2
   Pro/Con
      Pro: Does not repeat the query
      Pro: Very small state - just a connection & cursor
      Pro: Self consistent
      Con: More open connections. Might hold open one db connection
           per user
      Con: What does this do to the db? Doesn't it cache rows not yet
           fetched?
      Con: Complexity for time-out

[aliya sharma]

THanks Anupreet and Stan,
I have solved this problem. Stan, i did read that discussion earlier and that what helped me get started...THanks again.

Aliya

[Stan James]

So don't leave us hangin ... how did you do it?

[ernest fakudze]

Hi guys,

I've also been curious about how this is done for a long time. First time I tried to build such a thing I failed because I did not have my thinking cap on . I needed it for my guestbook at www.ernieboy.com. So I tried again two days ago with a new approach. In fact, I used a method which is similar to yours Anupreet where I store all the records in a session and then scroll through them. This is a jsp that I used to put this thing together and it demonstrates the basics of how it works. You can download the source code from here, it runs as is. Any constructive criticism of the code is welcome so that you can help me be a better developer.

My question. If I had 10000 records in that db, wouldn't this method be a drawback since everyone that asks for my guestbook will have a Vector with 10000 records in it? Doesn't this affect server memory? Of course, the other way to go is to use some sort of a scrollable result set as already mentioned but this also has overhead since we are hitting the databse for every N records we need. At least the data is not stale though like when using the vector in a session. So what is better guys?

[ October 23, 2004: Message edited by: ernest fakudze ]
[ October 23, 2004: Message edited by: ernest fakudze ]

[Anupreet Arora]

Hello Ernest !!

If I had 10000 records in that db, wouldn't this method be a drawback since everyone that asks for my guestbook will have a Vector with 10000 records in it? Doesn't this affect server memory?

U are quite right ..but a typical case where such applications are required is when a user searches for some records. So I will design my application in such a manner that the user is kind of forced to give search criteria which restircts the records returned to a more managable number...
Practically, its not feasible for a user to browse 10000 records...so u will have a search screen where the user will give some primary search criterion...and ur database query will have the where clauses built out of those and hopefully the search will return a smaller result set ... to the eventual goal of less of memory consumption in the session or server and also the ease of browsing the resultset ..

Hope this makes sense...

I haven't explored the approach II Stan mentioned in his alogorithms ...that involves caching the result set.. can anyone give me a lead on how to implement such a thing...any links etc..

[aliya sharma]

Hello,

I used the first approach mentioned in Stan's post.

 ][B]I. Repeat the query for each new request.   Algorithm      Client requests page 1      Execute the query      Return rows for page 1      Client requests page 2      Execute the query      Skip rows for page 1      Return rows for page 2[/B] 

I could records upto 60,000 with no difficulty and within no time.

Aliya