I am interested in finding out what would be the best way to deal with pagination in a web application conecting to a data base and performin a query against the db.
Once the query is performed, I have a ResultSet.
I need to show the rows of the result set to the user, using pagination.
I need to show the user how many pages I found, present the first page and allow the user to chose which page to see.
I know how to do this, and allready did it, creating my own CachedResultSet, that I persist in the session, and connecting it to a table object that knows how to paginate.
What I need to know is what are the proved best ways to deal with this type of problem from the design point of view.
If I have this CachedResultSet, I can run into problems if a lot of users are hitting my application server at the same time, and I have too many such objects stored in memory. I could reduce the size of each CachedResultSet, by setting a max for each of them, and presenting to the user a message that the result set was too big and say only 100 rows are presented in 10 pages, each with 10 rows, and that the user should refine the search to get less results.
This way I minimize the size of each CachedResultSet, but stil can't controll how many such objects will be at once in memory. On the other hand, I did not see this type of design on the major sites, like Google, and others, so it might not be the best ideea.
I can use one of MySQL features, that limits the number of rows, and allowd what range to find. However this seems to have not been optimized, so it might be slow.
I can have a primary key that I use saying "Select * from whatever where id <= something and id >=something.
However, I run into problems when I delete rows.
The deleted row will dissapear from the table, and I lose the sequence of the id. I could have all the remaining rows renumbered each and every time I delete a row, but this also seems a bit weird to me to do.
Anyway, maibe there are allready better ways of doing it out there.
I am looking for an answer.
However, I am looking for an answer that will be at the design level, and not some implementations, that I have allready seen on the net, none of them really responding to the design questoin.