Re: JDBC: Getting started with Serialization

On Wed, 22 Dec 1999, SHARMA, Vivek, GNW IT wrote:

> Hi,
>
> Is there any example code that I can look at to see how to use PostgreSQL's
> JDBC Object Serialization extensions? Would some one also please tell me
> where I can find the source code for the JDBC driver? I have tried hunting
> through the postgres site and through Peter's site but can not find any
> links to it.

This is a common question. The source is in the src/interfaces/jdbc
directory of the main postgresql source.

As for serialisation, theres an example in the examples subdirectory.

> Basically I am trying to work out whether this functionality would reduce
> the amount of object-to- relational database mapping code that I would have
> to write. However my attempts use the postgresql.util.Serialize class has
> not been successful -- I am having a hard time following the documentation
> provided.
>
> So far I have tried various permutations based on:
>
> * Instantiating an object of type postgresql.util.Serialize. But when I use
> the constructor Serialize(Connection db, String X) I get a "class X not
> found" exception.

Did you call create first?

> * Trying to use the create(Connection db, Class c) or create(Connection db,
> Object o) methods I just get a null pointer exception.

Can you send me some more details on this please.

> I would really appreciate any pointers on how to use this functionality.

Ok, its been a while since I looked at this part of the code, so here
goes:

First you have your class which implements Serializable (see Trick 1
below).

Then you call the postgresql.util.Serialize.create() method. This creates
the tables needed to store your class. It uses a simple translation trick
to map Java class names to PostgreSQL table names.

Now you can use the store and get methods to retrieve your object based on
an oid. The best place to do this is to use the
PreparedStatement.setObject() and ResultSet.getObject() methods, as these
create the Serialize instance automatically.

Trick 1: Have your class have the following variable:

public long oid;

When you store an object, and this value is 0, then a new instance is
created. If it's not, then the instance in the database who's oid matches
it is replaced with it.

When you retrieve an object, the oid of the row is placed in this
variable, so a future store updates the database copy.

To duplicate an object in the database, simply retrieve it, set oid=0 then
store it.

If you don't define oid, then a new instance in the database is created
every time you call store, ie entries in the database are effectively
read-only.

Trick 2: create() does not create any indexes so you may want to create
them to help in any queries.

Trick 3: If you use the oid trick in #1 above, then you should index oid
in each table to improve retrieval.

Trick 4: If you have several classes, which are all refered to by another,
then call create on the main one. It will recurse down. However, it may
fail if you have a circular link (ie class a refers to class b, and class
b refers to class a).

Trick 5: To refer to a serialised class' table in a normal table:

package mypackage;
public class myclass implements Serializable
{
public long oid;
public String string;
}

Calling create should create a table mypackage_myclass.

Now you have a table called classes:

create table classes (cls mypackage_myclass,id serial);

Here, cls maps to the oid value in mypackage_myclass, so if you query
against classes and use ResultSet.getObject("cls"), the stored instance of
mypackage.myclass is returned.

Similarly PreparedStatement.setObject("cls",obj) will serialise obj and
store it's oid in cls.

PS: Defining cls as mypackage_myclass tells getObject() what class to
load, so although postgresql will work with oid, it won't work with
Serialize.

Finally, this will be better documented by the next release :-)

Peter

--
Peter T Mount peter(at)retep(dot)org(dot)uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf