This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Statement.setMaxRows Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Statement.setMaxRows" Watch "Statement.setMaxRows" New topic


N Naveen

Joined: Sep 04, 2003
Posts: 27
I have a few doubts.
I have some sql queries which fetch a lot of data from a oracle DB.
I wish to use the statement object's setMaxRows() method to restrict
the number of records fetched.
I wish to know the following.
If the query fetches more than say 200 records set through the
setMaxRows method,will the truncation of the excess records happen
at the database server or the java application/app server ???
Also,will the query fetch more than the no of records set ???
(of course the query fetches more than 200 records in oracle )
Pls reply to this asap.
Thanks in advance.
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

All thats doing is (potentially) reducing the size of the ResultSet object. It won't affect the speed of the query. setMaxRows doesn't change the actual SQL - using top/limit/rownum e.g. - so it doesn't change the work the DB does. The query will return more results than your limit if there are more results to return, then truncate them to fit your ResultSet. This is also how top/limit/rownum work anyway.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
N Naveen

Joined: Sep 04, 2003
Posts: 27
Thanks for the reply.
Where does the truncation happen ?
Does it happen at the Application/App Server
or at the database server ?
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

Its of the ResultSet object. So it its done in your Java application.
Rahul Babbar
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
I was just curious...
Why doesn't it happen at the database level....
ie what does ResultSet stand to gain by fetching all the records and then truncating to maxRows, isn't it better that it changes the sql and modifies its rownum and gets only those number of records which are fetched...

Rahul Babbar
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
subject: Statement.setMaxRows
Similar Threads
records between sequence numbers
ORA-01002 fetch out of sequence
Problem with Thread..
Significance of ResultSet.setFetchSize()
Query regarding display tag pagination