This week's book giveaways are in the AI and JavaScript forums.
We're giving away four copies each of GANs in Action and WebAssembly in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of GANs in ActionE this week in the AI forum
or WebAssembly in Action in the JavaScript forum!
  • 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
  • Bear Bibeault
  • Paul Clapham
  • Jeanne Boyarsky
  • Knute Snortum
Sheriffs:
  • Liutauras Vilda
  • Tim Cooke
  • Junilu Lacar
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Joe Ess
  • salvin francis
  • fred rosenberger

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: 67340
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: 67340
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: 67340
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: 67340
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);
 
a fool thinks himself to be wise, but a wise man knows himself to be a fool - shakespeare. foolish tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!