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: , , , , , , ,

7 comments:

Slack Aliss said...

Good post, Andy.

I particularly like the syntax colouring in the code samples.

I have one question on the coding, however. Do you have any evidence that this is truly an atomic operation? If the saveXml() fails, are you sure nothing is left in the DB due to the flush()?

abeacock said...

Slack Aliss, cheers for the nice comments. To answer your question regarding atomic behaviour my answer is "No I have evidence at all!"

Slack Aliss said...

Perhaps a follow-up posting would be appropriate on the subject of testing complex transactions for atomicity? ;)

Prajot Gawshinde said...

Really really a helpful entry.I was having the exact requirement and found the solution so accurate for me.

Andries Schutte said...

Thanks Andy! I was looking for exactly this. Hope you are doing well - I guess you're back in England now.

Andy B said...

Good to hear from you mate,I doing really well and I hope you are too!

Glad you found the blog post handy :D

Anonymous said...

We can also use Spring Data JDBC Extensions project to achieve the same. Need to include spring-data-oracle-1.0.0-RC1.jar and the required xdb.jar and xmlpaserv2.jar.