File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Using Rownum in JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Using Rownum in JDBC" Watch "Using Rownum in JDBC" New topic

Using Rownum in JDBC

Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 265
Hello Ranchers..
I'm doing JSP and Oracle in my project. In a particular point i want to display the recrods in a page using pagination..
I go thru the FAQ for pagination in JavaRanch FAQ
and i searched for the use of Rownum.. but failed to get idea..
the algorithm behind this will be like this
from the previous page..
<a href="next.jsp?i=20"> next 20 </a>
int start = rs.getString("i");
int count = select count(*) from table_name;
test = count - start;
end = start+20
end = test;
if(i< count) //count assigns the count of the table
Select * from <table-name> where rownum> start and rownum <end;

this is just a sample code.. for developing the code..

can you please help me to get the query for using rownum.. so that i can just substitute the start and end values.. it will display the values as such..

Please help me Racnchers


Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1082

Select * from <table-name> where rownum> start and rownum <end;

Rownum does not work with > or = operator, so your mentioned query will not produce result. only query you can use with rownum is like

Select * from <table-name> where rownum <end;

You need to use inner query here which will do your task

but in this case you will not be able to use ordered paging, I mean you can not generate paging based on a column.

I would suggest you look for oracle function row_number() that would help you to generate paging on column basis


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
The standard way of doing paging on Oracle is:

(Borrowed from Tom Kyte,

In your case, if there really isn't a column value you want to order on, then you still need to order by something to ensure that your page ordering is reasonably stable and I would suggest you order by the ROWID pseudo-column (another Oracle-specific column). ROWIDs *can* change but they very very rarely do, so they're suitable for short-term ordering like this.
I agree. Here's the link:
subject: Using Rownum in JDBC
It's not a secret anymore!