Displaying resultset in many pages

[V Srinivasan]

I am retrieving selected records (some hundreds of records), from a table which has thousands of records, through Servlet. I want to view them pagewise say 25 records per page. How to do this? I thing storing the records in an array and dispatch 20 records per page is not sounds good. What is the solution which uses less resources at server? Anybody please respond.
Thanks in advance.

[Chantal Ackermann]

This site gives you some tips about the size of the blocks jdbc fetches in one run from the database:
http://www.as400.ibm.com/developer/java/topics/jdbctips.html
(section "Use Blocked fetch")
chantal

[V Srinivasan]

Thank you Chantal for your prompt response. I am going through that article.
Thanks

[anil bisht]

hi Chantal
any idea how can i do it, if i am using servlets as there i can't have the same connection and the result set ???
TIA
anil

[Chantal Ackermann]

hi Anil,
I think, it's not a good idea to use one connection for more than one user. try to work with a connection pool (the site I mentioned above provides some sample code). it is possible to reuse a result set, though, but only if you specify this during instantiation (see the API for ResultSet).
chantal

[Peter den Haan]

The least resource-intensive way is of course to make sure the database returns exactly those records you want to show. 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 "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).]

[Jamie Robertson]

Can you not just just Statement.setMaxRows(int) to get the desired effect? set maxRows to the number of rows that you think would be sufficient for the user to peruse through(or allow the user to set it with a drop down box of "Maximum Matches" or "Top [drop down number] Matches" or something like that)
"public void setMaxRows(int max)
throws SQLException
Sets the limit for the maximum number of rows that any ResultSet object can contain to the given number. If the limit is exceeded, the excess rows are silently dropped.
Parameters:
max - the new max rows limit; zero means unlimited"
Is this the same as Peter's solution except through jdbc?
Jamie

[Jamie Robertson]

I forgot to mention that you should order your query so the most important/best matches ensure that only the best answers are displayed.
Jamie

[Suresh Kanagalingam]

Hi Chantal,
The article you indicated above is excelent. Keep up the good work.
Suresh

[Peter den Haan]

Originally posted by Peter den Haan:
The least resource-intensive way is of course to make sure the database returns exactly those records you want to show. [...]

I wasn't in a position to try out these queries, and there you go... the Oracle one doesn't work (unless an index produces the right sorting order) because the ROWNUM condition is applied before sorting. The ANSI SQL way should still work for Oracle though.
- Peter

[Gerry Giese]

Peter, would the sorting be applied before ROWNUM if you selected against a sorted view? It's my understanding that if you're using multiple tables in a frequently-used query anyway that you should use a view. If you set the view so that it's sorted (indexed?), then select against that, I would hope that the ROWNUM would apply to the sorted data. My SQL is rather rusty, though. Comments?

[Adam Hardy]

Originally posted by Gerry Giese:
Peter, would the sorting be applied before ROWNUM if you selected against a sorted view?

how about for ANSI SQL? I don't think that has anything like ROWNUM, or at least a cursory search of the mySQL website didn't produce anything.
In the java class getting the resultset from the database, you could of course use the index of the resultset as a marker so that you know where to start when the next page is requested.
you could also limit the resultset like Jamie mentioned so that the maximum number of rows returned was equal to the index (the index of the last row displayed on the previous page) plus n records to display on this page.
the only problem I can see with that is in displaying the page numbers for the user to choose e.g.:
1 2 3 4 5 6 ... next>
at the bottom of the first page, because then the user could choose the last page in the set and you would end up requesting the whole thousand records - but is that so common?
anybody understand that?!
Adam

[Peter den Haan]

In an earlier response, I suggested a general SQL-92 compatible way to get this paging behaviour.
- Peter

[Adam Hardy]

Originally posted by Peter den Haan:
The least resource-intensive way is of course to make sure the database returns exactly those records you want to show. To retrieve n records, numbers k to l inclusive (i.e. l-k=n-1):
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 "fields", "tables", "condition" and "order" by the appropriate clauses. Performance will depend on the intelligence of your database's optimiser.
- Peter

Oh yes, sorry - I couldn't figure out the SQL. What are the t1.order and t2.order columns?
Adam