Re: JDBC Blob helper class & streaming uploaded data into PG

[Kris Jurka <books(at)ejurka(dot)com>]

On Wed, 4 Feb 2009, David Wall wrote:

> Does anybody have a JDBC Blob helper class so we can use the 
> setBlob()/getBlob() calls in JDBC for PG 8.3?  It would be a class that 
> implements the java.sql.Blob interface.

You really ought to use the driver's Blob implementation.  Right now 
creating a new Blob isn't terribly straightforward.  In theory the JDBC 4 
method Connection.createBlob should be used, but that has not been 
implemented in the postgresql driver yet.

The best way to do this at the moment is to insert a row with an empty 
blob, retrieve that blob and then write data into it.

CREATE TABLE test (id int, bigdata oid);

INSERT INTO test VALUES (1, lo_creat(-1));

ResultSet rs = stmt.executeQuery("SELECT bigdata FROM test WHERE id = 1");
rs.next();
Blob blob = rs.getBlob(1);
OutputStream out = blob.setBinaryStream(1);
// from java.util.zip. to compress the data.
GZIPOutputStream gz = new GZIPOutputStream(out);
while (!done) {
	gz.write(your data);
}
gz.close();
rs.close();

Kris Jurka