Monday, November 25, 2013

Oracle's XMLTYPE with Hibernate 4

    XML is quite popular nowadays. You can store XML as a VARCHAR type in a DB. However, it has some limitations, e.g. you cannot perform XPath DB queries on a VARCHAR. Oracle DB comes with XML dedicated type - XMLTYPE that allows to perform XPath queries, has XML validation logic built-in and some storage optimizations. On the contrary, Hibernate does not support XMLTYPE out of the box. We need to define our custom UserType.
There is some information in the Internet on how to incorporate XMLTYPE into Hibernate. However, there is a trick that needs to be done in order to make it working as expected. Because of that I decided to create this blog entry. In my example I will use Oracle 11.2.0.3 and Hibernate 4.
First of all you need to have:
  • ojdbc6.jar (from Oracle's page),
  • xdb6.jar (from Oracle's page),
  • xmlparserv2.jar (I found this one in my SQL Developer distribution),
in your classpath. If you use Tomcat you can just drop all three files in Tomcat/lib folder. The tricky part is that xmlparserv2.jar uses ServiceLoader format and has the following files in META-INF/services folder:
  •  javax.xml.parsers.DocumentBuilderFactory,
  •  javax.xml.parsers.SAXParserFactory,
  •  javax.xml.transform.TransformerFactory.
It will register Oracle's implementation as a JAXP provider. Sometimes it is not desirable because e.g. you prefer Xerces parser. You can simply remove these three files from within JAR file (this is mentioned trick :)).

The second step is to provide UserType implementation (1):
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleResultSet;
import oracle.xdb.XMLType;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.usertype.UserType;
import org.w3c.dom.Document;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Objects;

public class HibernateXMLType implements UserType
{
    @Override
    public int[] sqlTypes() {
        return new int[] {
                oracle.xdb.XMLType._SQL_TYPECODE
        };
    }

    @Override
    public Class returnedClass() {
        return Document.class;
    }

    @Override
    public int hashCode(Object o) {
        return o.hashCode();
    }

    @Override
    public Object assemble(Serializable cached, Object owner) {
        try {
            return new DocumentConverter().stringToDom((String) cached);
        } catch (Exception e) {
            throw new HibernateException("Could not assemble String to Document", e);
        }
    }

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

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

    @Override
    public boolean equals(Object o1, Object o2) {
        return Objects.equals(o1, o2);
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor impl, 
                              Object owner) throws SQLException {
        XMLType xmlType = null;
        try {
            OracleResultSet ors = rs.unwrap(OracleResultSet.class);
            xmlType = (XMLType) ors.getSQLXML(names[0]);
            return xmlType != null ? xmlType.getDocument() : null;
        } finally {
            if (null != xmlType) {
                xmlType.close();
            }
        }
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, 
                            SessionImplementor session) throws SQLException {
        XMLType xmlType = null;
        try {
            if (value != null) {
                xmlType = XMLType.createXML(st.getConnection().unwrap(OracleConnection.class), 
                                            new DocumentConverter().domToString((Document) value));
            }
            st.setObject(index, xmlType);
        } finally {
            if (xmlType != null) {
                xmlType.close();
            }
        }
    }

    @Override
    public Object deepCopy(Object value) {
        return value;
    }

    @Override
    public boolean isMutable() {
        return false;
    }
}
DocumentConverter is my custom implementation and you can find similar methods here.

Next, you need your custom Hibernate's dialect (2):
import oracle.xdb.XMLType;
import org.hibernate.dialect.Oracle10gDialect;

public class Oracle10gDialectWithXMLType extends Oracle10gDialect {
    public Oracle10gDialectWithXMLType() {
        registerHibernateType(XMLType._SQL_TYPECODE, "XMLTYPE");
        registerColumnType(XMLType._SQL_TYPECODE, "XMLTYPE");
    }
}

Finally you can place an annotation on your entity (3):
@Entity
@TypeDef(name = "HibernateXMLType", typeClass = HibernateXMLType.class).

After that you can define a field in your entity (4):

@Type(type = "HibernateXMLType")
private Document document;

This is all you need to utilize Oracle's XMLTYPE in Hibernate 4.

3 comments :

  1. Method is ok :) we must create own dialect for all databases

    ReplyDelete
  2. Can you please provide the DocumentConverter implementation

    ReplyDelete