Win a copy of Secure Financial Transactions with Ansible, Terraform, and OpenSCAP this week in the Cloud/Virtualization forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Rob Spoor
  • Henry Wong
  • Liutauras Vilda
Saloon Keepers:
  • Tim Moores
  • Carey Brown
  • Stephan van Hulst
  • Tim Holloway
  • Piet Souris
Bartenders:
  • Frits Walraven
  • Himai Minh
  • Jj Roberts

PAGINATION

 
Ranch Hand
Posts: 323
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All,
If i have to implement some pagination technique like suppose i want to retrieve 1-10 records or 10-20 records from database then do i have to fire 2 queries , one to know the total no of rows and another to get the required no of data. Any suggestions.
Thanks,
kundan
 
Bartender
Posts: 2968
6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Solutions would depend on the database that you are using and whether or not you can maintain the DB session across pages. Some SQL dialects allow you to specify "SELECT TOP n" or "SET ROWCOUNT". If you can maintain a DB session then you could simply use a server-side cursor or a stored procedure that maintains a cursor/temporary table of the results of the query.
Ideally you would go with stored procedures that simply need the starting position and maximum result set size - that way you could encapsulate the DB details in there and you would would only need a relatively thin application wrapper.
 
Ranch Hand
Posts: 961
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I know that MySQL and PostgreSQL have a limit and offset keyword in their select stataments, Oracle have a ROWCOUNT. SQL Server however, at least in version 2000 just provided a SELECT TOP n, which, by itself, is not sufficient to implement this paging, unless there is way you can restrict you query not to bring the records brought in the last page.
 
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Some options include ...

Repeat the same query every time and pull out the right rows for the page.

Execute a query that gets exactly the right rows for any page - start at the first key > the last one you got before, use row numbers, etc.

Get all the primary keys in the first query, stash them in a giant array in session or some temporary storage, use them to retrieve the proper rows for each page.

Get all the real rows and stash them in storage - probably much bigger! I did this a time or two in my mainframe days - selected all rows from DB2 and stored page-size chunks in fast VSAM files.

Others??

Note that some of these techniques guarantee that page forward followed by page backward will show you exactly the same rows you had before while others can be modified by concurrent inserts or deletes. Some use lots of memory while some use lots of database and CPU time. Some may be faster than others. Now you have to pick one!
 
Ranch Hand
Posts: 547
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If you are using java as primary language, you can go to Jdbc / Jsp / Servlet forums. You will find a lot of pagination topics.

Lastly, just Curiosity, SCEA asks pagination topic.
 
Ranch Hand
Posts: 8944
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Get all the primary keys in the first query, stash them in a giant array in session or some temporary storage, use them to retrieve the proper rows for each page.



Will this scale. I dont think so.
 
Sheriff
Posts: 6920
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There are plenty of other possibilities too:

If you have some control over what goes in the database, you may be able to store some sort of sequence numbers in the table data, and use that in your select.

If you are looking at "most recent" pages (like this forum software does for the thread lists) you might be able to select based on timestamp (say just the last two hours or two days etc.) then do the entries-per-page counting from the loaded data.

If you are worried about the problem of a database hit for every page, you might want to look carefully at your usage model. It's very common in paged systems that the first page gets something like 90% of the visits, the second page 90% of the remainder and so on. (Honestly, how often have you ever looked at the fifth page of results on Google?) Selecting (and caching) the first three or four pages each time) might then answer 99.99% of your page views.
 
Stan James
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
BTW: In the system I work on our solution was to do no such pagination. We'll give users up to n rows (usually 100) and no more. They shouldn't be spending time browsing through the database. Of course that last statement might only be true in our user population.
 
Pradeep bhatt
Ranch Hand
Posts: 8944
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Database specific soln looks good. How do we handle case where records gets deleted or new one gets added?
 
Try 100 things. 2 will work out, but you will never know in advance which 2. This tiny ad might be one:
SKIP - a book about connecting industrious people with elderly land owners
https://coderanch.com/t/skip-book
reply
    Bookmark Topic Watch Topic
  • New Topic