Uploading XML into XMLTYPE column in database with Spring JDBCTemplate

Couple of days ago I had been really struggling on how to upload and store an XML file into an XMLTYPE column in the database.  I have uploaded with no difficulty at all XML files into Oracle db table columns of type CLOB and BLOB but XMLTYPE columns seemed to through me for a loop. I would get the following error no matter what I tried:

ORA-01461 can bind a LONG value only for insert into a LONG column

This was very perplexing and searching the web on how to code this type of upload seemed to yield next to no results.  I thought putting down the steps I performed to accomplish this might help someone else struggling with this.

Lets start with a DAO class and walk through the code and what is needed:

UploadObjectDAOImpl.java – this class utilizes Spring JdbcTemplate to perform an insert into the database.

package com.mdrsolutions.web.dao.impl;

import com.mdrsolutions.external.sql.SqlCalls;
import com.mdrsolutions.web.dao.UploadObjectDao;
import com.mdrsolutions.web.dao.entity.UploadObject;
import com.mdrsolutions.web.dao.util.CustomPreparedStatementSetter;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.NoResultException;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository("uploadObjectDao")
@Transactional
public class UploadObjectDAOImpl<T extends UploadObject> extends BaseDaoImpl implements UploadObjectDAO<T> {

   private static final Logger logger = Logger.getLogger(UploadObjectDAOImpl.class);
   private static final String UPLOAD_XML_SQL_PATH = "db/scripts/application/uploadXML.sql"; 

   @Autowired
   private CustomPreparedStatementSetter customPreparedStatementSetter;

   @Override
   public void upload(final T t) throws SQLException {

       String uploadXMLSql = SqlCalls.getSql(UPLOAD_XML_SQL_PATH);  

       PreparedStatementSetter ps = customPreparedStatementSetter.create(getJdbcTemplate(), t);

       int update = getJdbcTemplate().update(uploadXMLSql, ps);
       logger.debug("Succefully updated table = " + update);
   }
}

Lets examine real briefly a couple of things about this class.

On line 29 you will notice the declaration of CustomPreparedStatementSetter which is used at line 36 passing in a reference to jdbcTemplate object as well as T object representing the UploadObject.

On line 34, a class SqlCall.getSql(path) retrieves the sql statement and assigns it to the String uploadXMLSql.

On line 38, jdbcTemplate.update method is called passing in both the updateXMLSql and the PreparedStatementSetter.

Concerning line 34, you could replace this with any valid insert sql statement. Line 34 basically represents the following SQL:


INSERT INTO XML_OBJECT
( DOC_ID
, RECVD_DATE
, XML_DATA)
values (?, ?, ?)

As you can tell it is a very basic Prepared INSERT SQL statement.

Line 34 of the UploadObjectDAOImpl.java demonstrates declaration of CustomPreparedStatementSetter class which by far is the most important part of this tutorial; lets take a look at that class next.

CustomPreparedStatementSetterImpl.java

package com.mdrsolutions.web.dao.util.impl;

import com.mdrsolutions.web.dao
import com.mdrsolutions.web.dao.entity.UploadObject;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import oracle.jdbc.OracleConnection;
import oracle.xdb.XMLType;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.nativejdbc.SimpleNativeJdbcExtractor;
import org.springframework.stereotype.Component;

@Component("customPreparedStatementSetter")
public class CustomPreparedStatementSetterImpl implements CustomPreparedStatementSetter {

 private static final Logger logger = Logger.getLogger(CustomPreparedStatementSetterImpl.class);

 @Override
 public PreparedStatementSetter create(JdbcTemplate jdbcTemplate, final UploadObject t) throws SQLException {
    SimpleNativeJdbcExtractor jdbcExtractor = new SimpleNativeJdbcExtractor();

    //XMLType insert requires the use of an OracleConnection,
    //which is usually wrapped inside the jdbcTemplate connection object
    Connection nativeConnection =
               jdbcExtractor.getNativeConnection(jdbcTemplate.getDataSource().getConnection());

    OracleConnection conn = null;
    //Now, we have to unwrap the OracleConnection
    if (nativeConnection.isWrapperFor(OracleConnection.class)) {
        conn = (OracleConnection) nativeConnection.unwrap(OracleConnection.class);
    } else {
        conn = (OracleConnection) nativeConnection;
    }

    //Next, we have to wrap the byte array in an InputStream to accepted
    InputStream is = new ByteArrayInputStream(t.getObject().getBytes());

    //Then, instantiate an XMLType object by using native OracleConnection and InputStream of the byte array object
    final XMLType xmldoc = new XMLType(conn, is);

    //Finally create a Spring PreparedStatementSetter object and set the below prepared statement
    PreparedStatementSetter xmlPs = new PreparedStatementSetter() {

        @Override
        public void setValues(PreparedStatement ps) throws SQLException {
             ps.setString(1, t.getDocId());
             ps.setDate(2, new java.sql.Date(t.getRecvdDate().getTime()));
             ps.setObject(3, xmldoc);
        }
    };

   return xmlPs;
   }
}

Please take note of the comments made throughout this class. Because, we are using JdbcTemplate, we don’t have to worry about closing connections here. Spring JdbcTemplate handles that for us, which makes this such an easier implementation.

Ok, so lets look at lines 32 through 41 briefly.  This part gave me a head ache because the documentation doesn’t clearly define the use of a regular Connection object versus an OracleConnection object, which you have to have to make this work. If you are using a Tomcat jndi connection to get your DataSource, you will need to ensure that the accessToUnderlyingConnectionAllowed=”true” in your context.xml file for Tomcat.  I included an example below to help out with this.


<Context>
    <Resource auth="Container"
        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
        type="javax.sql.DataSource"
        testWhileIdle="true"
        testOnBorrow="true"
        testOnReturn="true"
        validationQuery="SELECT 1"
        validationInterval="30000"
        timeBetweenEvictionRunsMillis="30000"
        maxActive="100"
        midIdle="10"
        maxWait="10000"
        initialSize="10"
        removeAbandonedTimeout="60"
        accessToUnderlyingConnectionAllowed="true"
        removeAbandoned="true"
        minEvicttableIdleTimeMillis="30000"
        jmxEnabled="true"              
        jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
        driverClassName="oracle.jdbc.OracleDriver"
        name="jdbc/objectUploadDB" password="tomcat"
        url="jdbc:oracle:thin:@testdb.myoracle.com:1521:DEV" username="tomcat"/>
</Context>

So, by having this attribute set to true it basically enables the Connection to be unwrapped to get at the OracleConnection, which we have to have to continue.

So, looking at line 43 – 47 we see the following:

//Next, we have to wrap the byte array in an InputStream to accepted
InputStream is = new ByteArrayInputStream(t.getObject().getBytes());

//Then, instantiate an XMLType object by using native OracleConnection and InputStream of the byte array object
final XMLType xmldoc = new XMLType(conn, is);

Oracle requires that in order to insert XML into a column of type XMLType that you must wrap in a Java XMLType obect. Like I said at the beginning of this post, normal insertion of XML into CLOB or BLOB database fields is not difficult at all but if the column type is XMLType, it gets a little more involved.

So, we first have to convert the byte array into an InputStream and then we have to take the native OracleConnection and pass as a parameter into XMLType() constructor. This constructor takes two parameters: Connection and either String, byte[], or InputStream.

Concerning that second parameter, I tried the other two parameter types of String and byte[] but it generates a strange Oracle error (that I didn’t write down) but using an InputStream that wraps a byte[] will work every-time (or at least that’s what my testing shows so far).

And, concerning the first parameter, the documentation doesn’t say that a simple Connection object is sufficient – you end up finding out from the error messages that an OracleConnection is required.

OK, so that is about it. Looking back at line 38 in UploadObjectDAOImpl.java,  you will see the

int update = getJdbcTemplate().update(uploadXMLSql, ps);

This takes the insert sql statement and the PreparedStatementSetter and performs an insert/update with returning the number of rows affected.

Some may be wondering where do I get the

getJdbcTemplate()

method at. Well, I have a BaseDaoImpl which has that getJdbcTemplate() method for me to use and because I extend from BaseDaoImpl…well if you understand basic Java you should be able to infer the rest.