Spring JDBC and ORACLE XMLType Columns

If you have a table in the Oracle database with a XMLType column and you are using Spring JDBC to write your data access classes, how do you map your XMLType column parameter to the corresponding attribute on the Java DAO layer? I had confronted this and had figured out a implementation to achieve this. Would provide the working, in-production snippet of code segments, which would illustrate the implementation:

For insertion into database table:

Step1: Map the parameter on the Java DAO layer to the XMLType SQL attribute:

declareParameter(new SqlParameter("YOUR_XMLType_Column_Name", Types.OTHER));

Here, Types is java.sql.Types class

Step 2: Set the value for the XMLType column:

parametersMap.put(
"YOUR_XMLType_Column_Name" , new SqlTypeValue() {
     public void setTypeValue(PreparedStatement cs, int colIndx, int sqlType, String typeName) throws SQLException {
      Connection con = null;
      ((CallableStatement)cs).setObject(colIndx, new XMLType(getNativeJDBCConnection(con,getJdbcTemplate()), yourJavaObject.getXmlTypeValue()));
      if(null != con)
       con.close();
     }
    });

Assuming parametersMap HashMap carries the input parmeter

Here, 

SqlTypeValue is org.springframework.jdbc.core.SqlTypeValue

PreparedStatement is java.sql.PreparedStatement

SQLException is java.sql.SQLException

Connection is java.sql.Connection

CallableStatement is java.sql.CallableStatement

XMLType is oracle.xdb.XMLType

getNativeJDBCConnection helper method derives the native JDBC connection on the basis of the connection type specified. A sample implementation of this method is as follows:

private Connection getNativeJDBCConnection( Connection con, JdbcTemplate jdbcTemplate ) throws SQLException {
  NativeJdbcExtractorAdapter nativeJdbcExtractorAdapter = null;
  if (connectionType.equalsIgnoreCase("dbcp")) {
   nativeJdbcExtractorAdapter = new CommonsDbcpNativeJdbcExtractor();
   con = nativeJdbcExtractorAdapter.getNativeConnection(jdbcTemplate.getDataSource().getConnection());
  } else if (connectionType.equalsIgnoreCase("websphere")) {
   nativeJdbcExtractorAdapter = new WebSphereNativeJdbcExtractor();
   con = nativeJdbcExtractorAdapter.getNativeConnection(jdbcTemplate.getDataSource().getConnection());
  } else if (connectionType.equalsIgnoreCase("weblogic")) {
   nativeJdbcExtractorAdapter = new WebLogicNativeJdbcExtractor();
   con = nativeJdbcExtractorAdapter.getNativeConnection(jdbcTemplate.getDataSource().getConnection());
  } else {
   con = jdbcTemplate.getDataSource().getConnection();
  }
  return con;
 }

For reading a XMLType column value using a SQL from database table:

In the rowmapper, you can use the way as illustrated in the snippet below to read the XMLType column value:

if (rs.getObject(MarshForceProcessLogImpl.PROCESS_XML) != null) {    
    OPAQUE opq = ((OPAQUE) rs.getObject("YOUR_XMLType_Column_Name"));
    XMLType xt = oracle.xdb.XMLType.createXML(opq);
    processXmlData = xt.getStringVal();
   }

Here, 

OPAQUE is oracle.sql.OPAQUE

XMLType is oracle.xdb.XMLType

I have mostly used code in this segment to describe the way we can use XMLType Oracle columns in Spring JDBC based DAO Java layer. If you have any questions please feel free to drop a note and I can chip in more clarification. Also, if you want the physical java class, I can upload the same and share.

Hope this helps!