Using XMLTYPE with ADF Business Components

What is XMLType ?

From the official Oracle documentation:

XMLType is a system-defined opaque type for handling XML data. It has predefined member functions on it to extract XML nodes and fragments. You can create columns of XMLType and insert XML documents into it. You can also generate XML documents as XMLType instances dynamically using the SYS_XMLGEN and SYS_XMLAGG SQL functions.

In nutshell, you can use XMLType data type, to facilitate native handling of XML data in the database.  More details about XMLType  can be found here: http://docs.oracle.com/cd/E11882_01/appdev.112/e23448/t_xml.htm

Using XMLTYPE with ADF BC

In this post, I'm sharing a sample ADF application that reads and updates a database table with XMLTYPE  column type.  My primary objective was to enable the support  for XMLTYPE in ADF business components in pure 'Java' way.  Let me make it more clear. If you have used  Hibernate before, you might have noticed the org.hibernate.usertype.UserType  which will help you to add custom data types. Do we have such mechanism in ADF BC to support  some unconventional database types which is not directly supported by the framework? Well answer is Yes, however the implementation needs few more extra steps than what you do in Hibernate. Apparently, this post may help you to understand how you can enhance business components to add support for some database table column type which is not directly supported by the framework out of the box.

The high level architecture of the solution that I used is given here:
The below section contains the steps that I followed for building this sample. These steps may also help you to understand the hooks offered by ADF Business Components to support new data types. It's fun if you love coding and OK to dirty your hands a bit, stay tuned.

Step 1: First step is to know the support offered by your JDBC driver to read XMLType from database. Though there is java.sql.SQLXML type, the JDBC driver that I used for connecting to Oracle database does not implement this type. So I had to settle on oracle.xdb.XMLType, which is an Oracle proprietary extension to read XMLType from database.
The class oracle.xdb.XMLType is packed in xdb6.jar. Along with this, you may also need xmlparserv2.jar  and ojdbc6.jar. Luckily the later two jars come with your ADF runtime by default.  The challenge here is grabbing the right version of xdb6.jar that works with JDeveloper. To make your life simple I'm packaging the xdb6.jar in the sample application workspace that I shared at the end of this post.
Add the xdb6.jar to the Classpath entries using the Project Properties dialog.

Step 2:  When you create model project, you  might have noticed an option to specify the Data Type map in the very first screen(Initialize Model Project). The business components use  this type map entry definitions at runtime to map a Database column type to a java-class type and also to jdbc-sqltype and id. To add support for a new data type, you must extend the appropriate type map entry class to include the new type. Later you can configure the application module  to use your class for finding the type mapping entries.

The OracleTypeMapEntriesEx class that I'm using for this example look like as follows.  To get a real feel of the implementation, you can take look at the source of framework.bc.extension.OracleTypeMapEntriesEx class in the sample work space that is attached at the end of this post(See Download section).

public class OracleTypeMapEntriesEx extends 
    OracleAppsTypeMapEntries {
    static public byte BCXMLTYPE = 75;

    public OracleTypeMapEntriesEx() {
        super();
    }
    
    @Override
    protected void populateTypeMapEntries() {
         //Configure new type
        new JboTypeMap("XMLTYPE"/*COLUMN_TYPE*/, 
            XMLType.class.getName() /*JAVA_CLASS_NAME */,
            "XMLTYPE"/* JDBC_SQL_TYPE  */, 
            XMLType._SQL_TYPECODE/* JDBC_SQL_TYPE_ID */,
            null/*DISPLAY LENGTH*/, 
            OracleTypeMapEntriesEx.BCXMLTYPE/*BASICTYPE*/); 
        
        super.populateTypeMapEntries();
    }    
    
}

You can configure appropriate application module using the Configuration Editor to use this class for  identifying right data types while populating the  attribute values for business components such as entity object and view object. Set the fully qualified name of this class as value for property jbo.TypeMapEntries in the appropriate bc4j.xcfg file.

<AppModuleConfig DeployPlatform="LOCAL" jbo.project="model.demo.DemoModel"  
name="AppModuleAMLocal" ApplicationName="model.demo.AppModuleAM">             
  <Database 
  jbo.TypeMapEntries="framework.bc.extension.OracleTypeMapEntriesEx" 
  jbo.locking.mode="optimistic"/>
  <Security AppModuleJndiName="model.demo.AppModuleAM"/>
  <Custom JDBCDataSource="java:comp/env/jdbc/HRDS"/>
</AppModuleConfig>

Step 3: Build a custom Domain object by wrapping the XMLType.

For building domain class, you can the New Gallery window and choose the Domain item. See the framework.bc.extension.common.XMLTypeDomain class in the sample workspace to learn more about the custom domain class used in this example.

You may need to manually edit the generated domain class to meet the use case requirements. The class, XMLTypeDomain, used in this example abstracts the oracle.xdb.XMLType from the client. It also contains logic for transforming XMLType to String for display in the client side. You may also notice the use of XMLUnit APIs for implementing the equality check. Take a look the XMLTypeDomain file in the attached sample, it's interesting. (See the Download section for the sample)

Step 4: Next step is to tell the design time about the new data type that you introduced.This step is optional and not really required for running this example. I just wanted to tell you about this feature. So, feel free to skip this step if you are fine with editing the XML source of business components in order to add new data type for appropriate attributes.

Edit the <model project>.jpx to include the new type map entry that you defined in Step 2. This will enable the design time to display the new column type in the attribute type drop down that is displayed in the entity object overview editor. See this example:

<DesignTime>
<Attr Name="_jprName" Value="../../../DemoModel.jpr"/>
<Attr Name="_jbo.TypeMapEntries" 
Value="framework.bc.extension.OracleTypeMapEntriesEx"/>

Step 5: Generate business components from the database table. Open the entity object which contains the attribute  corresponding to the XMLTYPE   in the overview editor. Then change the type of the appropriate attribute to the new domain type that you created in Step 3. Go to the source mode of the entity object and change the SQLType and ColumnType manually to point to XMLTYPE as shown here.

<Attribute
    Name="FileContent"
    ColumnName="FILE_CONTENT"
    SQLType="XMLTYPE"    
    Type="framework.bc.extension.common.XMLTypeDomain"
    ColumnType="XMLTYPE"
    TableName="ADF_XMLTYPE_CHECK"  
    Domain="framework.bc.extension.XMLTypeDomain"/>

The above steps are enough to display XMLTYPE  from database in your business component browser.

Step 6: Now, to display the XMLTypeDomain  value on the ADF Faces page, build a custom JSF converter which handles the encoding and decoding of the underlying XML content. See the framework.view.extension.XMLTypeConverter in the attached sample. Configure this in faces-config.xml as shown below:


<converter>
    <converter-id>
    XMLTypeConverter
    </converter-id>
    <converter-class>
framework.view.extension.XMLTypeConverter
   </converter-class>
 </converter>

Step 7: Build the JSF page with appropriate view object and set the converter from Step 6 for the UI component which displays XMLType content.


<af:inputText 
  value="#{bindings.FileContent.inputValue}" 
  label="#{bindings.FileContent.hints.label}"
  required="#{bindings.FileContent.hints.mandatory}"
  columns="#{bindings.FileContent.hints.displayWidth}"
  maximumLength="#{bindings.FileContent.hints.precision}"
  shortDesc="#{bindings.FileContent.hints.tooltip}" id="it3" 
  rows="5"
  converter="XMLTypeConverter">
  <f:validator binding="#{bindings.FileContent.validator}"/>
</af:inputText>

Download

You can download the sample workspace from here.
[ Runs with Oracle JDeveloper  11.1.2.3.0 + HR Schema] 

Before running the sample, do the following
1. Create the following table in your HR schema. This example uses ADF_XMLTYPE_CHECK table to check read and update operations on XMLTYPE with ADF. You can copy the following CREATE statement and run it in your database. The same script is also available in the <XMLTypeSample>/lib folder in the sample workspace.

CREATE TABLE ADF_XMLTYPE_CHECK
  (
    FILE_ID   NUMBER NOT NULL ENABLE,
    FILE_NAME VARCHAR2(1000 BYTE),
    FILE_CONTENT XMLTYPE,
    CONSTRAINT PK_FILE PRIMARY KEY (FILE_ID) 
  )

2. Open the workspace and add xdb6.jar( this jar contains oracle.xdb.XMLType class) and 
xmlunit-1.3.jar (this jar contains XMLUnit class which is used for comparing XML contents in the XMLTypeDomain class) to the class path of the model project. To do this, right click the model project and choose Project Poperties, and then select Libraries and Classpath.  You don't need to go anywhere for grabbing these jars. You can find these jars in the sample workspace that is attached in this post,  You may find them in the <XMLTypeSample>/lib folder.


3. You are almost done ! Run main.jsf and try inserting records with XML content. Enjoy!


Comments

  1. Very informative article. Thanks for explaining the XMLType mapping so well.

    Do you know how to retrieve an XMLType in a custom ApplicationModuleImpl method calling a PL/SQL Function of Procedure?

    And would the use of a custom Type (not XMLType but some CREATE TYPE sometype (as TABLE OF some other type)) be handled in a similar manner?

    kind regards,

    Lucas

    ReplyDelete
  2. Thanks Lucas.
    1.The model.demo.test.TestXMLType class contains basic JDBC APIs for dealing with XMLType(a very unrefined POJO implementation, still helps to get a feel of API).
    2. If that does not involve any custom process during DML(say if this can be handled with setObject() on JDBC PreaparedstStmnt), then I think it should work. Otherwise, not that easy as I'm not finding any easy hook to add custom logic during PreparedStatement generation for INSERT/UPDATE - of course you can override SQLBuilder, but not that easy

    ReplyDelete
  3. Is there any way to use adf faces for example pivot table with source view based on xml which is driven from database procedure web service

    ReplyDelete
  4. Hi I am getting java.lang.ClassCastException: oracle.sql.OPAQUE cannot be cast to java.sql.Struct
    while running am for a view object that has xmltype. I have selected Oracle as type map. Please let me know what might be going wrong

    ReplyDelete
  5. I got it I was using generated DomainType class. replaced the class as per yours and its working now. Thanks for detailed blog

    ReplyDelete
  6. Thanks for a very descriptive article, but I am not able to get it working. Following is database information that I have my table in and the Jdeveloper version:

    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    "CORE 11.2.0.3.0 Production"
    tns for linux: version 11.2.0.3.0 - production
    NLSRTL Version 11.2.0.3.0 - Production

    ADF Business Components 11.1.1.64.93
    BPMN Editor 11.1.1.7.0.0.8
    CVS Version Internal to Oracle JDeveloper 11g Release 1 (client-only)
    Java(TM) Platform 1.6.0_24
    Oracle IDE 11.1.1.7.40.64.93
    soa composite editor 11.1.1.7.0.00.08
    Versioning Support 11.1.1.7.40.64.93

    Any thoughts if any of the above are not compatibale with your solution?

    Kind Regards
    Sam

    ReplyDelete
  7. Just to add what shows on the page, here it comes:

    XmlContent oracle.xdb.XMLType@120bfd4

    ReplyDelete
  8. And indeed, Iím just always astounded concerning the remarkable things served by you. Some four facts on this page are undeniably the most effective Iíve had.
    Database Administration Training | Database Administration course in chennai | Database Administration Training in chennai

    ReplyDelete

Post a Comment