aspose file tools*
The moose likes JDBC and the fly likes Displaying resultset in many pages Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Displaying resultset in many pages" Watch "Displaying resultset in many pages" New topic
Author

Displaying resultset in many pages

V Srinivasan
Ranch Hand

Joined: Aug 16, 2000
Posts: 99
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
Ranch Hand

Joined: Sep 28, 2000
Posts: 508
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
Ranch Hand

Joined: Aug 16, 2000
Posts: 99
Thank you Chantal for your prompt response. I am going through that article.
Thanks
anil bisht
Ranch Hand

Joined: Nov 04, 2000
Posts: 81
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
Ranch Hand

Joined: Sep 28, 2000
Posts: 508
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
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
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
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
Ranch Hand

Joined: Aug 17, 2001
Posts: 82
Hi Chantal,
The article you indicated above is excelent. Keep up the good work.
Suresh
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
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
Ranch Hand

Joined: Aug 02, 2001
Posts: 247
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?


CJP (Certifiable Java Programmer), AMSE (Anti-Microsoft Software Engineer)
Author of Posts in the Saloon
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
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

I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
In an earlier response, I suggested a general SQL-92 compatible way to get this paging behaviour.
- Peter
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Displaying resultset in many pages