Supporting the Oracle XMLTYPE datatype via JPA (Spring, Hibernate & JDBC)

On a recent project I was tasked with developing two domain objects which mapped via JPA to a couple of tables. This would have been easy apart from one table used the the Oracle-specific XMLTYPE data type:

create table PERSON
(
 P_ID number not null,
 P_NAME varchar2(50),
 P_UPDATED date,
 P_XML xmltype
);

The XMLTYPE datatype is not supported by JPA (or any Hibernate-specific annotations) and so I had to use a different approach. I created the JPA-based Person class as normal, adding @Column annotations to the class, ignoring the P_XML column. I then added the following bit of code to be a placeholder for the XML:

@Transient
// required so that JPA doesn't try to persist it, we need JDBC for that
private String xml;

I then developed the JpaPersonDao as normal, using the getJpaTemplate() methods to select, insert and update the database. This handles all the columns except the XMLTYPE one - you need to use JDBC for that one due to the way in which Oracle expects the column to be filled and read.

My approach was to use the JPA-based DAO to perform most of the work loading and saving the rows, but hook in a JDBC-based DAO behind the scenes to handle the XMLTYPE column. By hiding it in the DAO, the users of the PersonDao will not have to worry about the special nature of the XMLTYPE column.

This is the JdbcPersonDao performing access to the XMLTYPE-based column only:

public class JdbcPersonDao extends JdbcDaoSupport {

   private static final String SELECT_XML_SQL = "select p.P_XML.getClobVal() from PERSON p where P_ID = ?";
   private static final String UPDATE_XML_SQL = "update PERSON set P_XML = xmltype(?) where P_ID = ?";

   public String getXml(Integer id) {
       Object[] args = { new Integer(id) };
       int[] argTypes = new int[] { Types.INTEGER };
       return (String) getJdbcTemplate().queryForObject(SELECT_XML_SQL, args, argTypes, String.class);
   }

   public void setXml(Integer id, String xml) {
       OracleLobHandler lobHandler = new OracleLobHandler();
       lobHandler.setNativeJdbcExtractor(new CommonsDbcpNativeJdbcExtractor());

       Object[] args = { new SqlLobValue(xml, lobHandler), new Integer(id) };
       int[] argTypes = new int[] { Types.CLOB, Types.INTEGER };
       getJdbcTemplate().update(UPDATE_XML_SQL, args, argTypes);
   }
}

And here are the important bits of the JpaPersonDao hooking into the JDBC-based one to ensure that the data stays consistent, both accesses to the database are within the same transaction and so are atomic:

public class JpaPersonDao extends JpaDaoSupport implements PersonDao {

   @Autowired
   private JdbcPersonDao jdbcPersonDao; // deals with the xmltype clob

   public Person getPersonById(Integer id) {
       Person person = getJpaTemplate().find(Person.class, id);

       if (person != null) {
           person.setXml(jdbcPersonDao.getXml(id));
       }

       return person;
   }

   public void savePerson(Person person) {
       getJpaTemplate().persist(person);
       getJpaTemplate().flush(); // forces the generation of an ID, required in the saveXml call
       saveXml(person);
   }

   public Person updatePerson(Person person) {
       // put the xml to one side as the merge clears out the transient field
       String xml = person.getXml();
       Person updatedPerson = getJpaTemplate().merge(person);
       updatedPerson.setXml(xml);
       saveXml(updatedPerson);

       return updatedPerson;
   }

   private void saveXml(Person person) {
       if (person.getXml() != null) {
           jdbcPersonDao.setXml(person.getId(), person.getXml());
       }
   }
}

Not the most elegant solution, but at least with the use of the @Autowired JDBC-based DAO the mess is hidden from the caller...

Technorati Tags: Java, Spring, JPA, JDBC, Hibernate, Oracle, XMLTYPE, Andrew Beacock