JBossAS4.2.x JPA annatations on Oracle92 xmlType column

[cd_eat]

hi
It seems the current jpa implementation(hibernate3.?) in jboss4.2.x does'nt supports oracle xmlType.

I tried mapping the xmlType column to a plain String field, and the mapped filed( fetch=FetchType.EAGER) returned null in a query

And I tried mapping the xmlType column to a String field with annation @Clob, an exception occured during a query.

any ideas?

[cd_eat]

Really busi these days, now I post my way:

1. make an extention dialect of hibernate, and add it to the jboss server's library path (jboss-4.2.x\server\default\lib\ for example)
2. mapping the oracle xml column to a jpa bean field using the class type we defined above.
3. modify a jboss java source file (in jar file: jboss-4.2.x\server\default\lib\jboss-common-jdbc-wrapper.jar)
org.jboss.resource.adapter.jdbc.WrappedConnection to expose a sql connection.
4. notice: you must use the oci driver instead of the thin driver of oracle9i, since the're bugs in thin dirver which cause sending large xml content will faild( larger than 25KB maybe)
5. After all this, we can integrate oracle xml feature (xmlType column) with jpa beans.
6. More importent, I don't think u want to do this-_-. Oracle9i/10g xmlType is really slow(by store as clob), I made comparison between oracle9i and db2 9.5 by inserting 10,000 2KB xml records and then quering one using xpathes. result is that:

------------------Inserting speed--------Quering time(get 1 out of 10,000)
Oracle-------------14/s --------------------24s
DB2v95------------55/s-------------------0.6s


here are the source codes:
1:OracleXMLTypes

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.OPAQUE;
import oracle.xdb.XMLType;

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;
import org.jboss.resource.adapter.jdbc.WrappedConnection;

public class OracleXMLTypes implements UserType, Serializable{
 private static final Class returnedClass = String.class;
 private static final int[] SQL_TYPES = new int[] { oracle.xdb.XMLType._SQL_TYPECODE };
 public String xmlContent;

 public OracleXMLTypes(){

 }
 public OracleXMLTypes(String xmlContent){
 this.xmlContent = xmlContent;
 }

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

 public Class returnedClass() {
 return returnedClass;

 }

 public boolean equals(Object arg0, Object arg1) throws HibernateException {
 if (arg0 == null || arg1 == null) {
 throw new HibernateException("None of the arguments can be null.");
 }

 if (arg0 instanceof oracle.xdb.XMLType
 && arg1 instanceof oracle.xdb.XMLType) {

 return arg0.equals(arg1);
 }
 return false;

 }

 public int hashCode(Object arg0) throws HibernateException {
 return arg0.hashCode();
 }

 public Object nullSafeGet(ResultSet rs, String[] names, Object arg2)
 throws HibernateException, SQLException {
 XMLType xt = (XMLType)rs.getObject(names[0]);
 return new OracleXMLTypes(xt.getStringVal());
 }

 public void nullSafeSet(PreparedStatement ps, Object value, int index)
 throws HibernateException, SQLException {
 WrappedConnection wc = (WrappedConnection)ps.getConnection();
 XMLType xmlType = XMLType.createXML(wc.oriSQLConnection(), ((OracleXMLTypes)value).xmlContent);
 ps.setObject(index, xmlType);
 }

 public Object deepCopy(Object value) throws HibernateException {
 //
 return value;
 }

 public boolean isMutable() {
 return false;
 }

 public Serializable disassemble(Object arg0) throws HibernateException {
 return null;
 }

 public Object assemble(Serializable arg0, Object arg1)
 throws HibernateException {
 return null;
 }

 public Object replace(Object arg0, Object arg1, Object arg2)
 throws HibernateException {
 return null;
 }

}

2: jboss java src:WrappedConnection

/*
 * JBoss, Home of Professional Open Source.
 * Copyright 2006, Red Hat Middleware LLC, and individual contributors
 * as indicated by the @author tags. See the copyright.txt file in the
 * distribution for a full listing of individual contributors.
 *
 * This is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as
 * published by the Free Software Foundation; either version 2.1 of
 * the License, or (at your option) any later version.
 *
 * This software is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this software; if not, write to the Free
 * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 * 02110-1301 USA, or see the FSF site: http://www.fsf.org.
 */
package org.jboss.resource.adapter.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.jboss.logging.Logger;
import org.jboss.util.NestedSQLException;

/**
 * A wrapper for a connection.
 *
 * @author <a href="mailto:[e-mail]">David Jencks</a>
 * @author <a href="mailto:[e-mail]">Adrian Brock</a>
 * @version $Revision: 57189 $
 */
public class WrappedConnection implements Connection
{
 private static final Logger log = Logger.getLogger(WrappedConnection.class);

 private BaseWrapperManagedConnection mc;

 private WrapperDataSource dataSource;

 private HashMap statements;

 private boolean closed = false;

 private int trackStatements;

 
// here we must expose a sql conn, which used by the 'OracleXMLTypes' we defined
 public Connection oriSQLConnection(){
 return mc.con;
 }

 public WrappedConnection(final BaseWrapperManagedConnection mc)
 {
 this.mc = mc;
 if (mc != null)
 trackStatements = mc.getTrackStatements();
 }

 void setManagedConnection(final BaseWrapperManagedConnection mc)
 {
 this.mc = mc;
 if (mc != null)
 trackStatements = mc.getTrackStatements();
 }

 public WrapperDataSource getDataSource()
 {
 return dataSource;
 }

 protected void setDataSource(WrapperDataSource dataSource)
 {
 this.dataSource = dataSource;
 }

 public void setReadOnly(boolean readOnly) throws SQLException
 {
 checkStatus();
 mc.setJdbcReadOnly(readOnly);
 }

 public boolean isReadOnly() throws SQLException
 {
 checkStatus();
 return mc.isJdbcReadOnly();
 }

 public void close() throws SQLException
 {
 closed = true;
 if (mc != null)
 {
 if (trackStatements != BaseWrapperManagedConnectionFactory.TRACK_STATEMENTS_FALSE_INT)
 {
 synchronized (this)
 {
 if (statements != null)
 {
 for (Iterator i = statements.entrySet().iterator(); i.hasNext(); )
 {
 Map.Entry entry = (Map.Entry) i.next();
 WrappedStatement ws = (WrappedStatement) entry.getKey();
 if (trackStatements == BaseWrapperManagedConnectionFactory.TRACK_STATEMENTS_TRUE_INT)
 {
 Throwable stackTrace = (Throwable) entry.getValue();
 log.warn("Closing a statement you left open, please do your own housekeeping", stackTrace);
 }
 try
 {
 ws.internalClose();
 }
 catch (Throwable t)
 {
 log.warn("Exception trying to close statement:", t);
 }
 }
 }
 }
 }
 mc.closeHandle(this);
 }
 mc = null;
 dataSource = null;
 }

 public boolean isClosed() throws SQLException
 {
 return closed;
 }

 public Statement createStatement() throws SQLException
 {
 checkTransaction();
 try
 {
 return new WrappedStatement(this, mc.getConnection().createStatement());
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException
 {
 checkTransaction();
 try
 {
 return new WrappedStatement(this, mc.getConnection().createStatement(resultSetType, resultSetConcurrency));
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)
 throws SQLException
 {

 checkTransaction();
 try
 {
 return new WrappedStatement(this, mc.getConnection()
 .createStatement(resultSetType, resultSetConcurrency, resultSetHoldability));
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public PreparedStatement prepareStatement(String sql) throws SQLException
 {
 checkTransaction();
 try
 {
 return new WrappedPreparedStatement(this, mc.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY));
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
 throws SQLException
 {
 checkTransaction();
 try
 {
 return new WrappedPreparedStatement(this, mc.prepareStatement(sql, resultSetType, resultSetConcurrency));
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency,
 int resultSetHoldability) throws SQLException
 {
 checkTransaction();
 try
 {
 return new WrappedPreparedStatement(this, mc.getConnection()
 .prepareStatement(sql, resultSetType, resultSetConcurrency, resultSetHoldability));
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException
 {
 checkTransaction();
 try
 {
 return new WrappedPreparedStatement(this, mc.getConnection().prepareStatement(sql, autoGeneratedKeys));
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException
 {
 checkTransaction();
 try
 {
 return new WrappedPreparedStatement(this, mc.getConnection().prepareStatement(sql, columnIndexes));
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException
 {

 checkTransaction();
 try
 {
 return new WrappedPreparedStatement(this, mc.getConnection().prepareStatement(sql, columnNames));
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public CallableStatement prepareCall(String sql) throws SQLException
 {
 checkTransaction();
 try
 {
 return new WrappedCallableStatement(this, mc.prepareCall(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY));
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException
 {
 checkTransaction();
 try
 {
 return new WrappedCallableStatement(this, mc.prepareCall(sql, resultSetType, resultSetConcurrency));
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency,
 int resultSetHoldability) throws SQLException
 {

 checkTransaction();
 try
 {
 return new WrappedCallableStatement(this, mc.getConnection()
 .prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability));
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public String nativeSQL(String sql) throws SQLException
 {
 checkTransaction();
 try
 {
 return mc.getConnection().nativeSQL(sql);
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public void setAutoCommit(boolean autocommit) throws SQLException
 {
 checkStatus();
 mc.setJdbcAutoCommit(autocommit);
 }

 public boolean getAutoCommit() throws SQLException
 {
 checkStatus();
 return mc.isJdbcAutoCommit();
 }

 public void commit() throws SQLException
 {
 checkTransaction();
 mc.jdbcCommit();
 }

 public void rollback() throws SQLException
 {
 checkTransaction();
 mc.jdbcRollback();
 }

 public void rollback(Savepoint savepoint) throws SQLException
 {
 checkTransaction();
 mc.jdbcRollback(savepoint);
 }

 public DatabaseMetaData getMetaData() throws SQLException
 {
 checkTransaction();
 try
 {
 return mc.getConnection().getMetaData();
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public void setCatalog(String catalog) throws SQLException
 {
 checkTransaction();
 try
 {
 mc.getConnection().setCatalog(catalog);
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }
 public String getCatalog() throws SQLException
 {
 checkTransaction();
 try
 {
 return mc.getConnection().getCatalog();
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public void setTransactionIsolation(int isolationLevel) throws SQLException
 {
 checkStatus();
 mc.setJdbcTransactionIsolation(isolationLevel);
 }

 public int getTransactionIsolation() throws SQLException
 {
 checkStatus();
 return mc.getJdbcTransactionIsolation();
 }

 public SQLWarning getWarnings() throws SQLException
 {
 checkTransaction();
 try
 {
 return mc.getConnection().getWarnings();
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public void clearWarnings() throws SQLException
 {
 checkTransaction();
 try
 {
 mc.getConnection().clearWarnings();
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public Map getTypeMap() throws SQLException
 {
 checkTransaction();
 try
 {
 return mc.getConnection().getTypeMap();
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public void setTypeMap(Map typeMap) throws SQLException
 {
 checkTransaction();
 try
 {
 mc.getConnection().setTypeMap(typeMap);
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public void setHoldability(int holdability) throws SQLException
 {
 checkTransaction();
 try
 {
 mc.getConnection().setHoldability(holdability);
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public int getHoldability() throws SQLException
 {
 checkTransaction();
 try
 {
 return mc.getConnection().getHoldability();
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public Savepoint setSavepoint() throws SQLException
 {
 checkTransaction();
 try
 {
 return mc.getConnection().setSavepoint();
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public Savepoint setSavepoint(String name) throws SQLException
 {
 checkTransaction();
 try
 {
 return mc.getConnection().setSavepoint(name);
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public void releaseSavepoint(Savepoint savepoint) throws SQLException
 {
 checkTransaction();
 try
 {
 mc.getConnection().releaseSavepoint(savepoint);
 }
 catch (Throwable t)
 {
 throw checkException(t);
 }
 }

 public Connection getUnderlyingConnection() throws SQLException
 {
 checkTransaction();
 return mc.getConnection();
 }

 void checkTransaction() throws SQLException
 {
 checkStatus();
 mc.checkTransaction();
 }

 /**
 * The checkStatus method checks that the handle has not been closed and
 * that it is associated with a managed connection.
 *
 * @exception SQLException if an error occurs
 */
 protected void checkStatus() throws SQLException
 {
 if (closed)
 throw new SQLException("Connection handle has been closed and is unusable");
 if (mc == null)
 throw new SQLException("Connection handle is not currently associated with a ManagedConnection");
 }

 /**
 * The base checkException method rethrows the supplied exception, informing
 * the ManagedConnection of the error. Subclasses may override this to
 * filter exceptions based on their severity.
 *
 * @param e a <code>SQLException</code> value
 * @exception Exception if an error occurs
 */
 protected SQLException checkException(Throwable t) throws SQLException
 {
 if (mc != null)
 mc.connectionError(t);
 if (t instanceof SQLException)
 throw (SQLException) t;
 else
 throw new NestedSQLException("Error", t);
 }

 int getTrackStatements()
 {
 return trackStatements;
 }

 void registerStatement(WrappedStatement ws)
 {
 if (trackStatements == BaseWrapperManagedConnectionFactory.TRACK_STATEMENTS_FALSE_INT)
 return;

 synchronized (this)
 {
 if (statements == null)
 statements = new HashMap();

 if (trackStatements == BaseWrapperManagedConnectionFactory.TRACK_STATEMENTS_TRUE_INT)
 statements.put(ws, new Throwable("STACKTRACE"));
 else
 statements.put(ws, null);
 }
 }

 void unregisterStatement(WrappedStatement ws)
 {
 if (trackStatements == BaseWrapperManagedConnectionFactory.TRACK_STATEMENTS_FALSE_INT)
 return;
 synchronized (this)
 {
 if (statements != null)
 statements.remove(ws);
 }
 }

 void checkConfiguredQueryTimeout(WrappedStatement ws) throws SQLException
 {
 if (mc == null || dataSource == null)
 return;

 int timeout = 0;

 // Use the transaction timeout
 if (mc.isTransactionQueryTimeout())
 timeout = dataSource.getTimeLeftBeforeTransactionTimeout();

 // Look for a configured value
 if (timeout <= 0)
 timeout = mc.getQueryTimeout();

 if (timeout > 0)
 ws.setQueryTimeout(timeout);
 }

 Logger getLogger()
 {
 return log;
 }
}

[peiguo]

Is there a straight froward way? meaning whether JBoss supports XMType out of the box?

[cd_eat]

JBoss4.2.1 uses hibernate 3.2.4.sp1 as it's jpa implementation, and I'm not sure whether oracle xmlTypes has already been surpported in hibernate .

[peiguo]

The best document I can find on this is the following:
http://www.hibernate.org/118.html?cmd=prntdoc.

I take this as a clear indication that XMLType is not supported.

[peiguo]

Forgot to mention, read third last q&a on that page.

I was able to navigate to that page from Hibernate's home, so we can assume it speaks to the latest status.

[cd_eat]

yes, agree with that.
On the other hand, mapping db2 xml column is much easier, we can map db2 xmlcolumn to plain java string field, however we also have to modify hibernate src codes to mapp sql type 1111(db2 xml column) to java.lang.String to avoid the validation error.