Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Getting First n Records

 
Deepika Saxena
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
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65111
89
IntelliJ IDE Java jQuery Mac Mac OS X
  • 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
Author and ninkuma
Marshal
Pie
Posts: 65111
89
IntelliJ IDE Java jQuery Mac Mac OS X
  • 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
Author and ninkuma
Marshal
Pie
Posts: 65111
89
IntelliJ IDE Java jQuery Mac Mac OS X
  • 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
 
Balu Sadhasivam
Ranch Hand
Posts: 874
Android Java VI Editor
  • 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
Author and ninkuma
Marshal
Pie
Posts: 65111
89
IntelliJ IDE Java jQuery Mac Mac OS X
  • 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
 
Amol Pingate
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.
 
Charbel Keyrouz
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);
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic