An example of Large Objects and JDBC

Hey All, With all the discussion of large objects and JDBC I thought a fully functional
example would be handy.  Please forgive the length of this post, I wanted to
include enough documentation to be useful. Listed below is the Shelf class, then below that is a demo class which
shows it in action.  Obviously make sure the Postgres JDBC driver is in your
classpath before testing. Note: in my recent reply to Gabriel I attached a Shelf class.  This one is simpler
and better documented.  Throw the other one out.
Have Fun, Dave

/* Shelf encapsulates the process of serializing a class    to a table in a postgresql database.
 author: David Huttleston Jr 11/15/1999 mailto: dhjr@hddesign.com license: free feedback: I'd love some, especially any
correctionsor improvements.
 
 The table whose name is passed in the constructor must have fields called "id"  and "object".  It's fine if the table
hasother fields.
 
 NOTE: Because transactions are used, each Shelf instance must have exclusive access to its connection. ie con must not
beshared.
 
 The schema below is an example of a good shelf table:
 CREATE TABLE "tbl_objectshelf" (   "id" int4 PRIMARY KEY,   "object" oid );*/

import java.sql.*;
import java.io.*;
import java.util.*;

public class Shelf
{ public Shelf(   String driver,   String dsn,   String username,   String password,   String table   ) throws
SQLException,ClassNotFoundException {   this.setConnection(driver, dsn, username, password, table ); }
 
 public Shelf(Connection newCon, String table)   throws SQLException {   this.setConnection(newCon, table); }
 public void setConnection(   String driver,   String dsn,   String username,   String password,   String table   )
throwsSQLException, ClassNotFoundException {     Class.forName(driver);     Connection con =
DriverManager.getConnection(dsn,username, password );     this.setConnection(con,table); }
 
 public void setConnection(Connection newCon, String table) throws SQLException {   con = newCon;
con.setAutoCommit(false);
   String sqlPut = "insert into \"" + table + "\" (id, object) values (?,?)";   psPut = con.prepareStatement(sqlPut);
   String sqlGet = "select object from \"" + table + "\" where id = ?";   psGet = con.prepareStatement(sqlGet); }
 public void putObject(Integer key, Object object)   throws SQLException, IOException {     // create temporary sink to
storeobject     ByteArrayOutputStream baos = new ByteArrayOutputStream();
 
     // fill temporary sink with serialized object     ObjectOutputStream oos = new ObjectOutputStream(baos);
oos.writeObject(object);
     // create source out of the temporary sink     byte[] sink = baos.toByteArray();
     // NOTE: pg jdbc driver does not support streams yet     // ByteArrayInputStream bais = new
ByteArrayInputStream(sink);
     // send everything to the database     con.clearWarnings();     psPut.clearParameters();     psPut.setInt(1,
key.intValue());    psPut.setBytes(2, sink);
 
     // NOTE: pg jdbc driver does not support streams yet     // psPut.setBinaryStream(2, bais, sink.length);
     psPut.executeUpdate();     con.commit();     // NOTE: if an exception is thrown     //   the commit will not be
reachedand the     //   transaction will be rolled back by default }
 
 public Object getObject(Integer id)   throws SQLException, IOException, ClassNotFoundException {
con.clearWarnings();  psGet.clearParameters();   psGet.setString(1, id.toString() );   ResultSet rs =
psGet.executeQuery();  Object obj = null;
 
   if (rs != null)   {     if ( rs.first() )     {       InputStream is = rs.getBinaryStream(1);
ObjectInputStreamois = new ObjectInputStream(is);       obj = ois.readObject();       ois.close();       is.close();
}     rs.close();   }   return obj; }
 
 public void close() throws java.lang.Throwable {   psPut.close();   psGet.close();   con.close(); }
 protected void finalize() throws java.lang.Throwable {   this.close();   super.finalize(); }
 Connection con; PreparedStatement psPut; PreparedStatement psGet;
}

/* TestShelf is a demo of the Shelf class
 NOTE: run this schema in myDatabase to create tbl_objectshelf CREATE TABLE "tbl_objectshelf" (   "id" int4 PRIMARY
KEY,  "object" oid );
 
*/

import java.util.*;

public class TestShelf
{ public static void main(String[] args) {   try   {     Shelf shelf = new Shelf(       "org.postgresql.Driver",
"jdbc:postgresql://localhost/myDatabase",      "myUserName",       "myPassword",       "tbl_objectshelf"       );
 
     // build an sample object to save on the shelf     ArrayList stuff = new ArrayList();     stuff.add( new
Integer(1));     stuff.add( new String("my 2nd thing") );
 
     // create an integer to use as primary key     //   you must change this integer each time you run     //   this
example,otherwise postgres will complain about     //   a duplicate primary key     Integer key = new Integer(2);
 
     // save stuff on the shelf     shelf.putObject(key, stuff);
     // restore the stuff into a new ArrayList     // since Shelf.getObject() returns a generic object     // you must
explicitlycast it into the proper form     ArrayList restoredStuff = (ArrayList) shelf.getObject(key);
 
     // prove that the stuff is restored     System.out.println(       " The second item within stuff: " +
(String)restoredStuff.get(1)       );   } catch(Exception x) { x.printStackTrace(); } }
 
}