aspose file tools*
The moose likes JDBC and the fly likes Getting First n Records Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Getting First n Records" Watch "Getting First n Records" New topic
Author

Getting First n Records

Deepika Saxena
Ranch Hand

Joined: Jul 05, 2009
Posts: 59
Hi,
How can we restrict the number of results that are to be retrieved from the database? is there any way, where we can get only n number of records from the resultset without modifying the query?

say, we have 1000 records in the resultset and we want only records from 500 to 520. How can we do this from java side without?

Thanks in advance for your valuable suggessions.
--Deepika
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60997
    
  65

Most DBs has a means of doing this in a SQL statement. Each is different however.

You might want to read through the paging entry of the JSP FAQ for more information.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Deepika Saxena
Ranch Hand

Joined: Jul 05, 2009
Posts: 59
Thanks for the reply Bil.
I know that this can be easily achieved thru queries, where as we can get the records for a particular limit. Ex: in DB2 we can use FETCH FIRST N RECORDS to do this.
But i just wanted to know, is the same is possible on a reselt set , where we already got the full results and want to get only a few records.

--Deepika
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60997
    
  65

Well, I suppose you could just iterate through the first n records...

Hibernate has a way of specifying this as a Java method rather than as part of the SQL, but you've not indicated that you are using it.

Bil?
Deepika Saxena
Ranch Hand

Joined: Jul 05, 2009
Posts: 59
No Bibeault, I am not using any ORM Framework. I am just using JDBC.

Thanks.
--Deepika.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60997
    
  65

Bill?

In any case, I do not believe that there's a JDBC way to say "only fetch this many records". Someone more familiar with the more esoteric sides of JDBC may have some cleverness up their sleeve...
Deepika Saxena
Ranch Hand

Joined: Jul 05, 2009
Posts: 59
Sorry Bibeault.. it was a typo mistake.
--Deepika
Balu Sadhasivam
Ranch Hand

Joined: Jan 01, 2009
Posts: 874



Check out ResultSet setFetchSize() if that could help you. But i m not sure it could return next n ... and so on .. try it.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60997
    
  65

Right, that's what I was kind of thinking of. That gives the drive a "hint" of how many rows to fetch at a time, but is no guarantee. But it's likely the closest you'll get.

Is there any reason you don't want to be specific and specify the row count in the SQL? (Trying to keep the SQL DB-agnostic perhaps?)
Deepika Saxena
Ranch Hand

Joined: Jul 05, 2009
Posts: 59
No Bibeault, i just wanted to know all the possible ways to achieve this. This could be very simple thru SQL.
Thanks.
--Deepika
Amol Pingate
Greenhorn

Joined: Oct 02, 2008
Posts: 14


Tested With MySQL ........

you can achieve this using simple sql query

e.g. SELECT *FROM `Employee`LIMIT 0 , 10;


this will fetch first 10 employee.

if you want the employee from 10 to 20 then just give

SELECT *FROM `Employee`LIMIT 10 , 10;

this will give employee's list from 11th record .


0 ==== first record.
Charbel Keyrouz
Ranch Hand

Joined: Jun 10, 2005
Posts: 46
before you enter the while(rs.next()) loop do the following:

rs.setFetchSize(nbrows);
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Getting First n Records