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


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using Rownum in JDBC" Watch "Using Rownum in JDBC" New topic
Author

Using Rownum in JDBC

Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
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;
if(count>start)
{
test = count - start;
if(test>20)
end = start+20
else
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

Thanks
regards

Aravind
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081


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

Shailesh


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.
 
 
subject: Using Rownum in JDBC
 
Similar Threads
Pagination in JSP
Please help with pagination in JSP?
Pagination
Pagination
pagination in jsp