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.

----
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import 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();
}

}
----