Retrieving data with ResultSet object for a large number of records

I have a Java servlet that retrieves data and creates a text file with the retrieved data and then creates a zip file with the text file.  It works OK for a table or view with around 20,000 records but does not work for a table with say 200,000 records.  For the large table it does create the text file and writes a certain number of records but then quits without completing and no zip file.   I assume it cannot handle this many records at once.   It works OK on a server with 1G more of RAM.  

Here is my code for creating the text file:
    try
    {
      Connection cn = getConnection();
    // Prepare the query statement
    String query_str = "SELECT * FROM " + var0 + "." + var1;
    PreparedStatement ps = cn.prepareStatement(query_str);
    // Execute the query
    ResultSet rs = ps.executeQuery();
    ResultSetMetaData rsMeta = rs.getMetaData();
    int colCount = rsMeta.getColumnCount();
    // For each column in the result set print the column name
    curName = rsMeta.getColumnName(1);
    if (colCount == 1) {
        fileout.println(curName);      
    } else {
        fileout.print(curName);
    }
    for (int curCol = 2; curCol <= colCount; curCol++)
    {
      curName = rsMeta.getColumnName(curCol);
      if (curCol == colCount) {
        fileout.println("|"+curName);      
      } else {
        fileout.print("|"+curName);
      }
    }  
    // Loop over the rows in the query result
    while (rs.next())
    {
      if (rs.getString(1) == null) {
      } else {
          fileout.print(rs.getString(1));            
      }
      for (int curCol = 2; curCol <= colCount; curCol++)
      {
        if (rs.getString(curCol) == null) {
          fileout.print("|");      
        } else {
          fileout.print("|"+rs.getString(curCol));
        }
      }
      fileout.println();
    }
    rs.close();
    ps.close();
    cn.close();    
    }

    catch(Exception e)
    {
      e.printStackTrace();
    }
    fileout.close();

Is there a way to return a smaller number of records at a time (something like multiple result sets, not sure how to do this)?  I am not sure actually what the problem is.  I am assuming it has to do with too many records being retreived.  
 
perrycnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

objectsCommented:
are you getting any errors?

try increasing the fetch size to improve performance.
0
objectsCommented:
eg.

ps.setFetchSize(1000);
0
perrycnAuthor Commented:
No I am not getting any errors.   So I would put this statement before ResultSet rs = ps.executeQuery();.  I do not quite understand what this statment does.  It says:  the number of rows that should be fetched from the database when more rows are needed for this ResultSet object.  
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

objectsCommented:
So how is it currenlty failing? Are you saying rs.next() returns false even though there are more rows?

the fetch size controls how many rows at a time are retrieved from server.
0
perrycnAuthor Commented:
OK, I did put the statement, ps.setFetchSize(1000); in the code.  It is not quitting on creating the text file, before it only got to the size of around 7,000KB.  It is now around 35,000KB (file is should be around 56,000KB) and still running.  If I increased 1000 to 10000, would that make it faster?  Of course the browser timeout on the client.  The zip file is posted back to the user.  Not sure about how to keep browser from timing out if it takes along time for text file to be created.
0
perrycnAuthor Commented:
objects, Thanks for the quick response.  Do you understand why it works when you put in that statment, ps.setFetchSize(1000); but not when it is left out.  Apache which comes with Oracle 9i database release 2 is the webserver.   It just stops going thru the loop with rs.next().   It does not work on the server with 1G of RAM but does work on the server with 2G of RAM and faster processor.  Both servers are set up with the same software.  


0
objectsCommented:
sounds like the request could be timing out.
0
perrycnAuthor Commented:
Object,  Well back to this problem.  I am far from an expert.  What do you mean request timing out?  Is it the Java Servlet, web server, database, or network?   How do I figure out the problem?   I guess I should really close this and give you your points.
0
objectsCommented:
its the web server timing out the request by the sounds
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java EE

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.