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(); } }
}