Advantages and disadvantages of storing java object to database

[archanab]

For one of our applications using Oracle DB, we intend to store data as a java object. Can anyone list the advantages and also disadvantages using this approach?

[xcallmejudasx]

The Oracle DBMS can store and execute stored procedures and functions within itself. PL/SQL (Oracle Corporation's proprietary procedural extension to SQL), or the object-oriented language Java can invoke such code objects and/or provide the programming structures for writing them.

I'm really just firing blindly here but since the only info I know about oracle I just read off wikipedia.

If your data is already stored as an object it should be easier to just pull the object from the DB to use instead of having to create a new object(deserialize) using fields of data you pull instead.

You should look into memory options. Storing an actual object might use more bytes of memory then just storing the fields.

Sorting. Seems 100000x easier to sort by a field when the fields are actually tied to an object.

Perhaps someone with more knowledge will hop in and expand/clear up some of what I brought up.

[neilcoffey]

I assume you mean literally wrap an ObjectOutputStream around a byte array, write the object, then blob the bytes from the byte array. You problably want a DeflaterOutputStream in between to compress the data too. The main caveat with this is that you need to make sure you don't create a ticking timebomb where your code changes so that the object can't be deserialized again. Ideally, set a definite serialVersionID on the class and only serialize objects with no dependent objects and whose package and class name you don't intend to change and whose internal structure will change very little.

A safer option is to write out some XML (or human readable) representation of the object's state and store that. Again, you could compress the XML then blob the compressed data if you don't need to perform searches on the data.

[neilcoffey]

Sorry, so to explicitly answer your question:
Advantages:
(1) simplicity of storage (no creating complex array of different tables, fields to store your data)
(2) compactness (possibilty to compress all data from an object together into one blob), hence potential performance gains for simple store and retrieve
Disadvantages:
(1) the DB can't understand your data: if you need to do searches, sorts, filters etc on data, will have to "roll your own" in some way: create separate index fields, or load and scan the data from the app layer
(2) Sensitive to difficulty retrieving data later if you make a decision at storage time that's sensitive to the class structure

[fishtoprecords]

If the OP really means to do as @neilcoffey suggests, then I have a more fundamental question: Why are you using Oracle? or any other RDBMS?

You lose all the advantages that any RDMBS brings, such as queries.

Sounds like square peg in round hole to me.

[neilcoffey]

It's sort of a square peg in a round hole, but sometimes it's useful if (a) some of your data DOES fit well into a relational database, but (b) but you also have some data that is fiddly to map to database tables, and (c) the only functionality you want from the DB for that data is transaction security, caching, the convenience of having "all your data in one place", but you don't need any data-related functionality such as searching, sorting, joining etc.

[archanab]

Thanks Neil. XML is the other option we are considering.

[uncommon]

Disadvantages:
(1) the DB can't understand your data: if you need to do searches, sorts, filters etc on data, will have to "roll your own" in some way: create separate index fields, or load and scan the data from the app layer

I think this disadvantage seems to outweigh any of the advantages. The only exception I can see is if you have a unique scenario that just works better with storing the object.

[Steve11235]

As for disadvantages, apart from losing visibility to the object contents, there aren't any.

Advantages:
You can change the object without having to modify the database at all. As long as you handle the serialization process carefully, you can add properties to the object and still retrieve old versions, using default values.

As long as the object doesn't have an enormous number of properties, I would consult with the DBA's about using a variable length byte field rather than a BLOB. I don't do Oracle, but other databases have performance issues with BLOB, and variable length fields may not use unnecessary storage. Also, BLOB's can also require special handling, such as input and output streams.

[neilcoffey]

Sorry, yes I was using the word "BLOB" in a slightly abstract sense. I suppose what I really meant was "the appropriate data type for 'raw byte' storage depending on your DB and typical data size". That said, I wouldn't prematurely optimise with a choice of data type with a stupidly small limit (e.g. the SQL Server types that limit your row size to the size of a page probably aren't a good choice if you want to serialise arbitrary objects, and in any case SQL Server uses different BLOB storage strategies depending on the size of the data). MySQL's types with 64K limit may be a good compromise, although performance-wise, I'm not sure there's actually much in it.

As far as actually getting the data is concerned, I don't think that's a major problem if you're deserialising Java objects -- Blob will give you an InputStream to the data, which is precisely what you need.

[Steve11235]

Neil makes a good point about input streams...

BTW, you can serialize to XML as well. The disadvantage is increased overhead and much larger size. The advantages are that XML serialization is very simple (default constructor, follow Java Bean rules, all public properties are automatically stored), you can view the data (and modify it, which is bad), properties set to their default values don't get serialized, and properties not found in the serialized data are set to their default values.

P.S. Another advantage of serialized data is that one table can hold many different objects. If you looking at persisting session or shopping basket information in a Web app, I would lean toward serialization.

Also, you should at least look at Hibernate...