How to Oracle XMLTYPE in Hibernate

Question

One of the column is of the type XMLTYPE in Oracle database. In my application, I want to persist the data and using Hibernate.

I did the following for mapping XMLTYPE in hibernate

1. Define the custom user type implementing UserType

2. The custom user type implementation is based on the blog link - http://community.jboss.org/wiki/MappingOracleXmlTypetoDocument

3. ut we are not using C3p0 connection pool and instead we are using DBCP

I am facing issue while creating the XMLType in the custom user type

XMLType.createXML(st.getConnection(),HibernateXMLType.domToString((Document) value));

where st is the preparedStatement passed to the method.

The connection object returned by st.getConnection() is of the type org.apache.commons.dbcp.PoolableConnection

But the createXML method expects connection object only of the type oracle.jdbc.OracleConnection

I also tried getting the getInnermostDelegate but this also does not work.

The XMLTYPE creation methods are in the included jar xdb.jar - will there be any changes depending on the versions included?

Thanks

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Got the SQLConnection object using the below code-

SimpleNativeJdbcExtractor extractor = new SimpleNativeJdbcExtractor();
PoolableConnection poolableConnection = (PoolableConnection) extractor
                .getNativeConnection(st.getConnection());
Connection sqlConnection = poolableConnection.getInnermostDelegate();

Now, the error message is java.sql.SQLException: Invalid column type

Below is the over ridden method

public void nullSafeSet(PreparedStatement st, Object value, int index)
            throws HibernateException, SQLException {

        SimpleNativeJdbcExtractor extractor = new SimpleNativeJdbcExtractor();
        PoolableConnection poolableConnection = (PoolableConnection) extractor
                .getNativeConnection(st.getConnection());
        Connection sqlConnection = poolableConnection.getInnermostDelegate();

        try {
            XMLType xmlType = null;
            if (value != null) {
                xmlType.createXML(sqlConnection, HibernateXMLType
                        .domToString((Document) value));

            }
            st.setObject(index, xmlType);
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException(
                    "Could not convert Document to String for storage");
        }
    }

Left with no clue now...

Answer 1

Got the SQLConnection object using the below code-

SimpleNativeJdbcExtractor extractor = new SimpleNativeJdbcExtractor();
PoolableConnection poolableConnection = (PoolableConnection) extractor
                .getNativeConnection(st.getConnection());
Connection sqlConnection = poolableConnection.getInnermostDelegate();

Now, the error message is java.sql.SQLException: Invalid column type

Below is the over ridden method

public void nullSafeSet(PreparedStatement st, Object value, int index)
            throws HibernateException, SQLException {

        SimpleNativeJdbcExtractor extractor = new SimpleNativeJdbcExtractor();
        PoolableConnection poolableConnection = (PoolableConnection) extractor
                .getNativeConnection(st.getConnection());
        Connection sqlConnection = poolableConnection.getInnermostDelegate();

        try {
            XMLType xmlType = null;
            if (value != null) {
                xmlType.createXML(sqlConnection, HibernateXMLType
                        .domToString((Document) value));

            }
            st.setObject(index, xmlType);
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException(
                    "Could not convert Document to String for storage");
        }
    }

Left with no clue now...