3.8.7.10. XML Column Mapping

Some databases support XML column types and XPath queries and indexes over these columns. OpenJPA supports mapping of an entity property mapped to an XML column on the following databases and their minimum versions.

  • DB2 9

  • MySQL 5.1.30

  • Oracle 9

  • PostgreSQL 8.3 (XML support must be compiled in, the minimum JDBC driver version is 8.3-603)

  • SQL Server 2005

See Supported Databases for possible database-specific restrictions.

Annotate the entity property using the XMLValueHandler strategy:

@Persistent
@Strategy("org.apache.openjpa.jdbc.meta.strats.XMLValueHandler")

The default fetch type is EAGER but can be changed to LAZY by using:

@Persistence(fetch=FetchType.LAZY)

The entity property class is required to have JAXB binding annotations. You can generate property class from an XML schema by using the xjc generator from the JAXB reference implementation. The xjc will generate the class along with the required annotations. Ensure that @XmlRootElement appears in the root class. In some cases this annotation needs to be added manually.

The entity property class is required to have getter and setter methods for all its fields. By default, the xjc will not generate setter methods for collections but you can force it to do so by using the setters plugin or collection setter injector plugin.

The JAXB jar files must be on the application classpath (jaxb-api.jar, jaxb-impl.jar, jsr173_1.0_api.jar or equivalent).

JPQL path expressions can navigate into the mapped class and its subfields to any level.

The path expression is rewritten into an equivalent XPath expression using SQL XML functions.

The path expression must be single-valued. Path expressions over XML mapped classes can only be used in the WHERE clause as an operand to a simple predicate (= <> < > >= ⇐).

Path expressions over XML mapped fields can not be:

  • an input to a JPQL scalar function

  • an operand of BETWEEN, IS NULL, LIKE or IN predicate

  • used to project out subfields in the SELECT clause

  • used in the FROM, GROUP BY, HAVING, ORDER BY clauses

XML schema must not contain namespace declarations. The JPQL path expressions can not refer to Java fields generated from XML ANY type or XML mixed element types.

The data type generated by JAXB must be a valid type to use the property in a JPQL predicate.

Shown below is a sample XML schema myaddress.xsd, in which the JPA entity Order has shipAddress persistent field that maps to an XML column.

Example 119. myaddress.xsd
<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" >

<xs:complexType name="Address">
 <xs:sequence>
  <xs:element name="Name" type="xs:string" />
  <xs:element name="Street" type="xs:string"
  	minOccurs="1" maxOccurs="3" />
  <xs:element name="City" type="xs:string" />
</xs:sequence>
</xs:complexType>

<xs:complexType name="CAN_Address">
 <xs:complexContent>
  <xs:extension base="Address">
   <xs:sequence>
    <xs:element name="Province" type="xs:string" />
    <xs:element name="PostalCode" type="xs:string" />
   </xs:sequence>
  </xs:extension>
</xs:complexContent>
</xs:complexType>

<xs:simpleType name="USPS_ZIP">
 <xs:restriction base="xs:integer">
  <xs:minInclusive value="01000" />
  <xs:maxInclusive value="99999" />
 </xs:restriction>
</xs:simpleType>

<xs:complexType name="USA_Address">
 <xs:complexContent>
  <xs:extension base="Address">
   <xs:sequence>
    <xs:element name="State" type="xs:string" />
    <xs:element name="ZIP" type="USPS_ZIP" />
   </xs:sequence>
  </xs:extension>
 </xs:complexContent>
</xs:complexType>

<xs:element name="MailAddress" type="Address" />
<xs:element name="AddrCAN" type="CAN_Address"
   substitutionGroup="MailAddress" />
<xs:element name="AddrUSA" type="USA_Address"
   substitutionGroup="MailAddress" />
</xs:schema>

Java classes Address, USAAddress and CANAddress are produced from myaddress schema by using the xjc generator.

Example 120. Address.java
    ...
@XmlRootElement
@XmlAccessorType(XmlAccessType.FIELD)
@XmlType(name = "Address", propOrder = {
    "name",
    "street",
    "city"
})
public class Address {
    @XmlElement(name = "Name", required = true)
    protected String name;
    @XmlElement(name = "Street", required = true)
    protected List<String> street;
    @XmlElement(name = "City", required = true)
    protected String city;

   /**
     * Getter and Setter methods.
     *
     */
     ...
}
Example 121. USAAddress.java
    ...
@XmlRootElement
@XmlAccessorType(XmlAccessType.FIELD)
@XmlType(name = "USA_Address", propOrder = {
    "state",
    "zip"
})
public class USAAddress
    extends Address
{

    @XmlElement(name = "State")
    protected String state;
    @XmlElement(name = "ZIP")
    protected int zip;

   /**
     * Getter and Setter methods.
     *
     */
     ...
}
Example 122. CANAddress.java
     ...
@XmlRootElement
@XmlAccessorType(XmlAccessType.FIELD)
@XmlType(name = "CAN_Address", propOrder = {
    "province",
    "postalCode"
})
public class CANAddress
    extends Address
{

    @XmlElement(name = "Province")
    protected String province;
    @XmlElement(name = "PostalCode")
    protected String postalCode;

   /**
     * Getter and Setter methods.
     *
     */
     ...
}
Example 123. Showing annotated Order entity with XML mapping strategy
@Entity
public class Order {
    @Id  private into id;
    @Persistent
    @Strategy ("org.apache.openjpa.jdbc.meta.strats.XMLValueHandler")
    private Address shipAddress;
    ...
}
Example 124. Showing creation of Order entity having shipAddress mapped to XML column
...
myaddress.ObjectFactory addressFactory = new myaddress.ObjectFactory();
Customer c1 = new Customer();
c1.setCid( new Customer.CustomerKey("USA", 1) );
c1.setName("Harry's Auto");
Order o1 = new Order( 850, false, c1);
USAAddress addr1 = addressFactory.createUSAAddress();
addr1.setCity("San Jose");
addr1.setState("CA");
addr1.setZIP(new Integer("95141"));
addr1.getStreet().add("12500 Monterey");
addr1.setName( c1.getName());
o1.setShipAddress(addr1);
em.persist(o1);
...
Example 125. Sample JPQL queries for XML column mapping
 select o from Order o where o.shipAddress.city = "San Jose" or
			      o.shipAddress.city = "San Francisco"  (OK)

 select o.shipaAddress from Order o  (OK)

 select o.shipAddress.city from Order o  (INVALID)

 select o from Order o where o.shipAddress.street = "San Jose"  (INVALID multi-valued)