Date: Fri, 6 Oct 2000 23:40:39 -0500
Message-Id: <200010070440.XAA03622@proxy.hddesign.com>
To: pgsql-interfaces@postgresql.org
From: David Huttleston Jr <dhjr@hddesign.com>
Reply-To: dhjr@hddesign.com
Subject: 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 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(); }
  }
}