Skip site navigation (1) Skip section navigation (2)

Re: JDBC and processing large numbers of rows

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: David Wall <d(dot)wall(at)computer(dot)org>
Cc: pg(at)fastcrypt(dot)com, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: JDBC and processing large numbers of rows
Date: 2004-05-12 01:07:21
Message-ID: 40A178C9.5040903@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

In response to

Responses

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2004-05-12 01:15:18
Subject: Re: JDBC and processing large numbers of rows
Previous:From: Sean ShannyDate: 2004-05-12 00:55:44
Subject: Re: JDBC and processing large numbers of rows

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group