Skip to Main Content
  • Questions
  • Serializing Java Objects into the database (and getting them back out)

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, venkat.

Asked: January 17, 2001 - 1:29 pm UTC

Last updated: January 04, 2010 - 10:25 am UTC

Version: version 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi,

How do I serialize Java objects into Oracle database and vice-versa.

thanks & regards
Venkat Vudithyala

and we said...

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



Rating

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

"Serializing Java Objects into the database (and getting them back out)", version version 8.1.6

Brad Bittiker, April 30, 2001 - 8:03 pm UTC

Excellent. This is exactly what I was looking for.

Very Useful but .....

Damien Michalosky, May 31, 2002 - 11:28 am UTC

It is a great alternative to what I am actually looking for. I wanted to write a Java Stored Function that returns a Java Object. It looks like that is not possible for my research but serializing an object created by a Java Stored Procedure is possible and can be retrieved by an outside class. Any other ideas? Please post or email me.

Tom Kyte
May 31, 2002 - 1:02 pm UTC

regardless of the method -- you will have to have a serializable class (we are going from JVM to JVM after all -- not within a single JVM)

So, given that, I think this would be the most straight forward way. You can use a temporary blob instead of a table and have your java stored procedure return a blob. Your java client code will

a) connect
b) run the java stored procedure
c) get the blob back to the java client
d) deserialized it

How about serialized object to stored java procedure?

Chris Bruun, October 18, 2002 - 7:40 pm UTC

This example is great for updating an object table. How can serialized objects be passed to and deserialized by a java stored procedure?

Tom Kyte
October 18, 2002 - 8:01 pm UTC

Well, you could just use this technique -- the caller would serialize into a LOB (Temporary lob for example) and pass the LOB locator to the java stored proc.

java object

Phil, March 12, 2004 - 9:27 am UTC

is it possible to

pass a Java object to an Oracle stored procedure, the object would be compiled from an XML schema and would hold the content of an XML file. To do something ( eg create a record) a stored procedure would then extract the relevant values from the object, presumably they would be accessible as properties, then process the values ????

not possible, totally foolish ?

thanks

Tom Kyte
March 12, 2004 - 9:57 am UTC

you could theoretically serialize the java object into a blob, send it to the database, unserialized it but....

I don't see the tie in between a java "object" (instance) and XML?

If you have XML, just send XML to the db -- lots of capabilities in there to process it.

</code> http://otn.oracle.com/pls/db92/db92.docindex?remark=homepage#index-XML <code>

phil, March 12, 2004 - 10:32 am UTC

I want to validate the the xml against an xml schema first - is that possible in Oracle?

"read the doco phil!!"


thanks

HTT and Stamps

Yves Bergeron, July 05, 2004 - 1:18 pm UTC

Hi,

I'm currently working on a new PL/SQL bridge with the Stamps framework on which Matthew S. Piermarini worked (is Mark the brother of Matthew ???).

I used to work with HTT but it's a bit constraining regarding the templates storage. As far as I know, there is currently no public PL/SQL API for Stamps, the latest incarnation of HTT.

Within that framework, there is a template cache feature but it is not functional under mod_plsql as the JVM is refreshed on each call. So, I'm trying to replace the current cache feature with a database storage of parsed templates.

I think the examples given here will greatly help me.

I will also try to adapt the way the templates are get before the parsing. We intend to use XML-DB to keep the template sources and access them though a path or a list of possible resource folders (to mimic the PHP include_path).

If anyone got any hints regarding what I'm trying to do, don't hesitate to comments.

Thank you.

BLOB creation without database connexion

Yves Bergeron, July 09, 2004 - 11:14 am UTC

Hi everybody,

Always on my Stamps PL/SQL bridge, I'm trying to serialize a java object and create a lob with it and return that lob to PL/SQL to then store it in the database.

In my situation, I use the oracle JVM under mod_plsql. So, the PL/SQL procedure is connected to the database through the DAD definition. I don't know how to use that same connexion within java as I don't know any of the connexion parameters in the DAD.

In that context, is it possible to:
1- create a temporary lob within PL/SQL
2- pass the lob pointer to java
3- serialize my class using the lob pointer
4- return to PL/SQL and save the lob in the database using the temporary lob pointer

Thank you for any comments


Tom Kyte
July 09, 2004 - 11:24 am UTC

are you talking about a java stored procedure?

Java stored procedure and blob

Yves Bergeron, July 15, 2004 - 9:54 am UTC

Yes I'm talking about Java stored procedures.

I don't have any problem passing CLOB back and forth between PL/SQL and java.

With the current version of my coding, I got the message "ORA-29532: Java call terminated by uncaught Java exception: java.io.IOException: Closed LOB" even though I open my blob before streaming something inside.

Thank you for your help.

Here is the simplified PL/SQL and java coding for my procedures:

CREATE OR REPLACE PACKAGE BODY stamps
AS
------------------------------------------------------------------------------
FUNCTION getJavaFromCache (p_templateName in varchar2)
RETURN NUMBER
AS
LANGUAGE JAVA
NAME 'stampsXMLdb.StampsContext.getTemplateFromCache(java.lang.String) return int';

------------------------------------------------------------------------------
PROCEDURE getJavaBlob (p_template_ptr in number, p_template_blob in BLOB)
AS
LANGUAGE JAVA
NAME 'stampsXMLdb.StampsContext.getTemplateBlob(int, oracle.sql.BLOB)';

------------------------------------------------------------------------------
FUNCTION get (p_template_name IN VARCHAR2) RETURN NUMBER
AS
v_template_blob blob;
v_ptr_template integer;

BEGIN
-- Instantiate the template java object
-- and get a handle on that template
v_ptr_template := getJavaFromCache(p_template_name);

-- Create an empty blob
insert into stamps_template$ (stt_id,
stt_template)
values (seq_stamps_template$.nextval,
empty_blob())
returning stt_template into v_template_blob;

-- pass blob pointer to the java proc
getJavaBlob(v_ptr_template, v_template_blob);

-- After the proc, we assume the blob was
-- updated to contain the serialized java object

RETURN v_ptr_template;
END;

END stamps;
/

package stampsXMLdb;

import netwhit.stamps.*;

import java.io.*;
import java.util.*;
import oracle.sql.*;

public class StampsContext
{
private static StampsContext stampsInstance = null;
private static Cache templateCache;

static Vector activeTemplates;

private void StampsContext()
{
}

public static synchronized void initInstance() {

if (stampsInstance == null) {
stampsInstance = new StampsContext();
stampsInstance.templateCache = Cache.getCache();

activeTemplates = new Vector();
}
}

public static int getTemplateFromCache(String templateName)
throws Exception {

initInstance();

Template template = templateCache.get(templateName);

if (template == null) {
return -1;

} else {
activeTemplates.addElement(template);

return activeTemplates.size() - 1;
}
}

public static void getTemplateBlob(int templatePtr, BLOB templateBlob)
throws Exception
{
OutputStream os = null;
ObjectOutputStream oop = null;
Template template = (Template) activeTemplates.elementAt(templatePtr);

try {
templateBlob.open(BLOB.MODE_READWRITE);
os = templateBlob.getBinaryOutputStream();
oop = new ObjectOutputStream(os);
oop.writeObject(template);
oop.flush();
oop.close();
os.close();
} catch (Exception e) {
throw e;
} finally {
if (oop != null) { oop.close(); }
if (os != null) { os.close(); }
}
}

}


Tom Kyte
July 15, 2004 - 12:57 pm UTC

if you need the connection -- you just call

16 Connection conn =
17 new OracleDriver().defaultConnection();


in your java code -- is that what you are looking for?


else, can you get the example down to something really really small -- and use "create or replace java source" so I can reproduce in sqlplus..

Simplified case

Yves Bergeron, August 02, 2004 - 8:34 am UTC

Hi Tom,

Here is a runable script exposing my problem.

drop table tb_blob;

CREATE TABLE tb_blob
(blob_id NUMBER,
blob_content blob);

DROP SEQUENCE seq_blob_id;

CREATE SEQUENCE seq_blob_id START WITH 1 INCREMENT BY 1 NOCYCLE;

CREATE OR REPLACE PACKAGE pl_blob
AS
PROCEDURE storeBlob;
END pl_blob;
/
show errors

CREATE OR REPLACE PACKAGE BODY pl_blob
AS
------------------------------------------------------------------------------
PROCEDURE setBlobContent (p_blob_content in BLOB)
AS
LANGUAGE JAVA
NAME 'javaBlobClass.setBlobContent(oracle.sql.BLOB)';

------------------------------------------------------------------------------
PROCEDURE storeBlob
AS
v_blob_content blob;

BEGIN
-- Create an empty blob
insert into tb_blob (blob_id,
blob_content)
values (seq_blob_id.nextval,
empty_blob())
returning blob_content into v_blob_content;

-- pass blob pointer to the java proc
setBlobContent(v_blob_content);

-- After the proc, we assume the blob was
-- updated to contain the serialized java object
END;

END pl_blob;
/
show errors

create or replace and resolve java source named "javaBlobClass" as

import java.io.*;
import java.util.*;
import oracle.sql.*;

public class javaBlobClass
{
private static String stringToSerialize = "string content";

private void javaBlobClass()
{
}

public static void setBlobContent(BLOB blobContent)
throws Exception
{
OutputStream os = null;
ObjectOutputStream oop = null;

try {
blobContent.open(BLOB.MODE_READWRITE);
os = blobContent.getBinaryOutputStream();
oop = new ObjectOutputStream(os);
oop.writeObject(stringToSerialize);
oop.flush();
oop.close();
os.close();
} catch (Exception e) {
throw e;
} finally {
if (oop != null) { oop.close(); }
if (os != null) { os.close(); }
}
}
}
/
show errors

After creating the preceding objects, if I do

execute pl_blob.storeBlob

I get:

BEGIN pl_blob.storeBlob; END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.io.IOException: Closed LOB
ORA-06512: at "SHQYBN_STRUTS.PL_BLOB", line 16
ORA-06512: at "SHQYBN_STRUTS.PL_BLOB", line 23
ORA-06512: at line 1

Thank you for your help.

Tom Kyte
August 02, 2004 - 9:04 am UTC

well -- it really was closed! 

finally gets called on success and failure.  oop/os are not null, but they are "closed"

this works:

 25             oop.writeObject(stringToSerialize);
 26             oop.flush();
 27             oop.close();<b>
 28             oop = null;</b>
 29             os.close();<b>
 30             os = null;</b>
 31          } catch (Exception e) {
 32             throw e;
 33          } finally {
 34            if (oop != null ) { oop.close(); }
 35            if (os  != null ) { os.close(); }
 36          }
 37      }
 38  }
 39  /
 
Java created.
 
ops$tkyte@ORA10G> show errors
No errors.
ops$tkyte@ORA10G> set define on
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> execute pl_blob.storeBlob
 
PL/SQL procedure successfully completed.
 
 

Alex, January 31, 2006 - 4:09 pm UTC

Hi Tom,

I am trying to serialize java objects and store them in blobs. I don't understand how the above example works though. Could you please explain how the "write" method works? How exactly is the serialized object being written to the database? All I see is a insert statement that inserts an id and class and returns a blank blob. I suspect the writeObject method has something to do with it but once we serialize with that, it doesn't go back to the database to populate the blob column? I'm confused. Thanks.

Tom Kyte
February 01, 2006 - 2:24 am UTC

lobs work like files, like a stream, it streams them into the lob.

You'll want to check out the jdbc docs:
</code> http://docs.oracle.com/docs/cd/B19306_01/java.102/b14355/jstreams.htm#sthref849 <code>

calling java from pl/sql (oracle)

Amit, June 20, 2006 - 12:37 pm UTC

Hi Tom,

Thanks alot for all your help and support .
Am sure if we count all the "thanks" by all your followers in your site, we can't store it in any datatype in Oracle :-)


Now coming to my query -

Is there any method provided by oracle (pl/sql) to directly call Java code

1) without loading java into oracle as java procedures
2) without using any other language like C.

If yes please suggest some method or URL.




Tom Kyte
June 21, 2006 - 9:05 am UTC

not really. No. There are "things you can do" but they all require "not directly calling java". Eg: you can use utl_tcp to talk to a java server you write and run outside of the database - but you have to pack and unpack the data on the socket and such.

cache problems with java object serialization

Jaume, June 06, 2007 - 8:08 am UTC

Hi!
I found very interesting the thread and in fact we're using the same method to serialize java object into a Oracle 9.2 DB. The problem is that sometimes in multithreading environment where each thread has is own oracle connection, we're having retrieving obsolete (or cached) BLOBs (java objects). Basically, some threads do SELECT FOR UPDATE of some row of a table with one BLOB colunm. Then, these threads uses the commented method to serialize a java object. At the same time, another thread may be waiting in another SELECT FOR UPDATE for the same row (and BLOB). When the first thread (or connection) performs the COMMIT, the waiting thread/connection gets an obsolete/cached version of the BLOB. This only happens in heavy multithreading situations.

Any idea if the JDBC driver may be caching somehow the BLOBs across connections? (we're using the oracle ojdbc14_9.2.0.8 driver). How to solve that problem?

We only manage to avoid this problem by inserting the BLOB in a temp table and updating original BLOB again from the temp table before the commit.

Any help greatly welcome.
Tom Kyte
June 06, 2007 - 1:45 pm UTC

blobs are read consistent as of the time the lob locator is selected from the database.

if you

a) read a lob locator
b) someone commits a change to that blob
c) start reading the data out based on that lob locator

the data will be read consistently as of the point in time (a), it will not see bits of information committed at (b)

it follows the same rules as cursors (replace lob locator in a,b,c with CURSOR and it would be precisely the same)

Java Objects in the database

John Flack, June 07, 2007 - 8:42 am UTC

I read this thread with some alarm - sounds like the nasty old "a database is just a place to persist my objects" paradigm. I can't think of a single good reason to store serialized objects in the database. If the data is important enough to save, it is important enough to save in a form that can use the strengths of the database, not as a Blob. You can't query a Blob, can't enforce relationships, can't join ...
Use a good O/R framework and save your data as rows and columns.

newer methods?

Alex Jacoby, July 18, 2008 - 12:36 pm UTC

So, 7 years later, is there any easier way to do this? Is it crazy to want something more like the way it's shown in Sun's JDBC docs?

Engineer eng = (Engineer)rs.getObject("ENGINEERS");

http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/mapping.html

Thanks,
Alex

Serializing java objects

Ransford, December 22, 2009 - 11:52 am UTC

Hi Tom,

This is a very interesing article and the questions and feedbacks have helped in understanding the code provided. I am trying to serialize java objects and store them in blobs, specifically I want to store an instance of a sql statement object so later I can use it to cancel a long running query from a servlet. All my attempts so far has ended in failure, not sure how to proceed. This is the error I am getting using the write method provided:

private void setObject(Object obj) throws Exception
{
class SetObjectThread extends Thread
{
private Object obj;
private String className;

private Connection conn = null;
private ObjectOutputStream oos = null;
private CallableStatement stmt = null;
private OutputStream os = null;

SetObjectThread(Object obj) {
this.obj = obj;
this.className = obj.getClass().getName();
//className = "java.sql.Statement";
}

public void run() {
String sql = "begin \n" +
" insert into dlp_java_objects \n" +
" (id, classname, bytes) \n" +
" values(?,?,empty_blob()) \n" +
" return bytes into ?; \n" +
" end; ";

try {
conn = getDbConnection();

stmt = conn.prepareCall(sql);
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();
oos = new ObjectOutputStream(os);
oos.writeObject(obj);
oos.flush();
} catch (Exception e){
log.warn("ConnectDbBean.setObject(): " + e.toString());
} finally {
if(oos != null) {
try{
oos.close();
} catch(Exception e){}
}
if(os != null){
try{
os.close();
} catch(Exception e){}
}

if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {;}
stmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {;}
conn = null;
}

log.info("ConnectDbBean.setObject(): Done storing " + className);
}
}
}

System.out.println("ConnectDbBean.setObject(): storing statement object");
SetObjectThread t = new SetObjectThread(obj);
t.start();
}

ConnectDbBean.setObject():java.io.NotSerializableException: org.apache.tomcat.dbcp.dbcp.DelegatingStatement
Tom Kyte
December 31, 2009 - 8:35 am UTC

did you read up on the the exception being thrown?

NotSerializableException


Your object is not serializable, Object's do not have to implement the serializable interface.


http://java.sun.com/j2se/1.4.2/docs/api/java/io/NotSerializableException.html

<quote>

Thrown when an instance is required to have a Serializable interface. The serialization runtime or the class of the instance can throw this exception. The argument should be the name of the class.

</quote>


and you know, even if you could save this object, I've no idea what you would do with it since... Well, no one else could see this until you commit?

Why not set up a resource profile, to restrict long running queries? If that is your goal.

Serializing java objects followup

Ransford, January 01, 2010 - 3:57 pm UTC

...
Followup December 31, 2009 - 8am Central time zone:

did you read up on the the exception being thrown?

NotSerializableException


Your object is not serializable, Object's do not have to implement the serializable interface.



http://java.sun.com/j2se/1.4.2/docs/api/java/io/NotSerializableException.html


<quote>

Thrown when an instance is required to have a Serializable interface. The serialization runtime or the class of the instance can throw this exception. The argument should be the name of the class.

</quote>


and you know, even if you could save this object, I've no idea what you would do with it since... Well, no one else could see this until you commit?

Why not set up a resource profile, to restrict long running queries? If that is your goal.


I read and understood the documentation, I just wanted to see if anyone had a workaround. My application is quering against a very large database for a number of similar queries in a loop (both the query request and the cancellation is controlled from a web interface). If any of these queries are running long for whatever reason the capability is there to stop this query. Currently I store the statement handle along with the thread name in a static variable (HashMap), this works perfectly well except that if the application server (Tomcat) is shutdown the query might still be running, at that point the HashMap would be lost. It seems that this doesn't worth the effort, thanks for the great article thought.

Tom Kyte
January 04, 2010 - 10:25 am UTC

... I just wanted to see if anyone had a
workaround....

sure, use it with serializable objects?




but the database does have gobs of monitoring tools and the ability to shutdown a long running query for you automagically - no code involved.

Class cast exception when we read the object

SRam, March 16, 2014 - 6:07 pm UTC

Hello,

There is no doubt this example has helped me in completing my project. However, here is the issue

When we read the object - Object o = oip.readObject();
it throws a class cast exception when we dont have the related class. I am bit confused how did it worked for you?
Please help.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here