• Post Reply Bookmark Topic Watch Topic
  • New Topic

Pagination of Oracle database query resulet

 
Shahid Kahn
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My query retrieves more than 5000 records from Oracle database. I want to display the records 100 per page. I know it is called pagination. Any detailed styp-by-guide or tutorial or example available?
 
Dave Tolls
Ranch Hand
Posts: 2283
25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here's Tom Kyte's article from several years ago about using ROWNUM and Top-N for pagination in Oracle.
Look for the section "Pagination with ROWNUM" about half way down.
 
Tim Cooke
Sheriff
Posts: 3297
153
Clojure IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As a side note: I would seriously question the value of presenting over 5000 records on the UI, paginated or not. No user is going to look at every single one.
 
Dave Tolls
Ranch Hand
Posts: 2283
25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim Cooke wrote:As a side note: I would seriously question the value of presenting over 5000 records on the UI, paginated or not. No user is going to look at every single one.


That's the reason for pagination, surely?
So you're only displaying a subset of the results.

That's what Google does, after all.
 
J. Kevin Robbins
Bartender
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I always recommend the jQuery plugin DataTables. It will make this a lot simpler. It does the pagination for you, lets the user select how many records they want displayed, will process the records on the server side returning only as many as need to be displayed, provides column sorting, it's theme-able with jQuery UI and other features too numerous to list.

I'm not associated with the product, I just use it a lot.
 
Bear Bibeault
Author and ninkuma
Marshal
Posts: 65524
105
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'd suggest only retrieving as many rows as you are yogin to display. Why fetch 5000 rows if you are only going to show 100? There are SQL clauses to "slice" the data that is returned by the result set (depends upon database), and JPA methods to do the same.
 
Dave Tolls
Ranch Hand
Posts: 2283
25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bear Bibeault wrote:I'd suggest only retrieving as many rows as you are yogin to display. Why fetch 5000 rows if you are only going to show 100? There are SQL clauses to "slice" the data that is returned by the result set (depends upon database), and JPA methods to do the same.


Which is exactly what the Ask Tom page I linked to shows you how to do.
Attempting to show everything is a short hop from a padded room and a coat with long arms.
 
Bear Bibeault
Author and ninkuma
Marshal
Posts: 65524
105
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Agreed. And any large data set without filtering ability is a recipe for a straight-jacket.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!