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(at)hddesign(dot)com
license: free
feedback: I'd love some, especially any corrections or improvements.

The table whose name is passed in the constructor must have
fields called "id" and "object". It's fine if the table has other fields.

NOTE: Because transactions are used, each Shelf instance must have
exclusive access to its connection.
ie con must not be shared.

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
) throws SQLException, 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 store object
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 reached and 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);
ObjectInputStream ois = 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 explicitly cast 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(); }
}
}