Using Oracle XMLType column in hibernate

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

Step-by-step Solution

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

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

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

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

Why?

• In 11.2.0.2+ the XMLType column is stored using SECUREFILE BINARYXML by default, whereas earlier versions will stored as a BASICFILECLOB
• 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
• Diagnosis and solution for Binary XML decoding problem outlined here

Step 2: Create a hibernate UserType for the XMLType Column

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

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 };@Overridepublic int[] sqlTypes() {    return SQL_TYPES;}@Overridepublic Class returnedClass() {    return returnedClass;}@Overridepublic 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);}@Overridepublic int hashCode(Object x) throws HibernateException {    return x.hashCode();}@Overridepublic 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;}@Overridepublic 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);    }}@Overridepublic Object deepCopy(Object value) throws HibernateException {    if (value == null) {        return null;    } else {        return value;    }}@Overridepublic boolean isMutable() {    return false;}@Overridepublic Serializable disassemble(Object value) throws HibernateException {    try {        return (Serializable)value;    } catch (Exception e) {        throw new HibernateException("Could not disassemble Document to Serializable", e);    }}@Overridepublic 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);    }}@Overridepublic 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.

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

@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

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...

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.

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.*).

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

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.

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

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

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.

-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.

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

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

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

%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.

Done.

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

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

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

XMLType.createxml(?)

that can handle NULLs.

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

    @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:

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>