Re: A ResultSet related question

[Kevin Chipalowsky <kchipalowsky@yahoo.com>]

Any intelligent JDBC driver implementation, including
the Oracle driver you're using, will not try to bring
all rows in a result set into memory at the same time.
 This means that as long as you only iterate forward,
you will not reach any row count limit.

As for your specific problem, EJB solves it by
converting all returned rows into objects, and then
returning a Collection.  This, of course, means that
all rows will be held in memory at the same time,
which probably won't work for 100,000+ rows.

I would suggest you consider refactoring your code to
avoid this problem altogether.

1. If you are writing code to drive a UI, consider
breaking up the query size into much smaller chunks
that easily fit in memory all at the same time.

2. If you are processing the rows one at a time,
consider refactoring your code into something like
this:

public void queryAndProcess(
               ProcessorInterface myProcessor ) {
  // Execute query, get result set
  // . . .

  while( resultSet.next() ) {
    myProcessor.processOneRow( resultSet );
  }

  // Close result set, statement, and connection
}

3. If your processing requires looking at more than
one row at a time, rethink your algorithm.  There is
probably a way to implement it by only examining a
single row at a time.


Good luck, and I hope my 2 cents helps!





--- KUMARAN MANI <kumaranm@yahoo.com> wrote:
> Hello All,
> 
> Hope you are all having fun. I am trying to have
> fun,
> but however this resultset thing is unclear for me.
> So
> I am asking for your help. 
> 
> Here is the question:
> 
> I have a table in the Oracle DB, which contains ,
> say
> 100000 + records. I have to do a "SELECT * from
> table". and I get a resultSet back. 
> 
> Questions:
> 
> 1.) Is result set capable of holding onto 100000+
> records? What is the limit, if any?
> 
> 2.) Is there any memory requirement that I need to
> consider because of the no. of records in ResultSet?
> 
> 3.) If I assume, ResultSet (the oracle Java class)
> is
> intelligent enought to cache a limited set of
> records
> say 1000 at a time and then based on
> resultSet.next(),
> it will go back to the table and get the next 1000
> records and so on. Is this true?
> 
>     3.5) If the above is true? then please read the
> code below; and my question are in "XXX" in the
> code;
> 
> conn= jdbcPool.getConnection();
>     if (conn != null) {
>       pStmt= conn.prepareStatement(selectStmt);
>       rs= pStmt.executeQuery();
>     }
>   } finally {
>     try {
>       //XXX1: If I close the statement, the
> resultset
> is not accesible anymore outside this
> method..meaning,
> rs.next would not work! Why?
>       //if (pStmt != null)
>         //pStmt.close();
> 
> // XXX 2: If I close the connection here, and try to
> access the resultset outside this method, will it be
> a
> problem for 100000+ records?
>       if (jdbcPool != null && conn != null)
>         jdbcPool.returnConnection(conn);
>     } catch (Exception ex) {
>     }
>   }
>   return rs;
> 
> 
> 4.) In general, is there any better way to handle
> large number of records situation? Is ResultSet the
> only way?
> 
> 
> Thanks for any help. I appreciate it. 
> 
> Kumaran
> 
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Finance - Get real-time stock quotes
> http://finance.yahoo.com
> 


__________________________________________________
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com