To use the Oracle XML support you need to use a couple of jar files available in the Oracle XML Developers Kit download available from Oracle. You need xdb.jar and also the xmlparserv2.jar since the XMLType depends on this parser library.

There is optional support for Spring's Object/XML Mapping support. If you use this support then you would also need the Spring Web Services project, version 1.0 or 1.5. The jar files needed from this project are spring-xml.jar, spring-oxm.jar and spring-oxm-tiger.jar.

All samples in this chapter access a table named "xml_table". Here is the DDL to create this table:

CREATE TABLE xml_table (
    id NUMBER(10),
    xml_text XMLTYPE,
    PRIMARY KEY (id));

To write XML data to a table you need to pass in the XML using a custom SqlTypeValue. In this implementation you would be responsible for setting the parameter value for the XML column in accordance with the API provided by the database driver.

For Oracle we provide a database specific implementation of an SqlXmlValue, which is an extension of the SqlTypeValue, that is easier to use. It works together with an SqlXmlHandler and adds an abstraction layer on top of the database specific APIs provided by the database vendors. There is a new SQLXML datatype in JDBC 4.0 that provides an abstraction, but so far it is not widely implemented.

In this example we have an XML value that we pass in as the second parameter. This XML value can be in the form of a String or an org.w3c.dom.Document. We use an SqlXmlHandler instance to gain access to a new instance of the SqlXmlValue. For the Oracle support the implementation classes are OracleXmlHandler and OracleXmlTypeValue respectively.

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

The implementation of the SqlXmlHandler is chosen in the data access configuration file and should be injected into the DAO or Repository class.

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

Oracle's XMLType supports passing in an java.io.InputStream but since this is not supported by the JDBC 4.0 SQLXML datatype you will have to use the Oracle specific OracleXmlTypeValue directly.

    simpleJdbcTemplate.update(
            "INSERT INTO xml_table (id, xml_text) VALUES (?, ?)",
            id,
            new OracleXmlTypeValue(is));

Running a query against a table with an XMLTYPE column requires a RowMapper that can handle retrieval of the XMLType and the corrsponding XML data. The OracleXmlHandler provides several methods that supports easy access to the XML data. It is typically used in a RowMapper.

    String s = simpleJdbcTemplate.queryForObject(
            "SELECT xml_text FROM xml_table WHERE id = ?",
            new ParameterizedRowMapper<String>() {
                public String mapRow(ResultSet rs, int i) throws SQLException {
                    String s = sqlXmlHandler.getXmlAsString(rs, 1);
                    return s;
                }
            },
            id);

The XML data can be retreived as a String, a java.io.InputStream, a java.io.Reader or a javax.xml.transform.Source.

To map an object to XML and write this XML to a table you first need to use marshalling support available from the Spring Web Services project. Once the object data is marshalled to XML we can write the XML to a column in a database table. The latter part is very similar to the the XML support discussed above. We need to pass in the XML using a custom SqlTypeValue. In the object mapping implementation you would be responsible for marshalling the object to XML before setting the parameter value.

In this example we have an object that needs to be marshalled to XML. We are using a Marshaller provided by the Spring Web Services project. The marshaller is typically configured and then injected into the DAO or Repository. Here is an example configuration using the JAXB 2.0 support. In addition to JAXB 2.0, there is also support for JAXB 1.0, Casto, XML Beans, JiBX and XStream.

    <bean id="marshaller" class="org.springframework.oxm.jaxb.Jaxb2Marshaller">
        <property name="classesToBeBound">
            <list>
                <value>org.springframework.data.jdbc.samples.Item</value>
            </list>
        </property>
    </bean>

The JAXB 2.0 class that we are marshalling is a typical javaBean and it uses annotations for the meta data so there is no additional configuration needed.

package org.springframework.data.jdbc.samples;

import javax.xml.bind.annotation.*;
import java.math.BigDecimal;

@XmlRootElement(name = "item")
@XmlType(propOrder = {"name", "price"})
public class Item{
    private Long id = 0L;
    private String name;
    private BigDecimal price;

    @XmlAttribute(name="id")
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    @XmlElement(name = "item-name")
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @XmlElement(name = "price")
    public BigDecimal getPrice() {
        return price;
    }

    public void setPrice(BigDecimal price) {
        this.price = price;
    }


    public String toString() {
        return "[" + id + "] " + name + " " + price;
    }
}

For Oracle we provide a database specific implementation of an SqlXmlMarshallingValue, which is an extension of the SqlXmlValue, that is easier to use. It works together with an SqlXmlObjectMappingHandler similar to the SqlXmlHandler that we used in the previous example. The object to be marshalled is passed in when the new instance of the SqlXmlValue is created.

For our dabase insert we pass in the marshalled value as the second parameter. The first parameter is the id of the object, and this will be use as the primary key for the row. We use an SqlXmlHandler instance to gain access to a new instance of the SqlXmlMappingValue. For the Oracle support the implementation classes are OracleXmlObjectMappingHandler and OracleXmlMarshallingValue respectively.

    simpleJdbcTemplate.update(
            "INSERT INTO xml_table (id, xml_text) VALUES (?, ?)",
            item.getId(),
            sqlXmlObjectMappingHandler
                    .newMarshallingSqlXmlValue(item));

The implementation of the SqlXmlObjectMappingHandler is chosen in the data access configuration file and should be injected into the DAO or Repository class.

<bean id="sqlXmlHandler" 
    class="org.springframework.data.jdbc.support.oracle.OracleXmlObjectMappingHandler">
    <property name="marshaller" ref="marshaller"/>
</bean>

Last piece we need is reading the XML from the database and have it unmarshalled to an Item object. We will perform this work in a RowMapper together with the SqlXmlObjectMappingHandler.

    Item i = simpleJdbcTemplate.queryForObject(
            "SELECT xml_text FROM xml_table WHERE id = ?",
            new ParameterizedRowMapper<Item>() {
                public Item mapRow(ResultSet rs, int i) throws SQLException {
                    return (Item) sqlXmlObjectMappingHandler
                            .getXmlAsObject(rs, 1);
                }
            },
            id);

The XML data is unsmarshalled using an Unmarshaller which in the JAXB 2.0 case is also implemented by the Jaxb2Marshaller class. It must be injected into the unmarshaller property of the SqlXmlObjectMappingHandler. Since marshalling and unmarshalling is performed by the same object we pass in the bean named marshaller for the unmarshaller property.

<bean id="sqlXmlHandler" 
    class="org.springframework.data.jdbc.support.oracle.OracleXmlObjectMappingHandler">
    <property name="unmarshaller" ref="marshaller"/>
</bean>