Win a copy of The Journey To Enterprise Agility this week in the Agile and Other Processes forum! And see the welcome thread for 20% off.
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Junilu Lacar
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Ganesh Patekar
  • Tim Moores
  • Pete Letkeman
  • Stephan van Hulst
Bartenders:
  • Carey Brown
  • Tim Holloway
  • Joe Ess

Pagination using the database(DB2)  RSS feed

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This post is about how to implement pagination when you have a very large number of records and you need to retrieve small chunks of records to be displayed on each page from the database at a time.note: this is a pure "from the scratch" approach and should be treated as the last resort i.e if nothing else works
my implementation was to display 50 records in each page(DB2 database in the backend) and the user should be able to navigate back and forth using "Next" and "Previous" buttons
Implementing "Next" button:
---------------------------------
This is the easier part of the implementation, assume you have a table with 2 columns Emp ID and Emp Name and Emp ID is the key, lets say you retrieved the first 50 records from the database in descending order(showing the latest employees first) on the first page , so if i have 500 employees in all, this will fetch records with Emp ID through 500-450, when the user clicks on "Next" you need to show records from 449-400 in the next page, all you have to do is get hold of the last record in the previous list and run a query, which goes something like this

select Emp ID,Emp Name
from employee
where Emp ID < 450
order by DESC
fetch first 50 records only

this can go on and on till you reach the last page ,

Implementing "Prev" button:
--------------------------------
This is a little tricky , lets suppose i am on page#3 viewing records from 399-350 ,if i want to go to page#2 which is records 449-400 , i need to get hold of the first record in the previous page and run a query like this

select Emp ID,Emp Name
from employee
where Emp ID > 399
order by ASC
fetch first 50 records only

here if you noticed i switched to "order by ASC" , this is the important because if i didnt order the records in ASC , this query would have fetched the first fifty records in the table i.e 500-450 , so by sorting in the reverse order and fetching the first 50 records i was able to get the records 449-400

however this implementation has a problem , this query will return the records in the order 400-449 , but in my pages i need all records to be displayed in the descending order , so you will have to reverse the order of the items fetched from the database in your java code, which can be done using Collections.sort() and Comparator , below is snippet of the code i used
Collections.sort(lstItems, Collections.reverseOrder(<Object>.<Name of the comparator>));

Agreed that this implementation will create a lot of database calls , but this works prefectly if you cannot fetch say 10K records at one go and handle the pagination on java side using lists etc
 
Bartender
Posts: 10575
66
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

siddarth kandikonda wrote:Agreed that this implementation will create a lot of database calls , but this works prefectly if you cannot fetch say 10K records at one go and handle the pagination on java side using lists etc


Actually, the worst thing about it is that you probably don't need to do anything at all.

As far as I know you can set the cache limit for JDBC either externally or by method call (not sure exactly how myself; but I'm sure someone else here will), which means that you really only need to do the call as if you want all possible rows. Then you just deal with the ResultSet as you need to to get your 'pages'. The only problem might be if you need some sort of 'go to page n' facility, but it may even be smart enough to deal with that.

Lesson: Don't over-engineer before you know you need to.

Winston
 
siddartha kandikonda
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
totally agree with you, there must be really easy ways to do this, this could be something unique to the kind of setup im dealing with.ok,let me see if im understanding this correctly, you are suggesting that JDBC can handle this out of box,its going to take care of giving me the set of records i want on each page,but is it going to fetch that 10K records at one go and hold it in cache?woudn't that hit my performance?
 
Winston Gutkowski
Bartender
Posts: 10575
66
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

siddartha kandikonda wrote:...ok,let me see if im understanding this correctly, you are suggesting that JDBC can handle this out of box,its going to take care of giving me the set of records i want on each page,but is it going to fetch that 10K records at one go and hold it in cache?

Yes. And I suspect you can set the cache limit a lot lower than 10K (and it's more likely to be based on memory - eg, 64Kb - anyway).

...woudn't that hit my performance?

Depends what you want, but unless it's outlandish (or the 'find me a random page' thing I suggested above) I doubt that you'll have too many problems. Databases (and SQL, being a Data-directed language) are great for this sort of thing.

Winston
 
siddartha kandikonda
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks winston for pointing me in the right direction,i just started looking at what JDBC offers for pagination
 
Winston Gutkowski
Bartender
Posts: 10575
66
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

siddartha kandikonda wrote:...just started looking at what JDBC offers for pagination


My advice: don't look for 'pagination', because that's what you want to do with it. Look for something more like 'cache limits' or 'retrieval limits'.

Winston
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!