This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes How to Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to "page" results from a database?" Watch "How to "page" results from a database?" New topic
Author

How to "page" results from a database?

Andrew Carney
Ranch Hand

Joined: Oct 17, 2006
Posts: 96
Hello,

Suppose I need to query a database and the result contains 100K of records. Can someone please show me a code example of how to "page" the result for, say, 1K of records at a time?
John Simpson
Ranch Hand

Joined: Nov 28, 2005
Posts: 30
Hi,

If you are using a relational database and the data is ordered then you can do the paging in the SQL statement, on many DBs anyway.
Something like:



The fetch statement controls the number of rows returned. Set lastKey to the key of the last record in the previous page.
There's probably a better way to do it, but that should work.

John
Andrew Carney
Ranch Hand

Joined: Oct 17, 2006
Posts: 96
Hi John,

Interesting solution, the only problem is that they key is not something numeric that I can run on, it's random numbers.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61458
    
  67

So? What does that have to do with anything? The whole concept of paging ties in with the order with which rows are to be returned. You can specify whatever sorting you like with an ORDER BY clause.

There's a JSP FAQ entry on this subject, by the way.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Diego Pedrosa
Greenhorn

Joined: Nov 09, 2007
Posts: 8
Hi,

I think you can solve this issue with the 'LIMIT' SQL keyword.
Read this: Paging in SQL


SCJP 5.0 (Yeah!! finally...)<br /> <br />There are 10 kinds of people...<br />The ones that understand binary,<br />and the ones that don't. ;P
Andrew Carney
Ranch Hand

Joined: Oct 17, 2006
Posts: 96
Hi Diego,

Nice one! I'm going to give it a go!
Pat Farrell
Rancher

Joined: Aug 11, 2007
Posts: 4659
    
    5

Originally posted by Roy Cohen:


Interesting solution, the only problem is that they key is not something numeric that I can run on, it's random numbers.


sounds like a good reason to have an autoincrement field in the table. Key it, and you are ready to go.

If you don't index the column, this approach can get really slow.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2504
    
    8

Here's the link to this nice JavaRanch Howto from the JSP FAQ:
PaginationOrPaging : How do I limit the number of rows displayed in the browser?
Regards, Jan
[ November 13, 2007: Message edited by: Jan Cumps ]

OCUP UML fundamental and ITIL foundation
youtube channel
John Simpson
Ranch Hand

Joined: Nov 28, 2005
Posts: 30
I'm probably missing something here, but I can't see a solution that will work with un-ordered data.
If the data is not ordered, then can you guarantee that rows will be returned in the same relative position over multiple queries?
Or is the data ordered but not keyed?

John
[ November 13, 2007: Message edited by: John Simpson ]
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by John Simpson:
I'm probably missing something here, but I can't see a solution that will work with un-ordered data.
If the data is not ordered, then can you guarantee that rows will be returned in the same relative position over multiple queries?
Or is the data ordered but not keyed?

John

[ November 13, 2007: Message edited by: John Simpson ]


you include an order by clause in your query.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to "page" results from a database?