paging resultset in servlets

[anil bisht]

hi
I am using jave servet to dispaly the customers list.
but it may fetch thousands of records from teh database . I need to display say 10 on each page with the facility of the next link. but i don't want to fetch thousands of results every time i click next link . it should just get the next 10 results from the database.
Can someone help me out ???
Anil

[Kyle Brown]

There are two possible solutions for this that I've seen, neither of which is really optimal, but you can decide which one you like better.
(1) You can copy the entire contents of the result set (JUST what you need for the list -- keep this small; something like name & primary key) into a data structure that you put in the HttpSession. For instance, if you are displaying a list of Employees you might use a Hashtable of Employee names to Employee serial#'s. If it's slightly more complex (e.g. you're showing a table rather than a list) you might have a Vector of Employee objects that contain the few fields displayed in the list. Then you just keep a note of what index your page ends on and page forward and backward that way. The downside of this is it's quite memory intensive if the list is big.
(2) Rewrite your SQL query so that you have some unique "row number" that you can make part of your query. Some databases allow this directly -- on others you'll need to add a new column into your table. Then you can make a different SQL query (different row numbers) for each page. The problem here is that you may need to change your table -- which might not be allowed.
Kyle
------------------
Kyle Brown,
Author of Enterprise Java (tm) Programming with IBM Websphere
See my homepage at http://members.aol.com/kgb1001001 for other WebSphere information.

[anil bisht]

hi kyle
thanks for the reply
1) first solution can't be used here as our details contain many fields( around 10) all to be displayed.
so my sesson variable will be huge. So this is ruled out.
2) as for the second solution i am confused as we have to order the results on 2- fields. so i can't use the query "select * from table where some_unique_id > last_result-in_last_page". actually i have done some asp coding so there in the result set there was some option which helped in the paging, i dont know if there is some similar method here ??.
Is there no othere option ???
anil

[Peter den Haan]

If you don't have a row number, or your search is dynamic enough that static row number field won't cut it, you can try the following. Schematically, to retrieve n records, numbers k to l inclusive (i.e. l-k=n-1):
Oracle:
SELECT fields
FROM tables
WHERE condition AND
 ROWNUM BETWEEN k AND l
ORDER BY order ASC
SQL Server:
SELECT TOP n fields
FROM tables
WHERE condition AND
 primarykey NOT IN (
  SELECT TOP k-1 primarykey
  FROM tables
  WHERE condition
  ORDER BY order ASC)
ORDER BY order ASC
ANSI SQL:
SELECT fields
FROM tables t1
WHERE condition AND
 (SELECT COUNT(*)
  FROM tables t2
  WHERE condition AND
   t2.order <= t1.order) BETWEEN k AND l
ORDER BY order ASC
Replace "primarykey", "fields", "tables", "condition" and "order" by the appropriate clauses. Performance will depend on the intelligence of your database's optimiser.
- Peter

[This message has been edited by Peter den Haan (edited October 23, 2001).]

[anil bisht]

hi peter
I think the oracle query u wrote wont work
first of all for "rownum between n and m " query will not work. (correct me if i am wrong)
and second case
the order by works only on the selected rows not on all.
So first it will select first 20 rows and then sort these 20 rows...
and then in the next page there will be again 20 sorted rows but there will me no sorting relation between these two pages.
it will have some default sorting.....
so in the first page it will have
anil
peter
tom
and next page it will have
amit
jack
jane
but what i want is
first page
amit
anil
jack
second page ..
jane
peter
tom
anil

[Dharmesh Chheda]

hey anil if you program has an integer primary key its damn simple but in case you dont then store the entire resultSet in a vector and use it to display the pages
i have implemented and its being used .... but i have the display in JSP...
if interested let me know
bye
------------------
IBM Certified WebSphere Application Server V3.5 Specialist

[Adam Hardy]

somebody was talking about a paging resultsets servlet that did the whole business, but I don't remember the details - you'd have to search the archives. i think it's worth it - sounded like the code did most things - producing page number links and forward and backward links like the search results in google or altavista.
good luck
adam

[Dharmesh Chheda]

Yeah adam
the search as same as that in google ...

------------------
IBM Certified WebSphere Application Server V3.5 Specialist

[anil bisht]

hi Dharmesh
YA we thought of storing them in a vector and storing vector in a session. but our ressult might give 10000, of records and each record containing 20 fields.
so will it have some perfomance issues.. on the server..
anil

[Dharmesh Chheda]

well anil ... when i was implementing the same thing my mind was toggling too .. but then that proved to be the best solution for me cause i was working on the tables used by an ERP.. which had no primary key..no unique field n stuff... and as far as the server load is concerned it doesnt affect much .. believe me

------------------
IBM Certified WebSphere Application Server V3.5 Specialist

[David O'Meara]

I'm feeling like a broken record, but I'm against puting data of this size on the session. But...
If you have 10000 records of 20 attributes, make the initial search and return the 10000 records but the primary key value only. Store these PKs in a list or if they are integers, an array of ints. Put this List or Array on the session.
It means that at each page you can easily pull off the range you want and can either pull the 20 rows off at once (where PK in ...) or one at a time (where PK = ? etc)
There is an overhead in extra db calls but a smaller amount of data to store on the server and less wasted data returned from the db. It might be worth profiling to see how well it works tho...
Dave.

[Peter den Haan]

Originally posted by anil bisht:
first of all for "rownum between n and m " query will not work. (correct me if i am wrong)

Think it will, but I don't have an Oracle db on-site here to try it out (it's all off the top of my head, so caveats apply).

and second case the order by works only on the selected rows not on all.

Spot on, I think - logically, the sorting is done after applying the WHERE clause. So you'd need to junk your order by and use indexes strategically to get the sort order right - not very nice and not always possible. Anyway, the ANSI SQL method should stil work.
The MS SQL method doesn't have this problem since TOP is applied after sorting the result set.
Thanks Anil.
- Peter

[This message has been edited by Peter den Haan (edited October 24, 2001).]

[Peter den Haan]

There's obviously also the facilities JDBC 2 scrollable result sets, if your driver supports them - you can jump to any arbitrary result in the set, read the records you want to display, then close it. At least you won't be transferring all that data over.
I agree with the qualms about storing that many rows in the session - if you have a significant number of users that will hurt. The least you should do is encapsulate the thing in an object that stores the results in a transient ArrayList that will be rebuilt when necessary. If you don't do that, performance on distributed servers will be atrocious.
In some cases, you might be able to simply store all the data, or at least enough of it to do indexing, in the application context. It usually means rolling some expiry mechanism to prevent it from going stale, it would need to be threadsafe, and it is only possible in a minority of cases, but it's fast and scalable when it works.
- Peter

[This message has been edited by Peter den Haan (edited October 24, 2001).]

[Mike Curwen]

I was going to mention the JDBC2 scrollable resultset, but wasn't sure if this supported the 'sliding window' type of resultset that an ADO RecordSet supports.

Is anyone aware if there are ANY drivers out there that just open a cursor on the table, and provide this type of behaviour? ie: they marshall the data back and forth as required... So the initial 'bunch' of records are the 'first' 100, and when you go rs.next() and exhaust that buffer, the driver goes back to the cursor and grabs the next 'bunch'... and does so transparently.

Seeing as how a JDBC resultset (at least pre 2.0) *requires* a connection, I don't see the problem with implementing this behaviour. But has any provider done it?

[Peter Guillebaud]

I have done this using the SQL keyword LIMIT to retrieve the next 10 from the db like "SELECT * FROM myTable Limit intVar, 10"
and you increment intVar by 10 if the user wans to see results 11 - 20 on the next page, and 21 - 30 on the next etc.

[anil bisht]

hi Peter
Which database did u use ???
anil

[Terence Doyle]

Hi,
GREAT Peter!!
I've been trying to get around this problem but my SQL is a little basic.
I tried this in the mySQL console and it worked a treat!
Now I've just to work it into a jsp..which should be relatively simple.
Thanks,
Terry

[Peter den Haan]

I should warn that LIMIT is not standard SQL, it is database-specific. In general, if you're asking these kind of questions and you are willing to use database-specific SQL extensions, it is a good idea to mention the specific database you are using.
- Peter

[DC Dalton]

I run a servlet driven site with MySQL database & have tens of thousands of rows that can be returned from a query. What I do is first determine if they are coming in fresh or from hitting the "next 100 results" button. I do this because I send a hidden field with the page num....Its a little confusing so Ill try to elaborate. The first time they come in the row varaible is null so the query is SELECT * FROM table WHERE [conditions] LIMIT 0, 100; Just before I call my while(rs.next()) I declare a variable call rowNum. Then as I am printing out the resultset in a while loop I increment this variable for each loop. If the results are more than 100 I then create a next button. Behind the next button is the afformentioned row variable AND the query string. I will mention that I had to replace the ' for the query string with $ before sending it to the next servlet or all sorts of strange things happened. On the next pass the if condition if(req.getParameter("row") != null) I grab the query, chop off the LIMIT x, x and then take the row variable & multiply it by 100 so the second pass thru looks like this:
SELECT * from table WHERE [condition] LIMIT 100, 200. I do this for thousands of pages of returns & it works like a charm PLUS it keeps the resultset objects down to a dull roar.....HOPE THIS HELPS!
[This message has been edited by DC Dalton (edited October 29, 2001).]

[Jim Wang]

I have met this problem before. My solution is change the attribute from read-forward-only to concur-updateabl when I create the connection. Then I put a record limit in my servlet(for me is 400, and for you may be 10). I also put a parameter for page number on each page. I use this pageNo to control which record need to be involed. For my case I don't think it's good to put the resultset in session. Because I don't know whether the next request from user is for next page also it would use so much memory.

[Manjunath Reddy]

Check out my response @ this thread.
http://www.theserverside.com/discussion/thread.jsp?thread_id=6993
cheers!

[anil bisht]

hi Jim,
*******************************************
Then I put a record limit in my servlet(for me is 400, and for you may be 10).
**********************************************
if i put a record limit on the servlet, then it will query the database for all tens of thousands of records and wil give me all the matching records, and servlet will teke only 10 and discard others.
so time taken will be same (either i fetch 10 records or 10000 records)
any idea on this ???
anil