How to fetch more than 10,000 records from database using ResultSet (Java)

[vikas khengare]

Hello Friends....
I have some problems regarding JDBC.

If My database has More than 10,000 records in some tables.
Then while retrieving (Fetch) them in one go using ResultSet object in
java it gives me OutOfMemory Exception. But if i want to fetch 8,000
records,then it is fetching very well.

My Problem is ==> How do I get access(Fetch) to those records using
ResultSet object which has more than 10,000 in numbers ?

I know there is some method called "getFetchSize" &"setFetchSize".
But I don't know how to use them in combination with SQL and ResultSet
object.

I hope that I will get some solution from Experts like you people.

Thanks.

Best Regards
[ (e-mail address removed) ]

 

[Gerbrand van Dieijen]

vikas khengare schreef:

Hello Friends....
I have some problems regarding JDBC.

If My database has More than 10,000 records in some tables.
Then while retrieving (Fetch) them in one go using ResultSet object in
java it gives me OutOfMemory Exception. But if i want to fetch 8,000
records,then it is fetching very well.

If you create a Statement of type Resultset_Fetch_forward (see
connnection.createStatement(...) ), the databasedriver or database
itself will discard rows that are already read.
This depends on the database and databasedriver. I believe Mysql will by
default retrieve a resultset at once server-side, but maybe with
fetch_forward the data will be read row by row client-side (via jdbc).

See documentation of your database for mor information.

 

[Alun Harford]

Hello Friends....
I have some problems regarding JDBC.

If My database has More than 10,000 records in some tables.
Then while retrieving (Fetch) them in one go using ResultSet object in
java it gives me OutOfMemory Exception. But if i want to fetch 8,000
records,then it is fetching very well.

My Problem is ==> How do I get access(Fetch) to those records using
ResultSet object which has more than 10,000 in numbers ?

java.sql.Resultset is an interface - it's not a class.
Your problem is due to a particular implementation of that class, so it's
hard to help you unless you say which database driver / database system
you're having the problem with.

Alun Harford

 

[vikas khengare]

Hi Alun

In my table I have More than 10000 Records may be they are in
some lakhs......
I want all of them But one bye one. When I fire SELECT query then
Whether I will get all those lakhs number of record or If i get then
whether I will be able to traverse through that records by using single
ResultSet intherface in JAVA.
I am using pure Java driver for SYBASE.

Thanks
Best Regards
(e-mail address removed)

 

[Roedy Green]

In my table I have More than 10000 Records may be they are in
some lakhs...

for those of you not familiar with Indian units of measure:

one lakh is 100,000.
one crore is 10,000,000

 

[Roedy Green]

I want all of them But one bye one. When I fire SELECT query then
Whether I will get all those lakhs number of record or If i get then
whether I will be able to traverse through that records by using single
ResultSet intherface in JAVA.
I am using pure Java driver for SYBASE.

I think the question you are asking is when I ask for large numbers of
records in a result set, do I get the first one right away, or do I
have to wait until it has got them all? You also might be asking if I
abort the query part way through will it still go get the rest?

The answers are YES, and NO.

It may only get 20 at a time or so and not get the next twenty until
you have consumed the previous 20, rather than getting ahead.

 

[vikas khengare]

Hi Roedy....
Thanks for reply...........

No..... I can proceed with few of them..... But before first
slot gets end I want second slots in my ResultSet.

I want all records in every condition So I have to travel all records.
There is no condition that I will get out in middle and I wont req.
rest of them; So I want all records.................

I want to just read all records Not to do Updarion in that........

So slot may be 20 / 200 / 2000 No problem........ But Delay wont
required....

Best Regards
(e-mail address removed)
(e-mail address removed)

 

[Roedy Green]

No..... I can proceed with few of them..... But before first
slot gets end I want second slots in my ResultSet.

there is nothing in the SQL standard about how it has to work. You
can figure it out by testing your candidate database with code that
reads one field of each result set record and immediately grabs the
next. Record the times to get each record and plot a histogram of the
distribution. From that you can deduce what is likely going on under
the hood.

See http://mindprod.com/jgloss/timeh.html

 

[Thomas Schodt]

Hi Alun

In my table I have More than 10000 Records may be they are in
some lakhs......
I want all of them But one bye one.

When you say you want the records one-by-one,
if you are keeping references to all the records you already read
that will give an OutOfMemoryException (given enough data).

 

[jcsnippets.atspace.com]

When you say you want the records one-by-one,
if you are keeping references to all the records you already read
that will give an OutOfMemoryException (given enough data).

There is a rather easy solution to avoid an OutOfMemoryException when
loading tables with an enormous amount of records/data, on the condition
that the table has a unique key (the primary key for example).

Let's assume the primary key field is called "seq".
First you select the first row:
select * from table where seq = (select min(seq) from table);

For each following row, you select:
select * from table where seq = (select min(seq) from table where seq >
previousSeq);

Not a pretty way of doing things, and it will hurt performance a lot, but it
works.

JC