wood burning stoves 2.0*
The moose likes Servlets and the fly likes Paging through a db Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "Paging through a db" Watch "Paging through a db" New topic

Paging through a db

Mark Leong
Ranch Hand

Joined: Dec 11, 2000
Posts: 60
Can somebody show me how to page through a db (that is, retrieving x number of rows each time - eg. row 8 to 16 and then 17 to 30, etc...).
Frank Carver

Joined: Jan 07, 1999
Posts: 6920
The answer depends a lot on things like which database are you using? Is your result set very large? Do you bring back a lot of information with each row? Is the SQL query "expensive"?
For "expensive" (slow) queries with small result sets, the best answer is usually to keep a note of all the results (in a session is a good place) and give back the appropriate section of the result set as each page is requested.
For "cheap" (fast) queries with large result sets it's better to let the database sort it out and re-run slightly different SQL for each page to only retrieve the rows you need for that page.
If your application is not at eithert of these two extremes you will need to make some sort of compromise - maybe ask the database for enough answers for (say) three or four pages at once, and hope the reader never goes beyond those pages.

Read about me at frankcarver.me ~ Raspberry Alpha Omega ~ Frank's Punchbarrel Blog
Mark Leong
Ranch Hand

Joined: Dec 11, 2000
Posts: 60
Thank you for reply.
maha anna
Ranch Hand

Joined: Jan 31, 2000
Posts: 1467
If the database contents change dynamically and frequently, then for each page if we re-execute the SQL query, the contents may be different. Isn't? The first 10 records of the resultset got previously , may not be the same ones when we execute the same SQL query next time.
For example there is a very HUGE items database whose contents are added/deleted/updated dynamically by many users who are authorized to do those tasks.
Assume I am next level person, who just make a search with some criteria and want to see what are the available items which matche my search. We assume when I make a search the database contains 100 items which match my query and each page diplays 10 items and there is a navaigation link Page1, Page2, Page3...Page10 at the first page displayed to user.
Also we assume we can't keep the whole recordSet in session due to its huge size. So if we re-execute the same SQL query with same search criteria , when the user clicks on Page2, due to the dynamic contents of the databse, this time the page2 contents MAY NOT be same as what was expected at the time the user first made the SQL query.
Have you encounterd this situation?
It is like Clicking on 'Check Mail' at Yahoo.com and we get Prev, Next links. When we click on Prev and next the mails which were available the time I clicked on 'Check Mail' only browsed through. If I want to see if there is any new mail came , I have to click on 'Check Mail' again.
I think we have to store the mailIds (the unique Item ids) atleast in session and each page link should remember the ItemIds (mailIds) they are supposed to display and when each time a 'Page(n)' is asked we just make a quick sql search to pull only those items which supposed to make up the page according to the user's first time SQL QUERY.
Any suggestions, ideas Frank, others.
maha anna

[This message has been edited by maha anna (edited December 12, 2000).]
Don't get me started about those stupid light bulbs.
subject: Paging through a db