jQuery in Action, 2nd edition*
The moose likes Oracle/OAS and the fly likes Query to fetch 100 records in 1000 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Query to fetch 100 records in 1000" Watch "Query to fetch 100 records in 1000" New topic
Author

Query to fetch 100 records in 1000

Mintoo kumar
Ranch Hand

Joined: Aug 21, 2007
Posts: 61
Hi all ,

I have more than 10000 records in database , due to size limit , i can not fetch all records at once.Therefore i just wanted to know is there any way that i can give size limit and get records step by step.
Like , first time i would get 100 records then another 100 and so on.

Thanks in advance.
Rajitha Gunawardhane
Greenhorn

Joined: Dec 11, 2007
Posts: 28
Hi,
here are the hints

* you can use LIMIT command in SQL
Ex:
SELECT * FROM `your_table` LIMIT 0, 10

* you can use
setMaxRows(int max) method or setFetchSize(int rows) in java.sql.Statement class

Best Regards,
Rajitha
Mintoo kumar
Ranch Hand

Joined: Aug 21, 2007
Posts: 61
Thanks for the help.

But it seem LIMIT is not wokring in SQL. as i m using PL/SQL devloper editor. if any idea . let me know

Thanks in advance.
Freddy Wong
Ranch Hand

Joined: Sep 11, 2006
Posts: 959

Use rownum in Oracle, e.g.

[ August 21, 2008: Message edited by: Freddy Wong ]

SCJP 5.0, SCWCD 1.4, SCBCD 1.3, SCDJWS 1.4
My Blog
Mintoo kumar
Ranch Hand

Joined: Aug 21, 2007
Posts: 61
Thanks a lot ,it worked.
Mintoo kumar
Ranch Hand

Joined: Aug 21, 2007
Posts: 61
Hi All ,

thanks for the suggestation.But my Query was abit diff.
like . i using the query

Select *
From myTable
Where rownum Between 1 and 20
Order By rownum

What i need to do is , i need to keep changing the limits like on first request is 1 and 20 ,then on second request i would require data b/w 21 to 30.

I have tried the same query in oracle but after changing the limit like 21 to 30 .it does not give any output.

Suggest me if anything i have left or need to alter in the query, m using oracle as DB.

Thanks in advance.
Madhavi Venna
Greenhorn

Joined: Aug 06, 2008
Posts: 22
Originally posted by Amit Kumar Jha:
Hi All ,

I have tried the same query in oracle but after changing the limit like 21 to 30 .it does not give any output.

Suggest me if anything i have left or need to alter in the query, m using oracle as DB.

Thanks in advance.


select * from
(select rownum as rank,m.* from myTable m )
where rank between lower_limit and upper_limit

I think this query will work for you.
Mintoo kumar
Ranch Hand

Joined: Aug 21, 2007
Posts: 61
Thanks Madhavi , I got it.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Query to fetch 100 records in 1000
 
Similar Threads
Hibernate fetch size
preparedStatement.executeQuery() not returning , not even showing any error
number of results
Regarding fetching the records based on a fetchsize limit
JDBC ResultSet