在 Oracle 中将对象序列化为 BLOB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/372114/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Serializing objects as BLOBs in Oracle
提问by bmw0128
I have a HashMap that I am serializing and deserializing to an Oracle db, in a BLOB data type field. I want to perform a query, using this field. Example, the application will make a new HashMap, and have some key-value pairs. I want to query the db to see if a HashMap with this data already exists in the db. I do not know how to do this, it seems strange if i have to go to every record in the db, deserialize it, then compare, Does SQL handle comparing BLOBs, so i could have...select * from PROCESSES where foo = ?....and foo is a BLOB type, and the ? is an instance of the new HashMap? Thanks
我有一个 HashMap，我在 BLOB 数据类型字段中将其序列化和反序列化为 Oracle 数据库。我想使用此字段执行查询。例如，应用程序将创建一个新的 HashMap，并有一些键值对。我想查询数据库以查看数据库中是否已存在具有此数据的 HashMap。我不知道如何做到这一点，如果我必须去数据库中的每条记录，反序列化它，然后比较，SQL 是否处理比较 BLOB，所以我可以有...select * from PROCESSES where foo = ?....并且 foo 是 BLOB 类型，而 ? 是新 HashMap 的实例吗？谢谢
回答by Juliet
Here's an article for you to read: Pounding a Nail: Old Shoe or Glass Bottle
这里有一篇文章供您阅读：敲钉子：旧鞋或玻璃瓶
I haven't heard much about your application's underlying architecture, but I can tell you immediately that there is nevera reason why you should need to use a HashMap in this way. Its a bad technique, plain and simple.
我对您的应用程序的底层架构了解不多，但我可以立即告诉您，您没有理由需要以这种方式使用 HashMap。这是一种糟糕的技术，简单明了。
The answer to your question is not a clever Oracle query, its a redesign of your application's architecture.
您的问题的答案不是巧妙的 Oracle 查询，而是对应用程序架构的重新设计。
For a start, you should not serialize a HashMap to a database (more generally, you shouldn't serialize anything that you need to query against). Its much easier to create a table to represent hashmaps in your application as follows:
首先，您不应该将 HashMap 序列化到数据库（更一般地说，您不应该序列化您需要查询的任何内容）。在您的应用程序中创建一个表来表示哈希映射要容易得多，如下所示：
HashMaps -------- MapID (pk int) Key (pk varchar) Value
Once you have the content of your hashmaps in your database, its trivial to query the database to see if the data already exists or produce any other kind of aggregate data:
一旦您在数据库中获得了哈希映射的内容，就可以轻松查询数据库以查看数据是否已经存在或生成任何其他类型的聚合数据：
SELECT Count(*) FROM HashMaps where MapID = ? AND Key = ?
回答by u7867
Storing serialized objects in a database is almostalways a bad idea, unless you know ahead of time that you don't need to query against them.
将序列化对象存储在数据库中几乎总是一个坏主意，除非您提前知道不需要查询它们。
How are you serializing the HashMap? There are lots of ways to serialize data and an object like a HashMap. Comparing two maps, especially in serialized form, is not trivial, unless your serialization technique guarantees that two equivalent maps always serialize the same way.
你如何序列化HashMap？有很多方法可以序列化数据和像 HashMap 这样的对象。比较两个映射，尤其是序列化形式的映射并非易事，除非您的序列化技术保证两个等效映射始终以相同的方式进行序列化。
One way you can get around this mess is to use XML serialization for some objects that rarely need to be queried. For example, where I work we have a log table where a certain log message is stored as an XML file in a CLOB field. This xml data represents a serialized Java object. Normally we query against other columns in the record, and only read/write the blob in single atomic steps. However once or twice it was necessary to do some deep inspection of the blob, and using XML allowed this to happen (Oracle supports querying XML in varchar2 or CLOB fields as well as native XML objects). It's a useful technique if used sparingly.
解决这种混乱的一种方法是对一些很少需要查询的对象使用 XML 序列化。例如，在我工作的地方，我们有一个日志表，其中将某个日志消息作为 XML 文件存储在 CLOB 字段中。这个 xml 数据代表一个序列化的 Java 对象。通常我们查询记录中的其他列，并且只在单个原子步骤中读/写 blob。然而，有一两次需要对 blob 进行一些深入检查，而使用 XML 允许这种情况发生（Oracle 支持在 varchar2 或 CLOB 字段以及本机 XML 对象中查询 XML）。如果谨慎使用，这是一种有用的技术。
回答by Gary Myers
Look into dbms_crypto.hash to make a hash of your blob. Store the hash alongside the blob and it will give you something to narrow down the search to something manageable. I'm not recommending storing the hash map, but this is a general technique for searching for an exact match between blobs. See also SQL - How do you compare a CLOB
查看 dbms_crypto.hash 以生成您的 blob 的哈希值。将散列与 blob 一起存储，它将为您提供一些将搜索范围缩小到可管理的内容。我不建议存储哈希映射，但这是搜索 blob 之间精确匹配的通用技术。另请参阅SQL - 如何比较 CLOB
回答by bmw0128
i cannot disagree, but i'm being told to do so. i appreciate your solution, and that's sort of what i had previously. thanks
我不能不同意，但有人告诉我要这样做。我很欣赏你的解决方案，这就是我以前的解决方案。谢谢
回答by BQ.
I haven't had the need to compare BLOBs, but it appears that it's supported through the dbms_lobpackage.
我不需要比较 BLOB，但它似乎是通过dbms_lob包支持的。
See dbms_lob.compare()at http://www.psoug.org/reference/dbms_lob.html
见dbms_lob.compare()在http://www.psoug.org/reference/dbms_lob.html
回答by Ian Ringrose
Oracle can have new data types defined with Java (or .net on windows) you could define a data type for your serialized object and define how queries work on it.
Oracle 可以使用 Java（或 Windows 上的 .net）定义新的数据类型，您可以为序列化对象定义数据类型并定义查询如何对其进行处理。
Good lack if you try this...
如果你试试这个，那就太好了……
回答by Ian Ringrose
If you serialize your data to xml, and store the data in an xml you can then use xpaths within your sql query. (Sorry as I am more of a SqlServer person, I don't know the details of how to do this in Oracle.)
如果将数据序列化为 xml，并将数据存储在 xml 中，则可以在 sql 查询中使用 xpath。（对不起，因为我更像是一个 SqlServer 人，我不知道如何在 Oracle 中执行此操作的详细信息。）
- If you EVERY need to update only part of the serialized data don't do this.
- Likewise if any of the data is pointed to by other data or points to other data don't do this.
- 如果您只需要更新部分序列化数据，请不要这样做。
- 同样，如果任何数据被其他数据指向或指向其他数据，请不要这样做。
