• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Getting First n Records

 
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Deepika Saxena
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
No Bibeault, I am not using any ORM Framework. I am just using JDBC.

Thanks.
--Deepika.
 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sorry Bibeault.. it was a typo mistake.
--Deepika
 
Ranch Hand
Posts: 874
Android VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
No Bibeault, i just wanted to know all the possible ways to achieve this. This could be very simple thru SQL.
Thanks.
--Deepika
 
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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.
 
Ranch Hand
Posts: 46
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
before you enter the while(rs.next()) loop do the following:

rs.setFetchSize(nbrows);
 
Oh, sure, you could do that. Or you could eat some pie. While reading this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic