I recently migrated 2 apps from the terribly ancient version of iBatis 2.3.4.726 to MyBatis 3.0.5 in order to comply with enterprise development standards and the experience was pretty smooth overall, with the help of this brief guide as well as the MyBatis documentation. The main pain came from Oracle’s XMLType. As XMLType is not a native JDBC type, a custom typehandler was created for iBatis. Due to the migration, some code tweaking needed to be done to get XMLType support running. For those who might be facing the same problems, I’ll run through some of the pitfalls.

First of all, the type handler for XMLType (found many months ago in the depths of the interweb after some googling) in ibatis 2.3.4.726 would have to change from this:

package project.callback;

import java.io.StringReader;
import java.sql.SQLException;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.xml.sax.InputSource;

import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;
import oracle.sql.OPAQUE;
import oracle.xdb.XMLType;
import oracle.xml.parser.v2.XMLParseException;

import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;

/**
 * Custom type handler to map <code>oracle.xdb.XMLType</code> to
 * <code>org.w3c.dom.Document</code>. More specifically it allows us to map an
 * Oracle XMLType column onto a XML Document.
 */
public class XMLTypeHandlerCallback implements TypeHandlerCallback
{

	public Object getResult(ResultGetter getter) throws SQLException
	{
		if (getter.getResultSet() instanceof OracleResultSet)
		{
			OPAQUE opaqueValue = getOpaqueValue(getter);
			if (opaqueValue != null)
			{
				XMLType xmlResult = XMLType.createXML(opaqueValue);
				return xmlResult.getDocument();
			}
			else
			{
				return (Document) null;
			}
		}
		else
		{
			throw new UnsupportedOperationException("XMLType mapping only supported for Oracle RDBMS");
		}
	}

	public void setParameter(ParameterSetter setter, Object parameter) throws SQLException
	{
		if (setter.getPreparedStatement() instanceof OraclePreparedStatement)
		{
			OraclePreparedStatement ops = (OraclePreparedStatement) setter.getPreparedStatement();
			if (parameter == null)
			{
				ops.setNull(setter.getParameterIndex(), oracle.jdbc.OracleTypes.OPAQUE, "SYS.XMLTYPE");
			}
			else
			{
				XMLType xmlInput = XMLType.createXML(ops.getConnection(), (Document) parameter);
				// System.out.println(xmlInput.getStringVal());
				ops.setObject(setter.getParameterIndex(), xmlInput);
			}
		}
		else
		{
			throw new UnsupportedOperationException("XMLType mapping only supported for Oracle RDBMS");
		}
	}

	public Object valueOf(String s)
	{
		try
		{
			DocumentBuilder db = DocumentBuilderFactory.newInstance().newDocumentBuilder();
			return db.parse(new InputSource(new StringReader(s)));
		}
		catch (Exception e)
		{
			if (e instanceof XMLParseException)
			{
				throw new IllegalArgumentException("Argument for valueOf() doesn't describe a XML Document");
			}
			else
			{
				throw new RuntimeException("Error creating XML document.  Cause: " + e);
			}
		}
	}

	private OPAQUE getOpaqueValue(ResultGetter getter) throws SQLException
	{
		OracleResultSet ors = (OracleResultSet) getter.getResultSet();
		OPAQUE op = null;
		if (getter.getColumnName() != null)
		{
			op = ors.getOPAQUE(getter.getColumnName());
		}
		else
		{
			op = ors.getOPAQUE(getter.getColumnIndex());
		}
		return op;
	}
}

to this version that we modified to be compatible with MyBatis 3.0.5:

package project.callback;

import java.io.StringReader;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.w3c.dom.Document;
import org.xml.sax.InputSource;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;
import oracle.sql.OPAQUE;
import oracle.xdb.XMLType;
import oracle.xml.parser.v2.XMLParseException;

/**
 * Custom type handler to map <code>oracle.xdb.XMLType</code> to
 * <code>org.w3c.dom.Document</code>. More specifically it allows us to map an
 * Oracle XMLType column onto a XML Document.
 */
public class XMLTypeHandlerCallback implements TypeHandler
{
	final static Logger logger = LoggerFactory.getLogger(XMLTypeHandlerCallback.class);

	@Override
	public Object getResult(ResultSet resultSet, String columnName) throws SQLException
	{
		if (resultSet.unwrap(OracleResultSet.class) instanceof OracleResultSet)
		{
			OracleResultSet oracleResultSet = (OracleResultSet) resultSet.unwrap(OracleResultSet.class);
			OPAQUE opaqueValue = oracleResultSet.getOPAQUE(columnName);
			if (opaqueValue != null)
			{
				XMLType xmlResult = XMLType.createXML(opaqueValue);
				return xmlResult.getDocument();
			}
			else
			{
				return (Document) null;
			}
		}
		else
		{
			throw new UnsupportedOperationException("XMLType mapping only supported for Oracle RDBMS LOL");
		}
	}

	@Override
	public Object getResult(CallableStatement callableStatement, int columnIndex) throws SQLException
	{
		if (callableStatement.unwrap(OracleCallableStatement.class) instanceof OracleCallableStatement)
		{
			OracleCallableStatement oracleCallableStatement = (OracleCallableStatement) callableStatement.unwrap(OracleCallableStatement.class);
			OPAQUE opaqueValue = oracleCallableStatement.getOPAQUE(columnIndex);
			if (opaqueValue != null)
			{
				XMLType xmlResult = XMLType.createXML(opaqueValue);
				return xmlResult.getDocument();
			}
			else
			{
				return (Document) null;
			}
		}
		else
		{
			throw new UnsupportedOperationException("XMLType mapping only supported for Oracle RDBMS");
		}
	}

	@Override
	public void setParameter(PreparedStatement preparedStatement, int i, Object parameter, JdbcType jdbcType) throws SQLException
	{
		if (preparedStatement.unwrap(OraclePreparedStatement.class) instanceof OraclePreparedStatement)
		{
			OraclePreparedStatement oraclePreparedStatement = (OraclePreparedStatement) preparedStatement.unwrap(OraclePreparedStatement.class);
			if (parameter == null)
			{
				oraclePreparedStatement.setNull(i, oracle.jdbc.OracleTypes.OPAQUE, "SYS.XMLTYPE");
			}
			else
			{
				XMLType xmlInput = XMLType.createXML(oraclePreparedStatement.getConnection(), (Document) parameter);
				oraclePreparedStatement.setObject(i, xmlInput);
			}
		}
		else
		{
			throw new UnsupportedOperationException("XMLType mapping only supported for Oracle RDBMS");
		}

	}

	public Object valueOf(String s)
	{
		try
		{
			DocumentBuilder db = DocumentBuilderFactory.newInstance().newDocumentBuilder();
			return db.parse(new InputSource(new StringReader(s)));
		}
		catch (Exception e)
		{
			if (e instanceof XMLParseException)
			{
				throw new IllegalArgumentException("Argument for valueOf() doesn't describe a XML Document");
			}
			else
			{
				throw new RuntimeException("Error creating XML document.  Cause: " + e);
			}
		}
	}
}

The main changes to take note of here are that we now

  • implement TypeHandler instead of TypeHandlerCallback
  • unwrap the SQL constucts instead of just doing an instanceof comparison, since these constucts are now wrapped in proxy objects

In addition, we can no longer use the old style of declaring custom typehandlers in the main sqlConfig.xml and resultMaps in sqlMap.xml. The relevant parts of the iBatis 2.3.4.726 xml look like this:

<!--sqlMapConfig.xml-->
<typeHandler javaType="org.w3c.dom.Document" jdbcType="XMLTYPE" callback="project.callback.XMLTypeHandlerCallback"/>

<!--sqlMap.xml-->
<resultMap id="PurchaseOrderMap" class="project.dto.PurchaseOrder" >
	<result property="orderInfo" jdbcType="XMLTYPE"/>
</resultMap>

For MyBatis 3.0.5, we’ll have to declare them as such:

<!--sqlMapConfig.xml-->
<typeHandlers>
	<typeHandler javaType="org.w3c.dom.Document" handler="project.callback.XMLTypeHandlerCallback"/>
</typeHandlers>

<!--sqlMap.xml-->
<resultMap id="PurchaseOrderMap" type="project.dto.PurchaseOrder" >
	<result property="orderInfo" javaType="org.w3c.dom.Document" typeHandler="project.callback.XMLTypeHandlerCallback"/>
</resultMap>

As it turns out, we no longer have to specify jdbcType. Instead, we specify the custom typehandler we wish to use to handle our esoteric datatype. With that out of the way, let’s get back to the joy of programming with MyBatis and XMLType!

ps: For those who are interested in finding more about XMLType, apart from the typical white papers and slides that you can find on OTN, another good resource for XMLType info is Marco Gralike’s blog.