Using Rownum in JDBC

[Aravind Prasad]

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]

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

select * from (Select ROWNUM R, COL1,COL2,COL3  from <table-name> )WHERE R > start and R< end 

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

[stu derby]

The standard way of doing paging on Oracle is:

select * 
  from ( select a.*, rownum rnum
           from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
          where rownum <= MAX_ROWS )
 where rnum >= MIN_ROWS

(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.