File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes using rownum with union Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "using rownum with union" Watch "using rownum with union" New topic
Author

using rownum with union

Pranav Sharma
Ranch Hand

Joined: Oct 27, 2003
Posts: 258
In order implement pagination on the front end, I need specific number(1-10, 11-20 ..) of rows to be returned from the db.

I know we can use constraints like
select * from table Where ROWNUM < 10

However I'm using multiple (4) joins in my sql query to fetch data from oracle 10g db.

How do i achieve this.
Brian Mozhdehi
Ranch Hand

Joined: Aug 17, 2006
Posts: 81
setMaxRows(int maxRows) on statement object
Purushoth Thambu
Ranch Hand

Joined: May 24, 2003
Posts: 425
If you want to fetch for each page then you need to use ORDER BY and ROWNUM. Let's say I join 3 tables emp, dept, loc and I need n rows at a time.

The query would be


You need to bind the values for ROWNUM predicate.

- ROWNUM does not ensure that records are fetched in any particular record. If you fire a query like
SELECT * FROM emp WHERE rownum < 10
Oracle does not guarantee you will get same set of 10 row very time. The ORDER BY clause will help to overcome this unpredictable behavior.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: using rownum with union
 
Similar Threads
Fetch only 'n' records from the ResultSet
sqlserver select where rownum between
SQL Question
JDBC2.0 feature. rs.absolute and relative( )
Pagination