xml 在休眠中使用 Oracle XMLType 列

Using Oracle XMLType column in hibernate

提问by a1ex07

I need to map Oracle XMLType column to hibernate entity class. There is a working (and I think well-known) solution that involves implementing UserType; however, I cannot use it because requires importing Oracle xml parsers, which in turn causes many problems .
I'm ok with accessing value of xml column as a string and leave transformation to the code that manipulates entity, but I cannot find the way to read value from and write it to database. What I have tried so far:

我需要将 Oracle XMLType 列映射到休眠实体类。有一个有效的（我认为是众所周知的）解决方案涉及实施UserType；但是，我不能使用它，因为需要导入 Oracle xml 解析器，这又会导致很多问题。
我可以将 xml 列的值作为字符串访问并将转换留给操作实体的代码，但是我找不到从数据库中读取值并将其写入数据库的方法。到目前为止我尝试过的：

1. Declaring property in entity class as String. Result - value is read as null. If property is just Serializable, I get "cannot deserialize" exception.
2. Using @Formulaannotation (CAST xmlCol as varchar2(1000)). Result - value is not stored
3. Using @Loaderand putting CASTin SELECT. That was the most promising attempt - value was read and stored successfully, but when it comes to loading collection of entities that contain xml column, I get null(Hibernate doesn't use sql in @Loaderif underlying table is LEFT JOINed).

1. 将实体类中的属性声明为String. 结果值读作null. 如果 property is just Serializable，我会收到“无法反序列化”异常。
2. 使用@Formula注释 ( CAST xmlCol as varchar2(1000))。结果 - 值未存储
3. 使用@Loader并把CAST在SELECT。这是最有希望的尝试 - 成功读取并存储了值，但是在加载包含 xml 列的实体集合时，我得到了null（@Loader如果底层表被LEFT JOINed，Hibernate 不使用 sql in ）。

Another approach that I believe should work is to have xml column as String(for writing) plus dummy field for reading with @Formula; however, it looks like a dirty hack to me, and I'd prefer not to do so unless I have no choice.

我认为应该工作的另一种方法是将 xml 列作为String（用于写入）加上用于读取的虚拟字段@Formula；然而，这对我来说似乎是一个肮脏的黑客，除非我别无选择，否则我宁愿不这样做。

Finally, the very last thing I can do is to change DB Schema (also more that 1 option, like view + triggers, column data type change), but this is not a good option for me either.

最后，我能做的最后一件事是更改 DB Schema（还有更多选项，例如视图 + 触发器、列数据类型更改），但这对我来说也不是一个好选择。

I wonder if I missed something or maybe there is a way to make (3) work?

我想知道我是否遗漏了什么，或者有没有办法让 (3) 工作？

回答by Matt M

My Direction and Requirements

我的方向和要求

• Entity should store XML as a string (java.lang.String)
• Database should persist XML in an XDB.XMLType column
  • Allows indexing and more efficient xpath/ExtractValue/xquery type queries
• Consolidate a dozen or so partial solutions I found over the last week
• Working Environment
  • Oracle 11g r2 x64
  • Hibernate 4.1.x
  • Java 1.7.x x64
  • Windows 7 Pro x64

• 实体应将 XML 存储为字符串 (java.lang.String)
• 数据库应将 XML 保存在 XDB.XMLType 列中
  • 允许索引和更高效的 xpath/ExtractValue/xquery 类型查询
• 整合我在上周找到的十几个部分解决方案
• 工作环境
  • 甲骨文 11g r2 x64
  • 休眠 4.1.x
  • Java 1.7.x x64
  • Windows 7 专业版 x64

Step-by-step Solution

分步解决方案

Step 1: Find xmlparserv2.jar (~1350kb)

第 1 步：找到 xmlparserv2.jar (~1350kb)

This jar is required to compile step 2, and is included in oracle installations here: %ORACLE_11G_HOME%/LIB/xmlparserv2.jar

此 jar 是编译第 2 步所必需的，并且包含在此处的 oracle 安装中：%ORACLE_11G_HOME%/LIB/xmlparserv2.jar

Step 1.5: Find xdb6.jar (~257kb)

步骤 1.5：找到 xdb6.jar (~257kb)

This is critical if you are using Oracle 11gR2 11.2.0.2 or greater, or storing as BINARY XML.

如果您使用 Oracle 11gR2 11.2.0.2 或更高版本，或者存储为 BINARY XML，这很重要。

Why?

为什么？

• In 11.2.0.2+ the XMLType column is stored using SECUREFILE BINARY XMLby default, whereas earlier versions will stored as a BASICFILE CLOB
• Older versions of xdb*.jar do not properly decode binary xml and fail silently
  • Google Oracle Database 11g Release 2 JDBC Driversand download xdb6.jar
• Diagnosis and solution for Binary XML decoding problem outlined here

• 在 11.2.0.2+ 中，默认情况下XMLType 列使用SECUREFILE BINARY XML存储，而早期版本将存储为BASICFILE CLOB
• 旧版本的 xdb*.jar 无法正确解码二进制 xml 并静默失败
  • Google Oracle Database 11g 第 2 版 JDBC 驱动程序和下载 xdb6.jar
• 此处概述的二进制 XML 解码问题的诊断和解决方案

Step 2: Create a hibernate UserType for the XMLType Column

步骤 2：为 XMLType 列创建休眠 UserType

With Oracle 11g and Hibernate 4.x, this is easier than it sounds.

使用 Oracle 11g 和 Hibernate 4.x，这比听起来容易。

public class HibernateXMLType implements UserType, Serializable {
static Logger logger = Logger.getLogger(HibernateXMLType.class);


private static final long serialVersionUID = 2308230823023l;
private static final Class returnedClass = String.class;
private static final int[] SQL_TYPES = new int[] { oracle.xdb.XMLType._SQL_TYPECODE };

@Override
public int[] sqlTypes() {
    return SQL_TYPES;
}

@Override
public Class returnedClass() {
    return returnedClass;
}

@Override
public boolean equals(Object x, Object y) throws HibernateException {
    if (x == null && y == null) return true;
    else if (x == null && y != null ) return false;
    else return x.equals(y);
}


@Override
public int hashCode(Object x) throws HibernateException {
    return x.hashCode();
}

@Override
public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner) throws HibernateException, SQLException {

    XMLType xmlType = null;
    Document doc = null;
    String returnValue = null;
    try {
        //logger.debug("rs type: " + rs.getClass().getName() + ", value: " + rs.getObject(names[0]));
        xmlType = (XMLType) rs.getObject(names[0]);

        if (xmlType != null) {
            returnValue = xmlType.getStringVal();
        }
    } finally {
        if (null != xmlType) {
            xmlType.close();
        }
    }
    return returnValue;
}

@Override
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);
    }
}


@Override
public Object deepCopy(Object value) throws HibernateException {
    if (value == null) {
        return null;
    } else {
        return value;
    }
}

@Override
public boolean isMutable() {
    return false;
}

@Override
public Serializable disassemble(Object value) throws HibernateException {
    try {
        return (Serializable)value;
    } catch (Exception e) {
        throw new HibernateException("Could not disassemble Document to Serializable", e);
    }
}

@Override
public Object assemble(Serializable cached, Object owner) throws HibernateException {

    try {
        return (String)cached;
    } catch (Exception e) {
        throw new HibernateException("Could not assemble String to Document", e);
    }
}

@Override
public Object replace(Object original, Object target, Object owner) throws HibernateException {
    return original;
}



private OracleConnection getOracleConnection(Connection conn) throws SQLException {
    CLOB tempClob = null;
    CallableStatement stmt = null;
    try {
        stmt = conn.prepareCall("{ call DBMS_LOB.CREATETEMPORARY(?, TRUE)}");
        stmt.registerOutParameter(1, java.sql.Types.CLOB);
        stmt.execute();
        tempClob = (CLOB)stmt.getObject(1);
        return tempClob.getConnection();
    } finally {
        if ( stmt != null ) {
            try {
                stmt.close();
            } catch (Throwable e) {}
        }
    }
}   

Step 3: Annotate the field in your entity.

第 3 步：注释实体中的字段。

I'm using annotations with spring/hibernate, not mapping files, but I imagine the syntax will be similar.

我在 spring/hibernate 中使用注释，而不是映射文件，但我想语法会相似。

@Type(type="your.custom.usertype.HibernateXMLType")
@Column(name="attribute_xml", columnDefinition="XDB.XMLTYPE")
private String attributeXml;

Step 4: Dealing with the appserver/junit errors as a result of the Oracle JAR

第 4 步：处理由于 Oracle JAR 导致的 appserver/junit 错误

After including %ORACLE_11G_HOME%/LIB/xmlparserv2.jar (1350kb) in your classpath to solve compile errors, you now get runtime errors from your application server...

在您的类路径中包含 %ORACLE_11G_HOME%/LIB/xmlparserv2.jar (1350kb) 以解决编译错误后，您现在会收到来自应用程序服务器的运行时错误...

http://www.springframework.org/schema/beans/spring-beans-3.1.xsd<Line 43, Column 57>: XML-24509: (Error) Duplicated definition for: 'identifiedType'
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd<Line 61, Column 28>: XML-24509: (Error) Duplicated definition for: 'beans'
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd<Line 168, Column 34>: XML-24509: (Error) Duplicated definition for: 'description'
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd<Line 180, Column 29>: XML-24509: (Error) Duplicated definition for: 'import'
... more ...

WHY THE ERRORS?

为什么会出现错误？

The xmlparserv2.jar uses the JAR Services API (Service Provider Mechanism) to change the default javax.xml classes used for the SAXParserFactory, DocumentBuilderFactory and TransformerFactory.

xmlparserv2.jar 使用 JAR 服务 API（服务提供者机制）来更改用于 SAXParserFactory、DocumentBuilderFactory 和 TransformerFactory 的默认 javax.xml 类。

HOW DID IT HAPPEN?

它是怎么发生的？

The javax.xml.parsers.FactoryFinder looks for custom implementations by checking for, in this order, environment variables, %JAVA_HOME%/lib/jaxp.properties, then for config files under META-INF/services on the classpath, before using the default implementations included with the JDK (com.sun.org.*).

javax.xml.parsers.FactoryFinder 通过依次检查环境变量 %JAVA_HOME%/lib/jaxp.properties 和类路径上 META-INF/services 下的配置文件来查找自定义实现，然后再使用JDK (com.sun.org.*) 中包含的默认实现。

Inside xmlparserv2.jar exists a META-INF/services directory, which the javax.xml.parsers.FactoryFinder class picks up. The files are as follows:

在 xmlparserv2.jar 中存在一个 META-INF/services 目录，javax.xml.parsers.FactoryFinder 类会选取该目录。文件如下：

META-INF/services/javax.xml.parsers.DocumentBuilderFactory (which defines oracle.xml.jaxp.JXDocumentBuilderFactory as the default)
META-INF/services/javax.xml.parsers.SAXParserFactory (which defines oracle.xml.jaxp.JXSAXParserFactory as the default)
META-INF/services/javax.xml.transform.TransformerFactory (which defines oracle.xml.jaxp.JXSAXTransformerFactory as the default)

SOLUTION?

解决方案？

Switch all 3 back, otherwise you'll see weird errors.

将所有 3 个切换回来，否则您会看到奇怪的错误。

• javax.xml.parsers.* fix the visible errors
• javax.xml.transform.* fixes more subtle XML parsing errors
  • in my case, with apache commons configurationreading/writing

• javax.xml.parsers.* 修复可见错误
• javax.xml.transform.* 修复了更微妙的 XML 解析错误
  • 就我而言，使用apache commons 配置读取/写入

QUICK SOLUTION to solve the application server startup errors: JVM Arguments

解决应用服务器启动错误的快速解决方案：JVM 参数

To override the changes made by xmlparserv2.jar, add the following JVM properties to your application server startup arguments. The java.xml.parsers.FactoryFinder logic will check environment variables first.

要覆盖 xmlparserv2.jar 所做的更改，请将以下 JVM 属性添加到应用程序服务器启动参数。java.xml.parsers.FactoryFinder 逻辑将首先检查环境变量。

-Djavax.xml.parsers.SAXParserFactory=com.sun.org.apache.xerces.internal.jaxp.SAXParserFactoryImpl -Djavax.xml.parsers.DocumentBuilderFactory=com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderFactoryImpl -Djavax.xml.transform.TransformerFactory=com.sun.org.apache.xalan.internal.xsltc.trax.TransformerFactoryImpl

However, if you run test cases using @RunWith(SpringJUnit4ClassRunner.class) or similar, you will still experience the error.

但是，如果您使用 @RunWith(SpringJUnit4ClassRunner.class) 或类似方法运行测试用例，您仍然会遇到错误。

BETTER SOLUTION to the application server startup errors AND test case errors? 2 options

应用服务器启动错误和测试用例错误的更好解决方案？2个选项

Option 1: Use JVM arguments for the app server and @BeforeClass statements for your test cases

选项 1：对应用程序服务器使用 JVM 参数，对测试用例使用 @BeforeClass 语句

System.setProperty("javax.xml.parsers.DocumentBuilderFactory","com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderFactoryImpl");
System.setProperty("javax.xml.parsers.SAXParserFactory","com.sun.org.apache.xerces.internal.jaxp.SAXParserFactoryImpl");
System.setProperty("javax.xml.transform.TransformerFactory","com.sun.org.apache.xalan.internal.xsltc.trax.TransformerFactoryImpl");

If you have a lot of test cases, this becomes painful. Even if you put it in a super.

如果您有很多测试用例，这将变得很痛苦。即使你把它放在一个超级。

Option 2: Create your own Service Provider definition files in the compile/runtime classpath for your project, which will override those included in xmlparserv2.jar

选项 2：在您的项目的编译/运行时类路径中创建您自己的服务提供者定义文件，这将覆盖 xmlparserv2.jar 中包含的那些

In a maven spring project, override the xmlparserv2.jar settings by creating the following files in the %PROJECT_HOME%/src/main/resources directory:

在 maven spring 项目中，通过在 %PROJECT_HOME%/src/main/resources 目录中创建以下文件来覆盖 xmlparserv2.jar 设置：

%PROJECT_HOME%/src/main/resources/META-INF/services/javax.xml.parsers.DocumentBuilderFactory (which defines com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderFactoryImpl as the default)
%PROJECT_HOME%/src/main/resources/META-INF/services/javax.xml.parsers.SAXParserFactory (which defines com.sun.org.apache.xerces.internal.jaxp.SAXParserFactoryImpl as the default)
%PROJECT_HOME%/src/main/resources/META-INF/services/javax.xml.transform.TransformerFactory (which defines com.sun.org.apache.xalan.internal.xsltc.trax.TransformerFactoryImpl as the default)

These files are referenced by both the application server (no JVM arguments required), and solves any unit test issues without requiring any code changes.

这些文件由应用程序服务器引用（不需要 JVM 参数），并且无需更改任何代码即可解决任何单元测试问题。

Done.

完毕。

回答by anojan

To simplify Celso's answer further, one can avoid creating a custom function by using Oracle's built-in function

为了进一步简化 Celso 的回答，可以避免使用 Oracle 的内置函数创建自定义函数

XMLType.createxml(?)

XMLType.createxml(?)

that can handle NULLs.

可以处理NULL。

So the following annotations combined with Celso's custom dialect class works well.

所以下面的注解结合 Celso 的自定义方言类效果很好。

    @Lob
    @ColumnTransformer(read = "NVL2(EVENT_DETAILS, (EVENT_DETAILS).getClobVal(), NULL)", write = "XMLType.createxml(?)")
    @Column(name = "EVENT_DETAILS")
    private String details;

You might also have to register the clob as xmltype in your custom dialect. So effectively you will have the following:

您可能还必须在自定义方言中将 clob 注册为 xmltype。因此，您将有效地拥有以下内容：

public class OracleDialectExtension extends org.hibernate.dialect.Oracle10gDialect {
    public OracleDialectExtension() {
        super();
        registerColumnType(Types.CLOB, "xmltype");
    }

    @Override
    public boolean useInputStreamToInsertBlob() {
        return false;
    }
}

Ensure to set your custom dialect in your hibernate configuration's session-factory property list:

确保在您的休眠配置的会话工厂属性列表中设置您的自定义方言：

<property name="hibernate.dialect"><!-- class path to custom dialect class --></property>

回答by Kamuffel

There exists an even more simple solution for this. Just use the ColumnTransformer Annotation.

对此有一个更简单的解决方案。只需使用 ColumnTransformer Annotation。

@ColumnTransformer(read = "to_clob(data)", write = "?")
@Column( name = "data", nullable = false, columnDefinition = "XMLType" )
private String data;`

回答by Celso

After trying many different approaches with no luck, I came up with this:

在尝试了许多不同的方法但没有运气之后，我想出了这个：

On my entity class:

在我的实体类上：

@ColumnTransformer(read = "NVL2(EVENT_DETAILS, (EVENT_DETAILS).getClobVal(), NULL)", write = "NULLSAFE_XMLTYPE(?)")
@Lob
@Column(name="EVENT_DETAILS")
private String details;

Please notice the parentheses around "EVENT_DETAILS". If you don't put them, Hibernate won't rewrite the column name by appending the table name to the left.

请注意“EVENT_DETAILS”周围的括号。如果您不放置它们，Hibernate 将不会通过将表名附加到左侧来重写列名。

You will have to create the NULLSAFE_XMLTYPE function, which will allow you to insert null values (since there's a restriction of exactly one question mark for the writing transformation on @ColumnTransformer and XMLType(NULL) produces an exception). I created the function like this:

您必须创建 NULLSAFE_XMLTYPE 函数，该函数将允许您插入空值（因为 @ColumnTransformer 上的写入转换只限制一个问号，而 XMLType(NULL) 会产生异常）。我创建了这样的函数：

create or replace function NULLSAFE_XMLTYPE (TEXT CLOB) return XMLTYPE IS
    XML XMLTYPE := NULL;
begin
    IF TEXT IS NOT NULL THEN
      SELECT XMLType(TEXT) INTO XML FROM DUAL;
    END IF;

    RETURN XML;
end;

On my persistence.xml file:

在我的 persistence.xml 文件中：

<property name="hibernate.dialect" value="mypackage.CustomOracle10gDialect" />

The custom dialect (if we don't override the "useInputStreamToInsertBlob" method, we would get "ORA-01461: can bind a LONG value only for insert into a LONG column" errors):

自定义方言（如果我们不覆盖“useInputStreamToInsertBlob”方法，我们会得到“ORA-01461：只能为插入到 LONG 列而绑定一个 LONG 值”错误）：

package mypackage;

import org.hibernate.dialect.Oracle10gDialect;

public class CustomOracle10gDialect extends Oracle10gDialect {

    @Override
    public boolean useInputStreamToInsertBlob() { 
        //This forces the use of CLOB binding when inserting
        return false;
    }
}

This is working for me using Hibernate 4.3.6 and Oracle 11.2.0.1.0 (with ojdbc6-11.1.0.7.0.jar).

这对我使用 Hibernate 4.3.6 和 Oracle 11.2.0.1.0（使用 ojdbc6-11.1.0.7.0.jar）有用。

I have to admit I didn't try Matt M's solution because it involves a lot of hacking and using libraries that are not in standard Maven repositories.

我不得不承认我没有尝试 Matt M 的解决方案，因为它涉及大量的黑客攻击和使用标准 Maven 存储库中没有的库。

Kamuffel's solution was my starting point but I got ORA-01461 error when I tried to insert big XMLs, that's why I had to create my own dialect. Also, I found problems with the TO_CLOB(XML_COLUMN) approach (I would get "ORA-19011: Character string buffer too small" errors). I guess this way the XMLTYPE value is first converted to VARCHAR2 and then to CLOB, thus, causing problems when attempting to read big XMLs. That's why after some research I decided to use XML_COLUMN.getClobVal() instead.

Kamuffel 的解决方案是我的起点，但是当我尝试插入大型 XML 时出现 ORA-01461 错误，这就是我必须创建自己的方言的原因。此外，我发现 TO_CLOB(XML_COLUMN) 方法存在问题（我会收到“ORA-19011：字符串缓冲区太小”错误）。我猜这样 XMLTYPE 值首先转换为 VARCHAR2，然后转换为 CLOB，因此，在尝试读取大 XML 时会导致问题。这就是为什么经过一些研究后我决定改用 XML_COLUMN.getClobVal() 的原因。

I haven't found this exact solution on the Internet. That's why I decided to create a StackOverflow account to publish it in case it could be of help to someone else.

我还没有在互联网上找到这个确切的解决方案。这就是为什么我决定创建一个 StackOverflow 帐户来发布它，以防它对其他人有帮助。

I'm using JAXB for constructing the XML String but I think it's not relevant in this case.

我正在使用 JAXB 来构造 XML 字符串，但我认为在这种情况下它不相关。