Hibernate with Oracle XmlType

The solution is based on Mapping Oracle XmlType to Document from the Hibernate’s website. I don’t need to use C3P0 though.

package mypackage.util;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.Serializable;
import java.io.StringWriter;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;import oracle.jdbc.OracleResultSet;
import oracle.sql.OPAQUE;
import oracle.xdb.XMLType;

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;
import org.w3c.dom.Document;
import org.xml.sax.SAXException;
public class HibernateXMLType implements UserType, Serializable {

private static final long serialVersionUID = 2308230823023l;
private static final Class returnedClass = Document.class;
private static final int[] SQL_TYPES = new int[] { oracle.xdb.XMLType._SQL_TYPECODE };

public int[] sqlTypes() {
return SQL_TYPES;
}

public Class returnedClass() {
return returnedClass;
}

public int hashCode(Object _obj) {
return _obj.hashCode();
}

public Object assemble(Serializable _cached, Object _owner)
throws HibernateException
{
try {
return HibernateXMLType.stringToDom((String)_cached);
}
catch (Exception e) {
throw new HibernateException(“Could not assemble String to Document”, e);
}
}

public Serializable disassemble(Object _obj)
throws HibernateException
{
try {
return HibernateXMLType.domToString((Document)_obj);
}
catch (Exception e) {
throw new HibernateException(“Could not disassemble Document to Serializable”, e);
}
}

public Object replace(Object _orig, Object _tar, Object _owner) {
return deepCopy(_orig);
}

public boolean equals(Object arg0, Object arg1)
throws HibernateException
{
if(arg0 == null && arg1 == null) return true;
else if (arg0 == null && arg1 != null ) return false;
else return arg0.equals(arg1);
}

public Object nullSafeGet( ResultSet rs, String [ ] names, Object arg2 )
throws HibernateException, SQLException
{
XMLType xmlType = null;
Document doc = null;
try {
OPAQUE op = null;
OracleResultSet ors = null;
if (rs instanceof OracleResultSet) {
ors = (OracleResultSet)rs;
} else {
throw new UnsupportedOperationException(“ResultSet needs to be of type OracleResultSet”);
}
op = ors.getOPAQUE(names[0]);
if(op != null) {
xmlType = XMLType.createXML ( op );
}
doc = xmlType.getDOM();
}finally {
if (null != xmlType) {
xmlType.close();
}
}
return doc;
}

public void nullSafeSet( PreparedStatement st, Object value, int index )
throws HibernateException, SQLException
{
try {
XMLType xmlType = null;
if (value != null) {
xmlType = XMLType.createXML(st.getConnection(),HibernateXMLType.domToString((Document)value));
}
st.setObject(index, xmlType);
}
catch (Exception e) {
throw new SQLException(“Could not convert Document to String for storage”);
}
}

public Object deepCopy(Object value)
throws HibernateException
{
if (value == null) return null;

return (Document)((Document)value).cloneNode(true);
}

public boolean isMutable() {
return false;
}

public static String domToString(Document _document)
throws TransformerException
{
TransformerFactory tFactory = TransformerFactory.newInstance();
Transformer transformer = tFactory.newTransformer();
transformer.setOutputProperty(OutputKeys.INDENT, “yes”);
DOMSource source = new DOMSource(_document);
StringWriter sw=new StringWriter();
StreamResult result = new StreamResult(sw);
transformer.transform(source, result);
return sw.toString();
}

public static Document stringToDom(String xmlSource)
throws SAXException, ParserConfigurationException, IOException
{
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
return builder.parse(new ByteArrayInputStream(xmlSource.getBytes(“UTF-8”)));
}
}

In the hbm.xml file set the property to be:

<property
name=”xmlMessage”
type=”mypackage.util.HibernateXMLType”
column=”MESSAGE_XML”
/>

In the .java file set the field to be:

private Document  xmlMessage;public Document getXmlMessage() {
return xmlMessage;
}public void setXmlMessage(Document xmlMessage) {
this.xmlMessage= xmlMessage;
}
Advertisements

10 thoughts on “Hibernate with Oracle XmlType

  1. i have a prole the following problem using HibernateXMLType: java.lang.UnsupportedOperationException: ResultSet needs to be of type OracleResultSet.

    Is it possible to have help?

    Thanks,
    Carlo

  2. OracleResultSet *may* be underlying type of your’s ResultSet (depends on the configuration, assuming DelegatingResultSet). Ugly hack:

    private ResultSet toUnderlyingResultSet(ResultSet rset) {
    while(rset instanceof DelegatingResultSet) {
    rset = ((DelegatingResultSet) rset).getDelegate();
    }
    return rset;
    }

    and in the your type:

    if (rs instanceof OracleResultSet) {
    ors = (OracleResultSet) rs;
    } else {
    ors = (OracleResultSet) (toUnderlyingResultSet(rs));

    good luck!

  3. I found this post very helpful and I was able to use it to get my XMLType column mapped properly. I am, however, also using the SchemaExport feature of Hibernate and I have been unable to get Hibernate to try and generate the Schema mapping SQL so Oracle can bind the Column to a Schema. Here is an example of the SQL I would like to generate:
    CREATE TABLE dev.xml_record (id NUMBER, xmlcol XMLType)
    XMLType COLUMN xmlcol
    XMLSCHEMA “xmlSchema”
    ELEMENT “record”;

    Hibernate is only able to generate the first line. I thought able using the Dialect.getTableTypeString() method to produce this additional SQL, but it would be applied to all tables I export, and I am only looking to add the SQL to this table alone. Thoughts?

  4. The xmlparserv2.jar overrides the defaul factories for DocumentBuilder and Transformer. So you will have to configure the following system properties for the above example to work:
    – javax.xml.parsers.DocumentBuilderFactory=com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderFactoryImpl
    – javax.xml.transform.TransformerFactory=com.sun.org.apache.xalan.internal.xsltc.trax.TransformerFactoryImpl

    Thanks for the very valuable example,
    julio V.

  5. Where do I need to set these system properties

    javax.xml.parsers.DocumentBuilderFactory=com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderFactoryImpl
    javax.xml.tansform.TransformerFactory=com.sun.org.apache.xalan.internal.xsltc.trax.TransformerFactoryImpl

    Thanks for your help.

  6. I am getting an error “(Fatal Error) DOMSource node as this type not supported” in OracleXMLType.nullSafeSet method.

    Thank you again for your help.

  7. Hi am getting error when i insert
    java.sql.SQLException: Could not convert Document to String for storage
    at OracleXMLType.nullSafeSet(OracleXMLType.java:126)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s