oracle 从 blob 反序列化 java 对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14768439/
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
deserialize java object from a blob
提问by lalachka
first of all, i apologize, i'm about to ask a set of dumb questions. i don't know java AT ALL and i don't know if we are allowed to ask questions like these.
首先，我很抱歉，我要问一组愚蠢的问题。我根本不知道 Java，我不知道我们是否可以问这样的问题。
if not - delete my topic.
如果没有 - 删除我的主题。
there's a table in oracle that stores a blob. it's binary and i'm able to decode it, the output looks like this
oracle 中有一个表存储了一个 blob。它是二进制的，我能够解码它，输出看起来像这样
?í sr /com.epam.insure.credentialing.forms.StorageBeanàó ˉw/§ L variablest Ljava/util/Map;xpsr java.util.HashMapúá?`? F
loadFactorI thresholdxp?@ w t $_hasCompletedt t
$_wf_progresssr java.lang.Integera ¤÷?8 I valuexr java.lang.Number???”à? xp t $_wf_statussq ~ t $_form_instance_idsr java.lang.Long;??ì#? J valuexq ~ ??t $_isVisitedt truet 1sq ~ sq ~ ?@ `w _t confidential readable infot 1t confidential readable infot $_errorssr java.util.ArrayListxò??a I sizexp w
xt regionIdsq ~ ?t
confidential readable infot t t $_subbean_errorssq ~ w
xt regiont SOUTHWESTt idt t codet t reqTypeNamet
confidential readable infot t confidential readable infot tint t $_hasCompletedt falset comRequiredt t
lineImpactq ~ t prChiropractorsq ~ t fromTypeReqt not zipt 342t changeToTypeReq6t confidential readable infot t
prPodiatristsq ~ t
$_isValidatedt truet $_hasErrorsq ~ -t EVPapprovalsq ~ sq ~ ?@ w Approvedq ~ Ct
NEGOTIATORq ~ Et
Negotiatort datet
03/31/2006q ~ It confidential readable infot q ~ \xt updateRequiredt noq ~ t truet approverssr .forms.StorageBeanList?Wtú?G xq ~ w
q ~ Rsq ~ sq ~ ?@ w t commentst t decisiont Approvedq ~ Ct RVPq ~ Et RVPt datet
04/04/2006q ~ It t commentst t decisiont Approvedq ~ Ct COOq ~ Et COOt datet
04/14/2006q ~ It ~ ?xsq ~ sq ~ ?@ w t commentsq ~ Pt decisiont Approvedq ~ Ct CEOq ~ Et CEOt d
so here are my questions
所以这是我的问题
for some reason, when i try to insert the decoded blob value (what i posted above) into a table (i was going to move it to MS Access and parse it there. this would be a horrible solution but i'm desperate) - the only thing that inserts is "?í" without the quotes. also, i can't select all and copy it from the DBMS output window, again, the only thing that pastes is "?í" without the quotes. it seems like this text is not really there. does anyone have an idea on how to insert it into a table?
if i was to do it the right way and use java, where do i start? excuse this dumbness but i don't even know how to run java code. i found a few sample codes on the net but i don't know where to paste it :)
出于某种原因，当我尝试将解码后的 blob 值（我在上面发布的内容）插入表中时（我打算将其移动到 MS Access 并在那里解析它。这将是一个可怕的解决方案，但我很绝望）-唯一插入的是没有引号的“？í”。此外，我无法选择全部并从 DBMS 输出窗口复制它，同样，唯一粘贴的内容是没有引号的“？í”。似乎这段文字并不存在。有没有人知道如何将它插入表格中？
如果我要以正确的方式使用 Java，我该从哪里开始？请原谅我的愚蠢，但我什至不知道如何运行 Java 代码。我在网上找到了一些示例代码，但我不知道将其粘贴到哪里:)
i did google it and saw that i have to create a .java file in a text editor and then compile it, is that true for my case? i thought maybe that's some different java code, i thought maybe in my case i'd have to run it from oracle because that's where the tables are.
我用谷歌搜索了一下，发现我必须在文本编辑器中创建一个 .java 文件，然后编译它，对我的情况来说是这样吗？我想也许那是一些不同的 Java 代码，我想也许在我的情况下，我必须从 oracle 运行它，因为那是表所在的位置。
i also have the table structure, i attached a piece of it. this blob stores a table.
我也有表格结构，我附上了它的一部分。这个 blob 存储一个表。
anyhow, i'm sure it's obvious by now that i'm clueless. if anyone can point me somewhere i'd really appreciate it.
无论如何，我相信现在很明显我一无所知。如果有人能指点我，我会非常感激。
thank you
谢谢你
采纳答案by Vadzim
Here is an example of oracle 11g java stored function that deserializes java object from blob. As a free bonus added an example of oracle java stored procedure to update blob with serialized java object.
这是一个从 blob 反序列化 java 对象的 oracle 11g java 存储函数的示例。作为免费奖励，添加了一个 oracle java 存储过程的示例，以使用序列化的 java 对象更新 blob。
If object's class isn't java built-in (as in my case), you would also need to publishit's source (with all dependencies) in oracle database.
如果对象的类不是 java 内置的（就像我的情况一样），您还需要在 oracle 数据库中发布它的源代码（包含所有依赖项）。
CREATE OR REPLACE JAVA SOURCE NAMED "ServiceParamsBLOBHandler" AS
import java.io.*;
import java.util.*;
public class ServiceParamsBLOBHandler {
private static Object deserialize(InputStream stream) throws Exception {
ObjectInputStream ois = new ObjectInputStream(stream);
try {
return ois.readObject();
} finally {
ois.close();
}
}
private static byte[] serialize(Object object) throws IOException {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ObjectOutputStream oos = new ObjectOutputStream(baos);
oos.writeObject(object);
oos.close();
return baos.toByteArray();
}
//@SuppressWarnings("unchecked")
private static List<Map<String, String>> getParams(oracle.sql.BLOB blob) throws Exception {
return (List<Map<String, String>>) deserialize(blob.getBinaryStream());
}
public static oracle.sql.BLOB updatedParamField(oracle.sql.BLOB blob, String paramName, String fieldName, String value)
throws Exception {
List<Map<String, String>> params = getParams(blob);
Map<String, String> param = getParam(params, paramName);
param.put(fieldName, value);
oracle.sql.BLOB res = oracle.sql.BLOB.createTemporary(blob.getOracleConnection(), true, oracle.sql.BLOB.DURATION_CALL);
res.setBytes(1, serialize(params));
return res;
}
public static void updateParamField(oracle.sql.BLOB[] blobs, String paramName, String fieldName, String value)
throws Exception {
oracle.sql.BLOB blob = blobs[0];
List<Map<String, String>> params = getParams(blob);
Map<String, String> param = getParam(params, paramName);
param.put(fieldName, value);
blob.truncate(0);
blob.setBytes(1, serialize(params));
}
private static Map<String, String> getParam(List<Map<String, String>> params, String name) {
for (Map<String, String> param : params) {
if (name.equals(param.get("name"))) {
return param;
}
}
return null;
}
public static String getParamField(oracle.sql.BLOB blob, String paramName, String fieldName) throws Exception {
Map<String, String> param = getParam(getParams(blob), paramName);
return param == null ? null : param.get(fieldName);
}
}
/
alter java source "ServiceParamsBLOBHandler" compile
--select * from SYS.USER_ERRORS
/
CREATE OR REPLACE function getServiceParamField(b IN BLOB, paramName IN VARCHAR2, fieldName IN VARCHAR2) RETURN VARCHAR2
as LANGUAGE JAVA NAME 'ServiceParamsBLOBHandler.getParamField(oracle.sql.BLOB, java.lang.String, java.lang.String) return String';
/
CREATE OR REPLACE function updatedServiceParamField(b IN BLOB, paramName IN VARCHAR2, fieldName IN VARCHAR2, value IN VARCHAR2) RETURN BLOB
as LANGUAGE JAVA NAME 'ServiceParamsBLOBHandler.updatedParamField(oracle.sql.BLOB, java.lang.String, java.lang.String, java.lang.String) return oracle.sql.BLOB';
/
CREATE OR REPLACE PROCEDURE updateServiceParamField(b IN OUT BLOB, paramName IN VARCHAR2, fieldName IN VARCHAR2, value IN VARCHAR2)
AS LANGUAGE JAVA NAME 'ServiceParamsBLOBHandler.updateParamField(oracle.sql.BLOB[], java.lang.String, java.lang.String, java.lang.String)';
/
-- oracle blob read usage example:
select getServiceParamField(byte_value, 'account', 'format') from entity_property where name='params';
-- oracle blob update with java stored function usage example:
update entity_property set byte_value=updatedServiceParamField(byte_value, 'account', 'format', '15')
where name='params' and entity_id = 123
-- oracle blob update with java stored procedure usage example:
BEGIN
FOR c IN (select byte_value from entity_property where name='params' and entity_id = 123 for update) LOOP
updateServiceParamField(c.byte_value, 'account', 'format', '13');
END LOOP;
END;
/
Update
更新
Concrete snippets for the case in question.
相关案例的具体片段。
1) Full object load
1) 全对象负载
private static String getVariable(oracle.sql.BLOB blob, String name) throws Exception {
ObjectInputStream ois = new ObjectInputStream(blob.getBinaryStream());
try {
//noinspection unchecked
return ((HashMap<String, String>) ((StorageBean) ois.readObject()).variables).get(name);
} finally {
ois.close();
}
}
2) Partial field load
2) 部分场载荷
private static String getVariable(oracle.sql.BLOB blob, String name) throws Exception {
ObjectInputStream ois = new ObjectInputStream(blob.getBinaryStream());
try {
ois.skipBytes(variablesOffset);
//noinspection unchecked
return ((HashMap<String, String>) ois.readObject()).get(name);
} finally {
ois.close();
}
}
回答by lalachka
i will learn to do this in java at some point but since this is a rush - I decided to use SQL to extract fields from the blob. i'm putting this here in case someone else is ever as desperate to do this.
我将在某个时候学习在 Java 中执行此操作，但由于这很匆忙 - 我决定使用 SQL 从 blob 中提取字段。我把这个放在这里，以防其他人同样不顾一切地这样做。
it's a very ugly and slow solution but so far i'm able to get some fields. i will update once i'm done, to say whether i was able to get everything or not.
这是一个非常丑陋和缓慢的解决方案，但到目前为止我能够获得一些领域。完成后我会更新，以说明我是否能够获得一切。
here's the code i'm using (this is just for 1 field but it will give you an idea)
这是我正在使用的代码（这仅适用于 1 个字段，但它会给您一个想法）
DECLARE
CURSOR c_dts IS
SELECT Form_ID
FROM NR_DTS_FORMTABLE
WHERE 1 = 1
--AND ROWNUM BETWEEN 501 AND 4500
AND form_ID > 204815
--AND ROWNUM < 5000
AND ROWNUM < 3
--AND form_id IN (SELECT form_id FROM NR_DTS_BLOB)
AND Form_Type_ID = 102;
DTS c_dts%ROWTYPE;
BEGIN
OPEN c_dts;
LOOP
FETCH c_dts INTO DTS;
EXIT WHEN c_dts%NOTFOUND;
DECLARE
v_hold_blob BLOB;
v_len NUMBER;
v_raw_chunk RAW(10000);
v_chr_string VARCHAR2(32767);
-- v_chr_string CLOB;
v_position NUMBER;
c_chunk_len NUMBER := 1;
Form_ID NUMBER;
BEGIN
SELECT form_content
INTO v_hold_blob
FROM NR_DTS_FORMTABLE
WHERE Form_ID = DTS.Form_ID;
v_len := DBMS_LOB.getlength(v_hold_blob);
v_position := 1;
WHILE (v_position <= LEAST(v_len, 32767)) LOOP
v_raw_chunk := DBMS_LOB.SUBSTR(v_hold_blob, c_chunk_len, v_position);
v_chr_string := v_chr_string || CHR(hex_to_decimal(RAWTOHEX(v_raw_chunk)));
v_position := v_position + c_chunk_len;
END LOOP;
--insert into table
INSERT INTO NR_DTS_BLOBFIELDS_VARCHAR(formid
,regionId)
SELECT DTS.Form_ID
,SUBSTR(v_chr_string
,INSTR(v_chr_string, 'regionIdt') + LENGTH('regionIdt') + 2
,INSTR((SUBSTR(v_chr_string, INSTR(v_chr_string, 'regionIdt') + LENGTH('regionIdt') + 2))
,CHR(116) || CHR(0)))
regionId
FROM DUAL;
END;
-- DBMS_OUTPUT.put_line(DTS.Form_ID);
END LOOP;
CLOSE c_dts;
END;
