Mapping XML to Oracle XMLTYPE in IBM Webspshere platform

Oracle XMLTYPE attribute type can be mapped to a W3C Document type representation. Hibernate's UserType mechanism allows to create vendor specific data types. There are certain points to consider when mapping an object to an Oracle XMLType. Oracle provides it's own implementation of W3C Document, but it may not be suitable for the underlying application implementation. Hence, it is better to encapsulate the logic of converting to 'application specific' document implementation in this user type. When running in-container, the websphere server wraps the oracle results sets and prepared statements for pooled connections. Hence, such a user type must deal with the data type mapping for both in / out of container scenarios.

 

The underlying implementation is rather project specific and demonstrates how an Oracle XMLTYPE can be mapped to a non-Oracle W3C Document implementation.

 

For more about Oracle XMLTYPE, refer to http://www.oracle-base.com/articles/9i/XMLTypeDatatype.php

Using minor code changes, project specific requirements can be met to:

1. Use W3C Oracle document implementation.

2. Use DOM4J implementation.

3. If an application uses simple xml strings, but stored as XMLTYPE, this type can encapsulate the semantics of data conversion as well.

package oracle.websphere;

import java.io.IOException;
import java.io.Reader;
import java.io.Serializable;
import java.io.StringWriter;
import java.sql.Clob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.apache.xml.serialize.OutputFormat;
import org.apache.xml.serialize.XMLSerializer;
import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;
import org.w3c.dom.Document;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;

// Handles the document storage and retrieval for Oracle XMLTYPE on a Websphere
// platform
public class OracleWebsphereXMLType implements UserType, Serializable {

    /* (non-Javadoc)
     * @see org.hibernate.usertype.UserType#nullSafeSet(java.sql.PreparedStatement, 
       java.lang.Object, int)
     */
    public void nullSafeSet(PreparedStatement st, Object value, int index)
        throws HibernateException, SQLException {

        oracle.xdb.XMLType xmlType = null;
        try {
            // null handling of document
            if (value == null) {
                st.setNull(
                    index,
                    oracle.jdbc.OracleTypes.OPAQUE,
                    "SYS.XMLTYPE");
                return;
            }

            // simple oracle connections out-of-container
            if (st instanceof oracle.jdbc.OraclePreparedStatement) {
                xmlType =
                    oracle.xdb.XMLType.createXML(
                        st.getConnection(),
                        toXML((Document) value));
                oracle.jdbc.OraclePreparedStatement oraclePS =
                    (oracle.jdbc.OraclePreparedStatement) st;
                oraclePS.setObject(index, xmlType);
            } else { // in-container
                xmlType =
                    (oracle
                        .xdb
                        .XMLType) (com
                        .ibm
                        .websphere
                        .rsadapter
                        .WSCallHelper
                        .jdbcPass(
                            oracle.xdb.XMLType.class,
                            "createXML",
                            new Object[] {
                                st.getConnection(),
                                toXML((Document) value)},
                            new Class[] {
                                java.sql.Connection.class,
                                String.class },
                            new int[] {
                                com
                                    .ibm
                                    .websphere
                                    .rsadapter
                                    .WSCallHelper
                                    .CONNECTION,
                                com
                                    .ibm
                                    .websphere
                                    .rsadapter
                                    .WSCallHelper
                                    .IGNORE }));

                com.ibm.websphere.rsadapter.WSCallHelper.jdbcCall(
                    null,
                    st,
                    "setObject",
                    new Object[] { new Integer(index), xmlType },
                    new Class[] { int.class, Object.class });
            }
        } finally {
            if (xmlType != null) {
                xmlType.close();
            }
        }
    }

    /* (non-Javadoc)
     * @see org.hibernate.usertype.UserType#nullSafeGet(java.sql.ResultSet, 
       java.lang.String[], java.lang.Object)
     */
    public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
        throws HibernateException, SQLException {

        oracle.xdb.XMLType xmlType = null;
        try {
            oracle.sql.OPAQUE value;

            if (rs instanceof oracle.jdbc.OracleResultSet) {
                oracle.jdbc.OracleResultSet oracleRs =
                    (oracle.jdbc.OracleResultSet) rs;
                value = oracleRs.getOPAQUE(names[0]);
            } else {
                value =
                    (
                        oracle
                            .sql
                            .OPAQUE) com
                            .ibm
                            .websphere
                            .rsadapter
                            .WSCallHelper
                            .jdbcCall(
                        null,
                        rs,
                        "getOPAQUE",
                        new Object[] { names[0] },
                        new Class[] { String.class });
            }

            // in case OPAQUE is null
            if (value == null) {
                return null;
            }

            xmlType = oracle.xdb.XMLType.createXML(value);
            Clob clob = xmlType.getClobVal();
            return newDocument(clob.getCharacterStream());
        } finally {
            if (xmlType != null) {
                xmlType.close();
            }
        }
    }

    // Converts a W3C Document to an XML String.
    protected String toXML(Document document) {
        XMLSerializer xmlSerializer = new XMLSerializer();

        OutputFormat outputFormat = new OutputFormat(document);
        outputFormat.setOmitXMLDeclaration(false);
        outputFormat.setEncoding("UTF-8");
        outputFormat.setIndenting(true);

        StringWriter stringWriter = new StringWriter();
        xmlSerializer.setOutputCharStream(stringWriter);
        xmlSerializer.setOutputFormat(outputFormat);
        try {
            xmlSerializer.serialize(document);
            stringWriter.flush();
            stringWriter.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        return stringWriter.toString();
    }

    // Creates a document from a reader
    protected static Document newDocument(Reader reader) {
        DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
        dbf.setValidating(false);

        try {
            return dbf.newDocumentBuilder().parse(new InputSource(reader));
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (SAXException e) {
            throw new RuntimeException(e);
        } catch (ParserConfigurationException e) {
            throw new RuntimeException(e);
        }
    }

...
}

 

For more information, contact at:

gautam_batra (at) hotmail (dot) com