• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Getting First n Records

 
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
  • 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
 
Marshal
Posts: 67451
173
Mac Mac OS X IntelliJ IDE jQuery Java
  • Mark post as helpful
  • send pies
  • 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
  • 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
Marshal
Posts: 67451
173
Mac Mac OS X IntelliJ IDE jQuery Java
  • Mark post as helpful
  • send pies
  • 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
  • Quote
  • Report post to moderator
No Bibeault, I am not using any ORM Framework. I am just using JDBC.

Thanks.
--Deepika.
 
Bear Bibeault
Marshal
Posts: 67451
173
Mac Mac OS X IntelliJ IDE jQuery Java
  • Mark post as helpful
  • send pies
  • 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
  • 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
  • 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
Marshal
Posts: 67451
173
Mac Mac OS X IntelliJ IDE jQuery Java
  • Mark post as helpful
  • send pies
  • 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
  • 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
  • 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
  • Quote
  • Report post to moderator
before you enter the while(rs.next()) loop do the following:

rs.setFetchSize(nbrows);
 
I didn't like the taste of tongue and it didn't like the taste of me. I will now try this tiny ad:
the value of filler advertising in 2020
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic