Mapping DB2 pureXML data using Hibernate User Types

PureXML is a native XML data management technology that provides hierarchical storage for XML documents, XML query languages and XML indexing capabilities, integrated with the relational capabilities.

 

By using Hibernate User Types one can map PureXML data into Java Objects.

 

This example presents a simple implementation of how to map pureXML data into a DOM Document. The Implementation was based on the sample database that comes with DB2. To create the sample database, one should type the command ‘db2sampl -xml’.

The first step is to create a Java entity that represents the table Product.

package db2.purexml.sample;

import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.Id;

import org.w3c.dom.Document;

@Entity
public class Product {
    
    @Id
    private String pid;
    private String name;
    private Double price;
    private Double promoprice;
    private Date promostart;
    private Date promoend;
    private Document description;
    
    // Getters and Setters
}

 

One should note that the column description is a DB2 native XML datatype that will be mapped to a DOM Document.

Now that the entity class is created, one needs to define the O/R mapping. In order to achieve this goal, Hibernate is capable of reading XML files that contain mapping definitions.

 

(purexml.hbm.xml)

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="db2.purexml.sample">
    <class name="Product">
        <id name="pid"/>
        <property name="name"/>
        <property name="price"/>
        <property name="promoprice"/>
        <property name="promostart"/>
        <property name="promoend"/>
        <property name="description" type="db2.purexml.sample.DB2XmlUserType"
            update="true" insert="true" access="property" column="DESCRIPTION" />
    </class>
</hibernate-mapping>

 

Please note that the class that maps the column description to the DOM Document is: db2.purexml.sample.DB2XmlUserType.

After defining the mapping, we need to create the user type class. In order to create this class one can follow one of two different approaches, depending on which DB2 driver is used.

If one is using db2jcc.jar driver which only includes JDBC 3.0 and earlier methods, one should use the following class:

 

 package db2.purexml.sample;

// Imports

public class DB2XmlUserType implements UserType {

     // ...

     public Object nullSafeGet(ResultSet rSet, String[] names, Object obj) throws HibernateException, SQLException {

          Document doc = null;
          final Reader reader = rSet.getCharacterStream(names[0]);

          if (reader != null) { 
               try {
                    final DocumentBuilderFactory builderFactory = DocumentBuilderFactory.newInstance();
                    builderFactory.setNamespaceAware(true);
                    final DocumentBuilder builder = builderFactory.newDocumentBuilder();
                    doc = builder.parse(new InputSource(reader));
               } catch (final Exception e) {
                    throw new HibernateException(e);
               } finally {
                    try {
                         reader.close();
                    } catch (final IOException e) {
                         LOG.error(MessageFormat.format("Could not close reader: {0}", e.getMessage()), e);
                    }
               }
          }

          return doc;
      }
    
     public void nullSafeSet(PreparedStatement stmt, Object value, int index) throws HibernateException, SQLException {


          if (value == null) {    
               stmt.setNull(index, Types.SQLXML);
          } else {
               final Source domSource = new DOMSource((Document) value);
               final Writer writer = new StringWriter();

               try {
                    final Result result = new StreamResult(writer);
                    Transformer transformer = TransformerFactory.newInstance().newTransformer();
                    transformer.transform(domSource, result);
                    stmt.setString(index, writer.toString());
               } catch (Exception e) {
                    throw new HibernateException(e);
               } finally {
                    try {
                         writer.close();
                    } catch (final IOException e) {
                         LOG.error(MessageFormat.format("Could not close writer: {0}", e.getMessage()), e);
                    }
               }
          }
     }

     // ...
}

 

On the other hand, if one is using db2jcc4.jar (requires Java 6 or later) which includes JDBC 4.0 and later methods, as well as JDBC 3.0 and earlier methods. The following class should be used for this purpose.

 

 package db2.purexml.sample;

// Imports

public class DB2XmlUserType implements UserType {

     // ...

     public Object nullSafeGet(final ResultSet rSet, final String[] names, final Object obj) throws HibernateException, SQLException {


          Document doc = null;
          Object db2xml = resolveDB2ResultSet(rSet).getObject(names[0]);

          if (db2xml != null) {
               final DOMSource domSource = ((SQLXML) db2xml).getSource(DOMSource.class);
               doc = (Document) domSource.getNode();
          }

          return doc;
     }

     protected DB2ResultSet resolveDB2ResultSet(final ResultSet rSet) {

          DB2ResultSet db2rs = null;

          if (rSet instanceof DB2ResultSet) {
               db2rs = (DB2ResultSet) rSet;
          } else {
               throw new UnsupportedOperationException("DB2 pureXML type support only available for DB2 JCC driver.");
          }

          return db2rs;
     }

     public void nullSafeSet(final PreparedStatement stmt, final Object value, final int index) throws HibernateException, SQLException {
          

          if (value == null) {    
               stmt.setNull(index, Types.SQLXML);
          } else {
               final SQLXML sqlxml = stmt.getConnection().createSQLXML();
               final DOMResult dom = sqlxml.setResult(DOMResult.class);
               dom.setNode((Document) value);
               stmt.setSQLXML(index, sqlxml);
          }
     }

    // ...
}

 

The two most important methods on these classes are nullSafeGet and nullSafeSet.

The nullSafeGet method is used whenever a record is being read from the database while nullSafeSet method is used whenever a record is to be persisted or if one is trying to construct a query by using the custom DOM Document type.

Using this approach, Hibernate can perform automatic conversion of the custom types. By the time the data was in our Java object, we would have a DOM Document class, however, when this object is persisted, we would get a DB2 native XML datatype.

Enjoy!

Pedro Ribeiro (ribeirux@gmail.com)