Supporting Oracle’s xmltype column using Java and Spring – Part 1

Oracle driver(ojdbc6.jar) is not compliant with JDBC 4.0 when it comes to the usage of Types.SQLXML. Hence, either, we will have to make use of Oracle’s proprietary oracle.xdb.XMLType. Or, we need to depend on Oracle SQL functions to convert the clob to xmltype. Let me outline, the approaches which are currently available.

There are three ways in which you can insert XMLType data into an Oracle table containing xmltype column from Java:

a. Inserting the data in terms of CLOB in java and then internally convert the clob to XML-Type using XML() in Oracle
Click here for more information.

b. Using Oracle’s proprietary oracle.xdb.XMLType.
Click here for more information.

c. Use Spring Data JDBC Extensions project if you are Spring fanboy.

We will discuss the third approach which is the simplest of the lot:

1. If you are using Java 6 and Spring 3 then just include spring-data-oracle-1.0.0.RC1.jar . In addition, dont forget to include xdb6.jar(can be downloaded from Oracle site) . This jar provides the XMLType support and should be used along-side ojdbc6.jar

2. Configure SimpleJdbcTemplate

3. Configure the SQLXmlHandler for Oracle:


<bean id="sqlXmlHandler"
class="org.springframework.data.jdbc.support.oracle.OracleXmlHandler"/>

4. In your respective DAO class inject simpleJdbcTemplate and sqlXmlHandler

5. Add the below code snippet to enable the conversion from String to xmltype


simpleJdbcTemplate.update(
"INSERT INTO xml_table (id, xml_text) VALUES (?, ?)",
id,
sqlXmlHandler.newSqlXmlValue(xml));
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s