File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes using rownum with union Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Customer Requirements for Developers this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "using rownum with union" Watch "using rownum with union" New topic

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:
subject: using rownum with union
jQuery in Action, 3rd edition