Using Oracle XMLType column in hibernate

Question

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:

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 @Formula annotation (CAST xmlCol as varchar2(1000)). Result - value is not stored
3. 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).

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.

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.

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

Answer 1

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 BINARY XML by 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 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 };

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

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.

Answer 2

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;`

Answer 3

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>

Answer 4

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.

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:

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:

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

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

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.

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.

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.

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

Answer 5

I had the issue when migrating from Hibernate 3.6.* to Hibernate 5.4, I resolved by adding dbUnit maven dependency before Oracle xmlparserv2. dbUnit has xerces:xercesImpl as transient dependency. This way I don't have to mess with App Server Config and unit tests runs just fine.