ResultSet doesn't return all the values

Question

I am loading to a database table 8000 records about the users and I am making some operations on that data and at the end I want to receive back my 8000 records with balance. My code:

public void getBalance(String database){
    Connection conn=null;
    Statement stmt=null;
    ResultSet rs=null;
    try{
         Class.forName("oracle.jdbc.driver.OracleDriver");
           conn=DriverManager.getConnection(
                "jdbc:oracle:thin:@address","user","password");
          stmt=conn.createStatement();
          rs=stmt.executeQuery("select count(*) from balance1");
          int count=0;
          while(rs.next()){
            count=rs.getInt(1);
           }
           System.out.println("The count is "+count);
           conn.setAutoCommit(false);
           stmt.setFetchSize(count);    
           rs = stmt.executeQuery("select * from balanceview");
           writeToAFile(rs);
    }catch(Exception e){
         e.printStackTrace();
     }finally{
        if(conn!=null){
            try{
                conn.close();
            }catch(Exception e){
                _logger.severe(e.getMessage());
                e.printStackTrace();
            }
        }if(stmt!=null){
            try{
                stmt.close();
            }catch(Exception e){
                _logger.severe(e.getMessage());
                e.printStackTrace();
            }
        }if(rs!=null){
            try{
                rs.close();
            }catch(Exception e){
                _logger.severe(e.getMessage());
                e.printStackTrace();
            }
        }
    }
}

but I am facing a problem only 2000 values are returned back. Is there a way to return all 8000 values.

Answer 1

JDBC will always fetches default number of rows (not the entire result set) to your local memory. Once you reach at the last line of the fetched rows (say by doing next() and try to access next row) and if there are more rows in the result, then another round-trip call will be made to the database to fetch next batch of rows to local memory.

Even you can set number of rows you want fetch in local memory than usual, you may consider CachedRowSet.

When you set the fetchSize() on the Statement, you are only giving a instruction to the JDBC driver how much you want it should fetch, but JDBC driver is free to ignore your instructions. I do not know what the Oracle driver does with the fetchSize(). Most of times its observed that MySQL JDBC driver will always fetch all rows unless you set the fetchSize() to Integer.MIN_VALUE but this is not the case with Oracle Database.