Re: JDBC and processing large numbers of rows

David Wall wrote:
> Thanks, Dave. Does anybody have any simple examples of the series of JDBC
> calls used to declare, open, fetch and close a cursor in PG? In Oracle? I
> know this is a PG list, so if no Oracle examples, can anybody at least
> confirm that using cursors with Oracle and standard JDBC is possible?
>
> There's nothing like having to write custom code to implement what Java
> purports to be write once, run anywhere! It seems that the JDBC spec would
> have to be severely lacking if you can't do something as simple (and old) as
> use cursors in a standard way.

I don't know -- that seems more a criticism of SQL than of JDBC. I don't
think DECLARE as a query statement exists in the SQL specs at all? The
DECLARE docs say:

> The SQL standard only makes provisions for cursors in embedded SQL. The
> PostgreSQL server does not implement an OPEN statement for cursors; a
> cursor is considered to be open when it is declared. However, ECPG, the
> embedded SQL preprocessor for PostgreSQL, supports the standard SQL
> cursor conventions, including those involving DECLARE and OPEN
> statements.

Anyway, back to your original question ..

JDBC *does* provide a standard way of using cursors to page through
result data. It's called ResultSet. Take a look at the ResultSet row
movement primitives -- they look suspiciously like cursor movement
primitives, don't they?

I'd suggest using an appropriate resultset type (SCROLLABLE_* or
FORWARD_ONLY, depending on your access patterns) and use setFetchSize()
and setFetchDirection() to hint to the driver about what you're doing.
Then just run your unmodified, cursor-less query. With a good driver
implementation you should get paging of the resultset transparently.
This is why all the different resultset types and fetch hints are there
in the first place..

The current postgresql driver will page results from the backend if you
use FORWARD_ONLY, a non-zero fetchsize, and autocommit off. It isn't
quite there yet for scrollable resultsets, but it's close (see the
recent patches from Andy Zeneski). Note that using a scrollable
resultset can be more expensive as the backend can only provide
scrollable cursors for free in some cases -- in other cases, it has to
materialize the whole resultset. This is a cost you'll end up paying
regardless of whether you're using cursors via ResultSet, cursor
manipulation directly, or LIMIT/OFFSET tricks (at least assuming you
actually want to (eventually) process all the data from a query and not
just a subset).

Then we just need holdable resultset support (for the autocommit case --
there's a parameter in JDBC3 for controlling this as holdable cursors
aren't free, especially if you have very large result sets) and it'd be
all transparent.

I don't know how Oracle handles all this, but hiding the cursor work
inside the ResultSet seems like the Right Way to do it.

-O