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.


How to externalize SQL into separate sql files for JDBC queries

The other day I was working on a project where I had to troubleshoot some sql that was nested in a java class file. After copying and pasting the sql statement, I had to spend sometime removing all of the concatenation symbols used to make it readable on a java page…you know instead of having one long line scrawled across the page. After I fixed it,  I had to go back and re-add all of those nasty “+” symbols and extra quotes. I thought to myself wouldn’t be nice if I could just store that sql in a separate file like a normal sql file.

So, I came up with idea of a Java class that could reference those files and read them back in. Its a first stab at it but it worked and made debugging the sql file much easier as well as easy to read.

package com.mdrsolutions.dao.sql;

import java.io.InputStream;
import java.io.InputStreamReader;
import com.google.common.io.CharStreams;
import com.google.common.io.Closeables;
import java.io.BufferedReader;
import java.io.IOException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.log4j.Logger;
import org.springframework.core.io.FileSystemResource;

public class SqlCalls {

    public static final String RETRIEVE_IMAGE_ALL_SQL = "db/sql/retrieve_group_all.sql";
    public static final String UPDATE_IMAGE_AUDIT_SQL = "db/sql/update_group.sql";

    private SqlCalls() {}

    /**
    * Method takes a file path String and returns the String representation of
    * that sql file
    *
    * @param filePath of location of the sql file
    * @return a String of the contents of the SQL file
    */
    public static final String getSql(String filePath) {
        String sql = "";

        try {
          //get the file stream object
          InputStream is = SqlCalls.class.getClassLoader().getResourceAsStream(filePath);

          //convert to String and close stream
          sql = CharStreams.toString(new InputStreamReader(is));
          if (null == sql || sql.isEmpty()){
            Closeables.closeQuietly(is);
            throw new IOException("File path to SQL file could not be read!");
          }

          Closeables.closeQuietly(is);

          return sql;
       } catch (IOException ex) {
           //log your error
           return sql;
       }
    }
}

On the calling class I simply called the static method

SqlCalls.getSql(String path)

like so with a little Spring JdbcTemplate magic:


@Repository("groupDao")
public class GroupDaoImpl extends BaseDaoImpl implements GroupDao {

    public GroupDaoImpl(DataSource dataSource){
        super(dataSource)
    }

    @Override
    public void update(final List<Group> groups){
        String updateSQL = SqlCalls.getSql(SqlCalls.UPDATE_GROUP_SQL);
        int[] batchUpdate = getJdbcTemplate().batchUpdate(updateSQL, new BatchPreparedStatementSetter(){
              @Override
              public void setValues(PreparedStatement ps, int i) throws SQLException {

                   Group grp = groups.get(i);
                   ps.setString(1, grp.getName());
                   ps.setString(2, grp.getLocation());
              }

              @Override
              public int getBatchSize() {
                   return groups.size();
              }
        }
    }
}

I supplied the path as static variable to the sql file and it worked like a charm. I was able to externalize my sql, maintain nice formatting, and be able to quickly pull it up and test and debug it in my favorite SQL developer IDE.

I stored my sql files under the project directory structure like so:


src/main/resources/db

In the future, it might be cool to take this to a different level and allow the application to scan the directory of sql files on start up of application and store them in a HashMap for easier retrieval, then maybe use the key to get the sql…just some thoughts…