jQuery in Action, 3rd edition
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, http://asktom.oracle.com/)

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: http://aspose.com/file-tools
subject: Using Rownum in JDBC
jQuery in Action, 3rd edition