• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using Rownum in JDBC

 
Aravind Prasad
Ranch Hand
Posts: 265
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic