For this one, I asked my resident java guru Mark Piermarini (mbpierma@us.oracle.com) to come up with an example. Below are two examples he came up with -- the first with minimal error checking and code shows an example of

o creating an object
o serializing it into the database
o reading it back out
o printing it.


The second, more robust example with comments is a little more flexible. It will serialize a java instance into the database if passed no inputs. After it puts it in -- it'll print out the "id" for that object instance. Later, you can run this same java example and pass it that id -- it'll read out that object instance and print it out, showing how to make this work across invocations.

Things you will need to run this example in this case are:


create sequence java_obj_seq;

create table java_objects(
id number,
classname varchar2(2048),
bytes blob default empty_blob()
)

We use the sequence to create a "primary key" for out object instances. We need this number later to retrieve a specific instance. The table is what we use to store the object instance. Here we store the classname of the original java instance in a varchar2 field and in the BLOB we'll store the bytes of the serialized object.

Here is the first small example:


import java.lang.*;
import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;

public class serial2 {
static String genID =
"select java_obj_seq.nextval from dual";

static String writeSQL =
"begin insert into java_objects(id,classname,bytes) "+
" values (?,?,empty_blob()) " +
" return bytes into ?; end;";

static String readSQL =
"select bytes from java_objects where id = ?";

public static long write(Connection conn, Object o)
throws Exception
{
long id = nextval(conn);
String className = o.getClass().getName();
CallableStatement stmt = conn.prepareCall(writeSQL);
stmt.setLong(1, id);
stmt.setString(2, className);
stmt.registerOutParameter(3, java.sql.Types.BLOB);
stmt.executeUpdate();
BLOB blob = (BLOB) stmt.getBlob(3);
OutputStream os = blob.getBinaryOutputStream();
ObjectOutputStream oop = new ObjectOutputStream(os);
oop.writeObject(o);
oop.flush();
oop.close();
os.close();
stmt.close();
System.out.println("Done serializing " + className);
return id;
}

public static Object read(Connection conn, long id)
throws Exception
{
PreparedStatement stmt = conn.prepareStatement(readSQL);
stmt.setLong(1, id);
ResultSet rs = stmt.executeQuery();
rs.next();
InputStream is = rs.getBlob(1).getBinaryStream();
ObjectInputStream oip = new ObjectInputStream(is);
Object o = oip.readObject();
String className = o.getClass().getName();
oip.close();
is.close();
stmt.close();
System.out.println("Done de-serializing " + className);
return o;
}

private static long nextval(Connection conn)
throws SQLException
{
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(genID);
rs.next();
long id = rs.getLong(1);
rs.close();
stmt.close();
return id;
}

public static void main(String[] argv)
throws Exception
{
String cs = "jdbc:oracle:oci8:@ora8idev";
String user = "scott";
String pass = "tiger";

DriverManager.registerDriver(new OracleDriver());
Connection conn =
DriverManager.getConnection(cs, user, pass);
conn.setAutoCommit(false);

LinkedList l = new LinkedList();
l.add("This");
l.add("is");
l.add("a");
l.add("test");
l.add(new Long(123123123));
l.add(new java.util.Date());

long id = write(conn, l);
conn.commit();

System.out.println("ID= " + id);
System.out.println("Object= " + read(conn, id));
conn.close();
}
}

Now, if we run the code:

$ /usr/java1.2/bin/java serial2
Done serializing java.util.LinkedList
ID= 1
Done de-serializing java.util.LinkedList
Object= [This, is, a, test, 123123123, Sun Jan 21 09:45:21 EST 2001]


So, according to that, the object has been serialized and stored in the database. In fact, we can log in using sqlplus and see it if we like:


scott@DEV816> create or replace function clean( p_raw in blob )
return varchar2
2 as
3 l_tmp long default utl_raw.cast_to_varchar2(
dbms_lob.substr(p_raw,2000,1));
4 l_char char(1);
5 l_return long;
6 begin
7 for i in 1 .. length(l_tmp)
8 loop
9 l_char := substr( l_tmp, i, 1 );
10 if ( ascii(l_char) between 32 and 127 )
11 then
12 l_return := l_return || l_char;
13 else
14 l_return := l_return || '.';
15 end if;
16 end loop;
17 return l_return;
18 end;
19 /

Function created.

scott@DEV816>
scott@DEV816> select id, classname,
2 dbms_lob.getlength(bytes) len,
3 clean(bytes) clean
4 from java_objects
5 /

ID CLASSNAME LEN CLEAN
---------- -------------------- ---- --------------------
1 java.util.LinkedList 191 ....sr..java.util.Li
nkedList.)S]J`."...x
pw.....t..Thist..ist
..at..testsr..java.l
ang.Long;.....#....J
..valuexr..java.lang
.Number...........xp
.....V..sr..java.uti
l.Datehj..KYt....xpw
.....1...xx

So, we see now how to serialize using a BLOB as the input stream and deserialize using the BLOB again. We can also review the contents (sort of) in SQLPlus.

UTL_RAW might not be installed on your system -- it comes with
Oracle7.1.6 and up. To install if you do not have it:

o cd $ORACLE_HOME/rdbms/admin
o connect as SYS or INTERNAL only using SVRMGRL
o @utlraw
o @prvtrawb.plb


Now for the longer, more robust example with error handling and comments. You have all you need above -- the following is just a little more sophisticated. After you compile this:

$ /usr/java1.2/bin/javac serial.java

we can run:

$ /usr/java1.2/bin/java serial 1
Done de-serializing java.util.LinkedList
Object= [This, is, a, test, 123123123, Sun Jan 21 09:45:21 EST 2001]

(after running the first example serial2.java that is). All this did was read out our existing java instance and "rebuild it" for us and then print it out. You can run serial without any arguments to create new instances of the linked list and read them back out by ID later. Here is the code:

import java.io.*;
import java.lang.*;
import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;

public class serial {
static String genID =
"select java_obj_seq.nextval from dual";

static String writeSQL = "begin insert into java_objects "+
" (id,classname,bytes) values (?,?,empty_blob()) "+
" return bytes into ?; end;";

static String readSQL =
"select bytes from java_objects where id = ?";

// We attempt to serialize the object to the database table.
// We use a
// sequence number to identify the object and return that
// value to the
// caller. We also save the class name in case someone wants
// to search
// for objects based on that name. If we get any exception,
// we rethrow
// it to the caller but we always try to clean up after
// ourself.

public static long write(Connection conn, Object o)
throws Exception
{
ObjectOutputStream oop = null;
CallableStatement stmt = null;
OutputStream os = null;
long id = nextval(conn);
String className = o.getClass().getName();

try {
stmt = conn.prepareCall(writeSQL);
stmt.setLong(1, id);
stmt.setString(2, className);
stmt.registerOutParameter(3, java.sql.Types.BLOB);
stmt.executeUpdate();
BLOB blob = (BLOB) stmt.getBlob(3);
os = blob.getBinaryOutputStream();
oop = new ObjectOutputStream(os);
oop.writeObject(o);
oop.flush();
} catch (Exception e) {
throw e;
} finally {
if (oop != null) { oop.close(); }
if (os != null) { os.close(); }
if (stmt != null) { stmt.close(); }
System.out.println("Done serializing " + className);
}

return id;
}

// We attempt to de-serialize the object from the database
// table using
// the given identifier. If we get any exception, we rethrow
// it to the
// caller but we always try to clean up after ourself.

public static Object read(Connection conn, long id)
throws Exception
{
ObjectInputStream oip = null;
PreparedStatement stmt = null;
InputStream is = null;
Object o = null;
String className = null;

try {
stmt = conn.prepareStatement(readSQL);
stmt.setLong(1, id);
ResultSet rs = stmt.executeQuery();

// Even though we expect only one row back, the caller
// could have
// passed an invalid identifier so we wrap this in a
// loop to
// make sure we don't get null pointer exceptions. In
// the case
// where there are 0 rows, we would return a null
// value. Where
// there are > 1 rows, we would return only the first
// one.

while (rs.next()) {
is = rs.getBlob(1).getBinaryStream();
oip = new ObjectInputStream(is);
o = oip.readObject();
className = o.getClass().getName();
break;
}
} catch (Exception e) {
throw e;
} finally {
if (oip != null) { oip.close(); }
if (is != null) { is.close(); }
if (stmt != null) { stmt.close(); }
System.out.println("Done de-serializing " + className);
}

return o;
}

// Get the next sequence value. Ideally, this would be better
// if
// we just created a prepared statement and repeatedly used
// that.
// Obviously, we must assume the same connection is always
// used.

private static long nextval(Connection conn)
throws SQLException {
long id = -1;
Statement stmt = null;
ResultSet rs = null;

try {
stmt = conn.createStatement();
rs = stmt.executeQuery(genID);

while (rs.next()) {
id = rs.getLong(1);
}
} catch (SQLException e) {
throw e;
} finally {
if (rs != null) { rs.close(); }
if (stmt != null) { stmt.close(); }
}

return id;
}

// Here we just simply connect to the database and either
// serialize or de-serialize an object. If we get an
// exception then we will rollback whatever we have done.
// Commit changes as necessary and close the connection.
public static void main(String[] argv) throws Exception {
long id;
Connection conn = null;
String cs = "jdbc:oracle:oci8:@ora816dev";
String user = "scott";
String pass = "tiger";

try {
DriverManager.registerDriver(new OracleDriver());
conn = DriverManager.getConnection(cs, user, pass);
conn.setAutoCommit(false);

if (argv.length == 0) {
id = write(conn, new java.util.Date());
conn.commit();
System.out.println("ID= " + id);

LinkedList l = new LinkedList();
l.add("This");
l.add("is");
l.add("a");
l.add("test");
id = write(conn, l);
conn.commit();
System.out.println("ID= " + id);
} else {
id = Long.parseLong(argv[0]);
Object o = read(conn, id);
System.out.println("Object= " + o);
}
} catch (Exception e) {
e.printStackTrace();
if (conn != null) { conn.rollback(); }
} finally {
if (conn != null) { conn.close(); }
}
}
}