This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
A normal ResultSet data isn't retrieved until you do the next call. The ResultSet isn't a memory table, it's a cursor. Suppose the problem sorted out for the less than 1 lack records. but how to get more than that count??
how many is "1 lack"? I'm sort of familiar with "lakhs", which I assue is what you mean. However, if you write in terms that everyone will understand, eg. in 100.000 records, you'll probably get better responses.
You might be interested in the setFetchSize() method of the Statement class. This allows you to set number of rows that will be retrieved from the database in one call. Setting it to a value of about 100 migth be a good idea. I wouldn't set it too high, though. What we want is to establish a nice flow from the database to the client in chunks that can be easily processed.
Also check the performance of your SQL query. If you read all data from a single table, not much can be done to speed things up (depending on your database), but if the data are produced using a more complicated query, there might be ample space for improvements.
Apart from this, at least three more things can be done to speed things up:
1) Retrieve columns from the resultset by their position, not by name. It saves the translation from name to column position for every access.
2) Avoid creation of objects if possible. If you're reading ints from the database just to write them to a file, don't convert them to Integers. Nulls have to be handled separately in this case, however it can save substantial processing time (my own experience).
3) If the processing of obtained records takes some time, think about using producer-consumer desing pattern. You'd have one thread to extract the data from the resultset as quick as possible and then call the next() method, while another thread would process the data. That way you could be able to crunch the data (eg. format them and write to the file) while next batch is being retrieved from the database. Some fine-tuning with respect to the fetch size and queue size will be necessary. However this is quite a lot of work, so I'd probably not choose this path unless I was really desperate.
Nothing "happens to them" (whatever you meant by that). You get them in the normal way by reading them from the ResultSet. Have another look at the API documentation for the setFetchSize method as I think you may have misunderstood it.