在hibernate中使用Oracle XMLType列

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列映射到hibernate实体类。有一个有效的(我认为是众所周知的)解决方案，它涉及到实现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. 将实体类中的属性声明为字符串。结果-值被读为空。如果属性是可序列化的，则会出现“无法反序列化”异常。
3. Using @Formula annotation (CAST xmlCol as varchar2(1000)). Result - value is not stored
4. 使用@Formula注释(将xmlCol转换为varchar2(1000)))。不存储结果值
5. Using @Loader and putting CAST in 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 @Loader if underlying table is LEFT JOINed).
6. 使用@Loader并将CAST放入SELECT中。这是最有希望的尝试——值被成功地读取和存储，但是当加载包含xml列的实体集合时，我得到null(如果将底层表保留为join, Hibernate在@Loader中不使用sql)。

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列作为字符串(用于编写)，加上使用@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模式(也有更多的选项，比如视图+触发器、列数据类型更改)，但对我来说，这也不是一个好的选择。

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

我想知道我是不是漏掉了什么，或者可能有办法让(3)工作?

4 个解决方案

#1

My Direction and Requirements

我的方向和要求

• Entity should store XML as a string (java.lang.String)
• 实体应该将XML存储为字符串(java.lang.String)
• Database should persist XML in an XDB.XMLType column
  • Allows indexing and more efficient xpath/ExtractValue/xquery type queries
  • 允许索引和更有效的xpath/提取值/xquery类型查询
• 数据库应该在XDB中保存XML。XMLType列允许索引和更有效的xpath/ExtractValue/xquery类型查询
• Consolidate a dozen or so partial solutions I found over the last week
• 整合了我上周发现的十几个部分解决方案。
• Working Environment
  • Oracle 11g r2 x64
  • Oracle 11 g r2 x64
  • Hibernate 4.1.x
  • Hibernate 4.1.x
  • Java 1.7.x x64
  • Java 1.7。x x64
  • Windows 7 Pro x64
  • Windows 7专业版x64
• 工作环境Oracle 11g r2 x64 Hibernate 4.1。Java 1.7 x。x64 Windows 7 Pro x64

Step-by-step Solution

一步一步的解决方案

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

第一步:找到xmlparserv2。jar(~ 1350 kb)

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

编译步骤2需要这个jar，并且包含在这里的oracle安装中:%ORACLE_11G_HOME%/LIB/xmlparserv2.jar

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

步骤1.5:找到xdb6。jar(~ 257 kb)

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或更高版本，或者将其存储为二进制XML，那么这一点至关重要。

Why?

为什么?

• In 11.2.0.2+ the XMLType column is stored using SECUREFILE BINARY XML by default, whereas earlier versions will stored as a BASICFILE CLOB
• 在11.2.0.2+中，XMLType列默认使用SECUREFILE二进制XML存储，而早期版本将作为BASICFILE CLOB存储
• Older versions of xdb*.jar do not properly decode binary xml and fail silently
  • Google Oracle Database 11g Release 2 JDBC Drivers and download xdb6.jar
  • Oracle数据库11g发布JDBC 2驱动程序并下载xdb6.jar
• 老版本的xdb *。jar不能正确解码二进制xml，并且在谷歌Oracle数据库11g发布2 JDBC驱动程序和下载xdb6.jar时不发出声音
• Diagnosis and solution for Binary XML decoding problem outlined here
• 二值XML解码问题的诊断与解决方法

Step 2: Create a hibernate UserType for the XMLType Column

步骤2:为XMLType列创建hibernate 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(服务提供者机制)更改默认的javax。用于SAXParserFactory、DocumentBuilderFactory和TransformerFactory的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来查找自定义实现。属性，然后在类路径上的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.parsers。修正可见错误
• javax.xml.transform.* fixes more subtle XML parsing errors
  • in my case, with apache commons configuration reading/writing
  • 在我的例子中，使用apache commons配置读写
• javax.xml.transform。*在我的例子中，使用apache commons配置读写修正了更细微的XML解析错误

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(springjunit4class4 classrunner .class)或类似的方法运行测试用例，您仍然会遇到错误。

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

应用服务器启动错误和测试用例错误的更好解决方案?两个选择

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项目中，重写xmlparserv2。通过在%PROJECT_HOME%/src/main/resources目录中创建以下文件来设置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.

完成了。

#2

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

还有一个更简单的解决方案。只需使用ColumnTransformer注释。

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

#3

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:

在我的坚持。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:只能绑定一个长值以插入长列”错误):

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.

kamubler的解决方案是我的起点，但是当我尝试插入大型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 * account to publish it in case it could be of help to someone else.

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

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

我正在使用JAXB构建XML字符串，但我认为它在本例中不相关。

#4

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:

确保在hibernate配置的会话工厂属性列表中设置自定义方言:

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