• Post Reply Bookmark Topic Watch Topic
  • New Topic

First/Previous/Next/Last Page  RSS feed

 
Yi Si
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello there,
Problem:
I want to implement "First/Previous/Next/Last Page" function for data retrive.
I think there are three strategies for this.
1. Download all records in client, control them with JS.
2. Retrive data again from DB every time of turning page.
3. Retrive all records one time, then cache them in server memory to response client retrieve.
Question:
Can somebody tell me which is best practise?
Whith second strategy, I think I need add mark on DB.
How to do this?
[ March 09, 2004: Message edited by: Yi Si ]
 
Chris Hall
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would recommend the 3rd option, grabbing the information once and storing it in memory. Unless the size of the data is too much for memory, which would mean a lot of data. If the data is too much for memory, hitting the database each time is necessary. My personal preference would be not to use javascript, I just feel you are adding unecessary complexity.
 
Chris Hall
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yi,
You keep posting the same question is different forums, I believe this is frowned upon, so please only post your question once in the appropriate forum. In this case I think it is most appropriate in JDBC..
 
Bear Bibeault
Author and ninkuma
Marshal
Posts: 65833
134
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Chris is correct; please do not cross-post the same question in more than one forum. Please choose a single forum you feel is best.
I originally closed this topic in favor of one in Servlets, but that topic seems to have been deleted so please continue any discussion here.
[ March 09, 2004: Message edited by: Bear Bibeault ]
 
Jeroen Wenting
Ranch Hand
Posts: 5093
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As said, do the scrolling in JSP code, caching the data somewhere.
I'm using the following code to scroll through my results:

had to add some "." into the JS events to get the forum engine to not complain.
First section uses a custom tag written by ourselves to set the table headers (with formatting, background, etc. etc.) in our house style.
Subsequent section uses JSTL with the paging option on <c:forEach> to provide pagination.
The last row contains buttons for scrolling back and forth (no first and last, forgot to add those (DOH)).
In the servlet code that retrieves the data, I do the following to control scrolling:

The two parts are in different methods, one called to process the user interaction and the other to prepare the next showing of the page.
"actie" contains the action selected on the page, which is set in the Javascript executed on pressing the button.
There's a different method executed when the user clicks on a row.
Edit: the list contains custom beans exposing the fields used in the JSTL of course. Any List will do, I implemented a List with sorting as the data is returned from our datastore in non-sorted order.
[ March 10, 2004: Message edited by: Jeroen Wenting ]
 
Nicholas Cheung
Ranch Hand
Posts: 4982
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The method provided by Jeroen is also suggested by Hans in another thread.
Structs is now support ResultSet listing, it can be stored inside the session and be converted during display using JSTL. It is a good idea to handling data retrieve from database, eespecially listing and searching.
Nick.
 
Srikanth Shenoy
author
Ranch Hand
Posts: 184
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi folks,
I have some suggestions based on my experiences for dealing Prev, Next functionality with large scrollable result sets.
There are two aspects to the Prev/Next functionality.
The first is the display and the second is database/memory data retrieval
I have found the following two third party (Both are free and one is open source) very helpful. They are:
1) Pager Taglib from jsptags.com
2) Display Tag from sourceforge (displaytag.sourceforge.net)
Both of them are very good, very powerful and they can pretty much take care of paging, display, formatting issues.
Coming to the second point - Data retrieval. It looks like a plain vanilla ValueListHandler is being suggested in the previous posts.
By plain vanilla I mean "All the data is fetched at one shot and is iterated over at increments of 10 (say)"
This approach works well if the number of rows is probably less than 100
When the row count goes well above 100 certain problems appear. Let us say 1000 rows were fetched.
1)The data has to be stored in HTTP Session. If each row takes up 500 bytes, you have a HTTP Session of size 500K. Multiply by that by number of users and you have run out of space....
2)If a shared cache is used, then the size of the cache is reduced, but what happens if the data in cache is removed (based on LRU - Least recently used).
Your solution has to address such questions. I have found it better to introduce a bit of database dependency in my solution (Standards freaks might frown on my solution, but sometimes rules have to be broken for getting even a decent performance)
My solution is a hybrid of fetching database rows on demand and fetching all rows. All databases provide features to uniquely identify the current row.
In Oracle it is the ROW_NUM
In DB2 it is the ROW_NEXT
When the first page is requested, fetch the data and track the ROW_NUM (or ROW_NEXT). A user who has requested the first page, will most certainly fetch more. Hence do a eager pre-fetch for the next three pages (say) after the first page is served (maybe asynchronously on a low priority thread - so your CPU has time for other higher priority tasks). This solution WORKS and works well.
You can abstract the above implementation behind a spicy ValueListHandler and ValueListIterator (Core J2EE Patterns).
(Sometimes plain-vanilla just doesnt cut it )

Now onto the Standards turf - Think about it - How many times in the lifetime of an application do you really port the database to a different vendor?
Even if you port it, the portability we have sacrificed in the above solution is neglible and is trivial to change when migrating to a different database.
Or shall I say, this is the best of both worlds ?
 
Pradeep bhatt
Ranch Hand
Posts: 8933
Firefox Browser Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
All databases provide features to uniquely identify the current row.
In Oracle it is the ROW_NUM
In DB2 it is the ROW_NEXT

What about Sybase, MySql ? Do they have this feature? :roll:
 
Jeroen Wenting
Ranch Hand
Posts: 5093
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The caching is simple.
I call the same function which itself manages the cache as well.
If the cache is empty, it fills the cache before returning the data.
There's a separate function to force the cache to be cleared which I can call if needed.
I've several resultsets of over 1000 rows each, no problem there.
But then my app runs on dedicated machines, and isn't bound by the strict memory limits often imposed by shared hosting plans (but then I'm working on a huge project).
 
brightsome xie
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
very good!!!
 
m bhramaresh
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i just implemented that by writing some code in my jsp page itself.
put some links down the report and some small calculations to make it easier
the suggestions on the top also appreciated..........
prefer which u felt as comfortable
cheers
 
Srikanth Shenoy
author
Ranch Hand
Posts: 184
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Pradeep Bhat:

What about Sybase, MySql ? Do they have this feature? :roll:

I dont know about Sybase or MySQL.
Can somebody shed some light, if something similar exists.
I am sure it does exist in any decent RDBMS.
 
Nicholas Cheung
Ranch Hand
Posts: 4982
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If your DB is MySQL, and you want to do the prev/next page in JSP, you can use the SQL:

where x is the starting record number while y is the ending record number.
For Sybase, it has a property named rowcount, but this may not help as the usage is different from rownum in Oracle, and limit in MySQL. You may need using it together with store procedure.
Although the database provides powerful functions to work with this, if the number of record is not that much, I still prefer retrieving the data out from the DB, and the convert the data into ArrayList or Struts list, and stored in the session.
Since DB is an expensive operation, if we query it everytime, problems maybe encounter. Various posts have been discussed on how JSP can perform listing with the function of prev/next page.
Nick
[ March 11, 2004: Message edited by: Nicholas Cheung ]
 
Lavern Matthew
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

...
I have found the following two third party (Both are free and one is open source) very helpful. They are:

1) Pager Taglib from jsptags.com
2) Display Tag from sourceforge (displaytag.sourceforge.net)

Both of them are very good, very powerful and they can pretty much take care of paging, display, formatting issues.
...

The Display Tag currently does not allow paging to be done in the data layer

The product I recommend is (valuelist.sf.net).
 
Mahesh Pinnamaneni
Ranch Hand
Posts: 100
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Guys,
i am a newbie to pager-taglib. when i have done the same thing as it is mentioned in documentation upto 'Installation'. i didn't understand the 'Examples' section. do i need to do this inorder to work with this example.

i am not getting the lower part of the jsp page which contains the prev/nect fucntionality when i access the pager-demo.jsp file.
pls help me, where i need to do changes.
thanks & regards,
Mahesh
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!