从Java结果集中检索以二进制XML形式存储的Oracle XMLType [英] Retrieving oracle XMLType stored as binary XML from a resultset in Java

问题描述

如果XMLType列存储为CLOB（这是11.2.0.1的默认值），我概述了为Oracle 11g（特别是11.2.0.1）写入/读取到Oracle XMLType列的解决方案。

参考答案：使用在java spring spring hibernate场景中的oracle XMLType

在SQLDeveloper中，如果您查看表定义（表DDL）中的SQL选项卡， XML列存储在11.2.0.1中，如下所示：

  XMLTYPE COLUMNATTRIBUTE_XML2STORE AS BASICFILE CLOB 
 
 
 
 
 
 
 问题： 
在Oracle 11.2.0.2中，XMLType的默认存储类型是二进制XML，这是出于性能原因而优选的。它看起来像这样： 
 
 
  XMLTYPE COLUMNATTRIBUTE_XML2作为SECUREFILE BINARY存储XML 
 然而，一旦以这种方式存储，我引用的解决方案就不再适用于从数据库中将XMLType列作为XML读取。
 
 
它从xmlType.getStringVal（）返回一些垃圾，我认为它是oracle二进制编码的XML。
 
 
 
 最终问题 
 I我无法从Oracle的binxml格式转换回有效的XML文档。我已经尝试过在 Oracle提供的各种配置，但他们似乎只是阅读，而不是解码它 - 所以它出错。
 
 
 
 nullSafeGet中的基本示例（请参阅上下文链接的答案）。
 
 
  xmlType =（XMLType）rs.getObject（names [0]）; 
 BinXMLProcessor xp = BinXMLProcessorFactory.createProcessor（）; 
 BinXMLStream bstr = xp.createBinXMLStream（xmlType.getInputStream（））; 
 BinXMLDecoder xdecode = bstr.getDecoder（）; 
 InfosetReader reader = xdecode.getReader（）; （reader.hasNext（）& i< 25）{
 i ++; 
 
; 
 reader.next（）; 
 logger.debug（1 v：+ reader.getValue（）+，s：+ reader.getStandalone（）+，类型：+ reader.getEventType（）+，+ reader。 getDataLength（））; 
} 
  
我尝试了类似的方法，用xmlType.getBlobVal替换xmlType.getInputStream（） 178），xmlType.getBytesValue（），但它们都抛出一个异常或返回垃圾数据。
 
 
  .http：
 
解决方案
找到catch，并且与代码无关。
 
 
 
 Hibernate UserType中正确的nullSafeGet，引用的答案是：
 
 pre $ public c ++ public void nullSafeSet（PreparedStatement st，Object value，int index，SessionImplementor session）throws HibernateException，SQLException {如果（logger.isTraceEnabled（））{
 logger.trace（nullSafeSet：+ value +，ps：+ st +，index：+ index）; 
 
 
} 
尝试{
 XMLType xmlType = null; 
 if（value！= null）{
 xmlType = XMLType.createXML（getOracleConnection（st.getConnection（）），（String）value）; 
} 
 st.setObject（index，xmlType）; 
 catch（Exception e）{
 throw new SQLException（无法将字符串转换为XML以进行存储：+（String）value）; 
} 
} 
  
 问题： SECUREFILE BINARY XML列（不是CLOB）必须使用xdb * .jar的最新（11.2.0.2+）版本，在本例中为xdb6.jar（〜257kb）。之前的xdb * .jar（对于10.x，〜136kb）仍然可以正常工作，即使错误地解码BINARY XML也不会抛出任何异常。 
 
 
  TL; DR ：下载xdb6.jar（〜257kb）来自Oracle 11gR2（11.2.0.3）JDBC驱动程序页面。老xdb罐子默默地失败，会让你难过。
 
I outlined a solution to writing/reading to an Oracle XMLType column for Oracle 11g (specifically 11.2.0.1) if the XMLType column is stored as a CLOB, which is the default for 11.2.0.1.

REFERENCE ANSWER: Using oracle XMLType in a java spring hibernate scenario

In SQLDeveloper, if you view the SQL tab within a table definition (table DDL) it defines the XML columns storage in 11.2.0.1 like this:
 XMLTYPE COLUMN "ATTRIBUTE_XML2" STORE AS BASICFILE CLOB 
Problem:
In Oracle 11.2.0.2, the default storage type for XMLType is Binary XML, which is preferred for performance reasons.  It looks like this:
XMLTYPE COLUMN "ATTRIBUTE_XML2" STORE AS SECUREFILE BINARY XML
HOWEVER, once stored this way, my referenced solution no longer works for READING the XMLType column as XML from the database.  It returns some garbage from xmlType.getStringVal(), which I assume is the oracle binary encoded XML.

Ultimate Problem
I am unable to convert back from the oracle binxml format to a valid XML document.

I have tried the Oracle BinXMLProcessor, BinXMLStream, BinXMLDecoder and InfosetReader in various configurations as outlined by Oracle, but they seem to just read, rather than decode it - so it errors out.

Basic example within the nullSafeGet (see linked answer for context).
xmlType = (XMLType) rs.getObject(names[0]);
BinXMLProcessor xp = BinXMLProcessorFactory.createProcessor();
BinXMLStream bstr = xp.createBinXMLStream(xmlType.getInputStream());
BinXMLDecoder xdecode = bstr.getDecoder();
InfosetReader reader = xdecode.getReader();

while (reader.hasNext() && i < 25) {
    i++;
    reader.next();
    logger.debug("1 v:" + reader.getValue() + ", s:" + reader.getStandalone() + ", type: " + reader.getEventType() + ", " + reader.getDataLength());
}
I tried similar approaches, replacing xmlType.getInputStream() with xmlType.getBlobVal(178), xmlType.getBytesValue(), but they all throw an exception or return garbage data.

Huh.
解决方案
Found the catch, and it's not related to the code.

The proper nullSafeGet in the Hibernate UserType, as noted in the referenced answer is:
public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException, SQLException {

    if (logger.isTraceEnabled()) {
        logger.trace("  nullSafeSet: " + value + ", ps: " + st + ", index: " + index);
    }
    try {
        XMLType xmlType = null;
        if (value != null) {
            xmlType = XMLType.createXML(getOracleConnection(st.getConnection()), (String)value);
        }
        st.setObject(index, xmlType);
    } catch (Exception e) {
        throw new SQLException("Could not convert String to XML for storage: " + (String)value);
    }
}
PROBLEM: when using a SECUREFILE BINARY XML column (not CLOB) you must use the most recent (11.2.0.2+) distribution of the xdb*.jar, which in this case is xdb6.jar (~257kb).  The earlier xdb*.jar (~136kb for 10.x) will still function, without tossing any exceptions even when incorrectly decoding BINARY XML.

TL;DR:  Download xdb6.jar (~257kb) from the Oracle 11gR2 (11.2.0.3) JDBC drivers page.  Older xdb jars fail silently and will make you sad.

                        
这篇关于从Java结果集中检索以二进制XML形式存储的Oracle XMLType的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！