Clever Geek Handbook

Serializing Objects as BLOBs in Oracle

I have a HashMap that I am serializing and deserializing in an Oracle db into a BLOB datatype field. I want to execute a query using this field. For example, the application will create a new HashMap and have multiple key-value pairs. I want to query the db to see if a HashMap exists with this data in the db. I don't know how to do this, it seems strange if I have to go to every record in the db, deserialize it, and then compare if SQL handles BLOB comparison, so I could ... select * from PROCESSES where foo = ?. ... and foo is BLOB, huh? is an instance of the new HashMap? Thanks to

+1


source to share


7 replies


You can read the article here: Nail Shot: Old Shoes or Glass Bottle

I haven't heard much about your underlying architecture, but I can tell you straight away that there will never be a reason why you should use a HashMap this way. His technique is bad, simple and simple.

The answer to your question is not an Oracle smart query, its a redesign of your application architecture.

For starters, you shouldn't serialize the HashMap in the database (in general, you shouldn't serialize everything you need to query). Its much easier to create a table to represent hash maps in your application like this:



HashMaps
--------
MapID (pk int)
Key (pk varchar)
Value

Once you have the content of your hash maps in your database, its trivial to query the database to see if the data or any other generalized data exists:

SELECT Count(*) FROM HashMaps where MapID = ? AND Key = ?
+4


source


Storing serialized objects in a database is almost always a bad idea unless you know beforehand that you don't need to query them.

How do you serialize the HashMap? There are many ways to serialize data and an object like a HashMap. Comparing two maps, especially in serialized form, is not trivial, unless your serialization method ensures that two equivalent maps are always serialized the same.



One way to get around this mess is to use XML serialization for some objects that you rarely need to query. For example, when I work, we have a log table where a specific log message is saved as an XML file in a CLOB field. The xml data is a serialized Java object. We usually ask for other columns in the record and only read / write blobs in single atomic steps. However, a deep blob check was required once or twice, and the use of XML allowed it to be done (Oracle supports XML queries on varchar2 or CLOB fields, and native XML objects). This is a useful technique if used sparingly.

+2


source


Take a look at dbms_crypto.hash to make a hash of your blob. Store the hash next to the blob and it will give you something to narrow down your search to something manageable. I don't recommend storing a hashmap, but this is a general method of finding exact matches between blocks. See also SQL - How do you compare CLOBs .

+2


source


I cannot disagree, but they tell me about it. I appreciate your decision and what I had before. thank

+1


source


I didn't need to compare BLOBs, but it seems to be supported via package dbms_lob

.

See dbms_lob.compare()

at http://www.psoug.org/reference/dbms_lob.html

0


source


Oracle can have new datatypes defined with Java (or .net on windows), you can define the datatype for your serialized object and define how queries work on it.

Nice flaw if you try this ...

0


source


If you are ordering data in xml and storing data in xml you can use xpaths in your sql query. (Sorry, since I'm more of a SqlServer person, I don't know the details on how to do this in Oracle.)

  • If you only need to update a portion of the serialized data, then don't.
  • Likewise, if other data points to any data, or points to other data, do not do so.
0


source






More articles:


All Articles