This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and the fly likes Pagination - Skipping rows in rs Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Pagination - Skipping rows in rs" Watch "Pagination - Skipping rows in rs" New topic
Author

Pagination - Skipping rows in rs

Lisa Modglin
Ranch Hand

Joined: Oct 28, 2003
Posts: 46
I'm going to try this method of pagination retrieve from the FAQs. I have never had the necessity of using anything less than the entire ResultSet. How do I skip rows? Is it done with the ResultSet object? If not, I can do this in a loop by counting to the row I want to begin on. I just thought there might be a way to jump to the row I want. Is there?

Repeat the query for each new request.
Algorithm
Client requests page 1
Execute the query
Return rows for page 1
Client requests page 2
Execute the query
Skip rows for page 1
Return rows for page 2
Lisa Modglin
Ranch Hand

Joined: Oct 28, 2003
Posts: 46
I think I found it. Is it the absolute() method?
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30361
    
150

Lisa,
Absolute is pretty much equivalent to rs.next(). The driver still pulls in all the rows over the network.

For pagination, people typically limit the results in the SQL query. The syntax varies based on your database. What database are you using?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Lisa Modglin
Ranch Hand

Joined: Oct 28, 2003
Posts: 46
Well, I'm using SQLServer. I thought I looked into limiting the results and that SQLServer does not have the same functions as MySQL. I thought, based on some posts, that you can only retrieve from the top in SQLServer and not from within the middle. Please let me know how if it is possible to retrieve, for example, rows 11 - 20.

Thanks!
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by Lisa Modglin:
Well, I'm using SQLServer. I thought I looked into limiting the results and that SQLServer does not have the same functions as MySQL. I thought, based on some posts, that you can only retrieve from the top in SQLServer and not from within the middle. Please let me know how if it is possible to retrieve, for example, rows 11 - 20.

Thanks!


To make the example clearer, let's say you want rows 31-40 (page 4, 10 rows per page) from:
SELECT x FROM table ORDER BY Y

You can do it something like this (I don't have a DB to test it on):



In other words, you select the top 40, invert the order and select the top 10, and reorder for the final result.
 
Don't get me started about those stupid light bulbs.
 
subject: Pagination - Skipping rows in rs