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