Re: Limiting Large Result Sets

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 30 Aug 2006 12:57:04 -0700
Message-ID: <1156967824.840787.255590@m79g2000cwm.googlegroups.com>

smartnhandsome_at_gmail.com wrote:
> So the user gets back a result for the first 10 rows, now the user
> again requests another set of 10 rows. This method is again called and
> here is the real problem starts he now wants to get results from 11-20.
> Simply put the question is how to maintain the state of the results
> with out modifying the query the user inputs??

Hi smartnhandsome (LOL, that's pretty good!),

Why can't you do something like the following?

import java.sql.*;

public class getRows {
  public static void main(String args[]) {     try {

      Class.forName("oracle.jdbc.driver.OracleDriver");
      Connection conn =
DriverManager.getConnection("jdbc:oracle:thin:@myhost:myport/myservice","foo","foo");
      conn.createStatement().executeUpdate("alter session set events
'10046 trace name context forever, level 12'");
      PreparedStatement pstmt = conn.prepareStatement("select * from
big_table");
      pstmt.setMaxRows(1000);
      ResultSet rst = pstmt.executeQuery();
      int i = 1;
      while (rst.next()) {
        if ( ++i % 100 == 0 ) { //fetch 100 rows a time
          System.out.println(i);
          Thread.sleep(3000); //wait for a sleeper thread that is

posted when the user sends it something before moving on to the next set of rows up to 1000 or whatever.

        }
      }

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

Regards,

Steve Received on Wed Aug 30 2006 - 14:57:04 CDT