17 Apr 2011

Working with Oracle SQL object types

Introduction
Sometimes, it could be convenient to represent real business entities using Oracle object types. For example, address, contact information or some payment instructions - all theses entities have standard set of attributes and, may be, have some internal business logic. It would be handy to define an object type, encapsulating all necessary attributes, and work with it in the same way if it were usual scalar type. In this post I'll show how to work with a simple Oracle object type at the business model level and how to create a declarative component working with this model at the view level.

Use Case
I need to store in my database information about payments. It should contain payment's date, currency, amount and contact information of the person connected with the payment. I'm going to use object type for contact info with the following structure:

create or replace type Tcontact_info as object
(Name Varchar2(100),
 PhoneNumber Varchar2(100),
 CellPhoneNumber Varchar2(100),
 email Varchar2(100)
 )

And the table for payments looks like this:

create table Payment
(ID Number,
 PaymentDate date,
 Currency Varchar2(3),
 Amount Number,
 Contact Tcontact_info
 )


Let's do it

In JDeveloper in the model project I created entity for Payment table using "Create Business Components from Tables" wizard.


The wizard has created entity object with the following structure:


Have a look at the Contact's type. And the wizard has created business domain object TcontactInfo representing Tcontact_info SQL object type:


After that I created entity based view object VPayment and Application Module containing an instance of this view object. If we now explore the Data Controls panel, we will see very interesting picture:



Contact attribute is defined like a child iterator with its own set of attributes and operations. And if we drag-n-drop VPayment from the Data Controls panel to the page, we will see interesting structure in the generated PageDef file:

  <executables>
    <variableIterator id="variables"/>
    <iterator Binds="VPayment" RangeSize="25" DataControl="AppModuleDataControl"
              id="VPaymentIterator"/>
    <accessorIterator MasterBinding="VPaymentIterator" Binds="Contact"
                      RangeSize="25" DataControl="AppModuleDataControl"
                      BeanClass="com.cs.blog.sqlobjectexample.model.common.TcontactInfo"
                      ObjectType="true" id="ContactIterator"/>
  </executables>
  <bindings>
    <!--General payment's attributes: -->   
    <attributeValues IterBinding="VPaymentIterator" id="Amount">
      <AttrNames>
        <Item Value="Amount"/>
      </AttrNames>
    </attributeValues>
    ... 

    <!--Contact's attributes: -->    
    <attributeValues IterBinding="ContactIterator" id="Name">
      <AttrNames>
        <Item Value="Name"/>
      </AttrNames>
    </attributeValues>
    ... 
  </bindings>
  

Access to Contact's attributes is provided via special "accessIterator"! And it works fine as we can see here:

At this point we could say "We are done!", but we are not looking for easy ways! I'm going to create declarative component for contact info in order to  use it widely in my applications.

In my previous post you can see how to create, deploy and use declarative components in details. In "Create JSF Declarative Component Wizard" we need to add attribute "value" with type of business domain TcontactInfo:

          

In jspx file for our declarative component ContactInfo I have added the following content:

<af:panelBox text="Contact Info" id="dc_pb1" showDisclosure="false"
              inlineStyle="width:350.0px;">
  <af:panelFormLayout id="dc_pfl1">
    <af:inputText value="#{attrs.value.name}" label="Person's name"
                  id="dc_it1"></af:inputText>
    <af:inputText value="#{attrs.value.phonenumber}" label="Phone number"
                  id="it6"></af:inputText>
    <af:inputText value="#{attrs.value.cellphonenumber}"
                  label="Cell phone number" id="it5"></af:inputText>
    <af:inputText value="#{attrs.value.email}" label="Email" id="dc_it2"></af:inputText>
    <f:facet name="footer"/>
  </af:panelFormLayout>
 </af:panelBox>

Note, that access to the Contact's attributes is provided using keyword "attrs.value", which is referring to an object of TcontactInfo class.

In my target jspx page I can use new declarative component like this:

<compLib:ContactInfo id="ci1" value="#{bindings.Contact.inputValue}"/> 

And my PageDef looks like this:

  <executables>
    <variableIterator id="variables"/>
    <iterator Binds="VPayment" RangeSize="25" DataControl="AppModuleDataControl"
              id="VPaymentIterator"/>
  </executables>
  <bindings>
    <!--General payment's attributes: --> 
    <attributeValues IterBinding="VPaymentIterator" id="Amount">
      <AttrNames>
        <Item Value="Amount"/>
      </AttrNames>
    </attributeValues>
    ...
    <attributeValues IterBinding="VPaymentIterator" id="Contact">
      <AttrNames>
        <Item Value="Contact"/>
      </AttrNames>
    </attributeValues>
 </bindings>


So, I don't need any "accessorIterator" and define "Contact" as usual scalar attribute. The following screen is presenting the result of our work. Sorry for design:

Are we done? Almost. We can get and show information from the database SQL object type. And we need to be able to put this information back, to save it to the database. Next time.


2 comments:

  1. Excellent post.

    Can you help me with this.

    Typical master details examples are
    It shows the dept in one region and shows the corresponding employees in another.

    To relate my need with that example,
    My need is to show BOTH dept and employees in both the regions.
    i.e. Dept id, Dept Name , Employee name, Employee id..

    We should be able to display in the top region all these fields from both tables
    and the corresponding details show up in the
    bottom region where you can insert/edit/delete BOTH the tables.

    Thanks

    ReplyDelete
  2. Hi!
    Thank You for your feedback.

    For your case you should set some properties for the Departments (header1) entity on the VO's definition page:

    Updatable = true
    Reference=false
    Participate in row delete = true

    ReplyDelete

Post Comment