Re: large query by offset and limt

Ge Cong wrote:
> Thank you very much. Could you show me how to do it in JDBC?

Here's one example. As I haven't been using JDBC directly it's probably 
horrible, but it'll do the job. Any exception will terminate this 
example, but in practice you'd want to catch and handle exceptions 
appropriately.

Sorry about the ugly formatting - mail client line wrapping and all.

The example uses a dummy "customer" table, scrolling through it in 
chunks of 1000 records and printing the primary key `id' for each record.

----
im****t java.sql.Connection;
im****t java.sql.DriverManager;
im****t java.sql.ResultSet;
im****t java.sql.SQLException;
im****t java.sql.Statement;

public class Main {

     private static final int BATCH_SIZE = 1000;

     public static void main(String[] args)
             throws ClassNotFoundException, SQLException {

         // Load the JDBC driver
         Class.forName("org.postgresql.Driver");

         // Initialize a read only connection
         Connection c = DriverManager.getConnection(
             "jdbc:postgresql:DBNAME", "USERNAME", "PASSWORD");
         c.setReadOnly(true);
         c.setAutoCommit(false);

         // Declare an open cursor attached to a query for the
         // desired information
         Statement s = c.createStatement();
         s.execute("DECLARE customer_curs CURSOR FOR"
                   + " SELECT id FROM customer");

         // and fetch BATCH_SIZE records from the cursor until fewer
         // than the requested number of records are returned (ie
         // until we've run out of results).
         int nresults = 0;
         do {
             s.execute("FETCH " + BATCH_SIZE + " FROM customer_curs");
             ResultSet rs = s.getResultSet();
             while (rs.next()) {
                 nresults++;
                 // Do something with the current record at `rs'
                 System.out.println("CustomerID: " + rs.getString(1));
             }
         } while (nresults == BATCH_SIZE);	

         // Clean up.
         c.close();
     }

}
----

-- 
Sent via pgsql-general mailing list (pgsql-general@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general