2 May 2011

Working with Oracle SQL object types. Part II

In my previous post I demonstrated how to retrieve and show information from a database table containing an attribute with SQL object type. In this post I'm going to show how to save this information back into the database.
So, I have a table with the following structure:

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

And Tcontact_info SQL object type has the following definition:

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

In order to update data in  Payment table I'm going to use PL/SQL procedure Payment_update:

create or replace procedure Payment_update
(aId Payment.Id%type,
 aPaymentdate Payment.Paymentdate%type,
 aCurrency Payment.Currency%type,
 aAmount Payment.Amount%type,
 aContact Payment.Contact%type
 )
is
begin
  update Payment
    set Paymentdate = aPaymentdate,
        Currency = aCurrency,
        Amount = aAmount,
        Contact = aContact
  where id = aID;      
end;   
 
In my entity object implementation class PaymentImpl I have overridden doDML method:

    protected void doDML(int operation, TransactionEvent e) {
        switch (operation) {
        case DML_UPDATE:
            {
                updateProcedure();
                break;
            }
        case DML_INSERT:
            {
                insertProcedure();
                break;
            }
        case DML_DELETE:
            {
                deleteProcedure();
                break;
            }
        }
    }

Methods insertProcedure(), updateProcedure() and deleteProcedure() are used to call appropriate PL/SQL procedures for corresponding DML modes.
For example, updateProcedure() looks like this:

    private void updateProcedure() {
        DBTransactionImpl dbti = (DBTransactionImpl) getDBTransaction();
        CallableStatement statement =
          dbti.createCallableStatement(("BEGIN " + 
            "Payment_update(:aId, :aPaymentdate, :aCurrency, :aAmount, :aContact);" + 
                                        "END;"), 0);
        try
        {
          statement.setObject("aId",getId());
          statement.setDate("aPaymentdate", getPaymentdate().dateValue());
          statement.setString("aCurrency", getCurrency());  
          statement.setObject("aAmount", getAmount());
          statement.setObject("aContact", getContact());  
          statement.execute();
        }
        catch (SQLException sqlerr)
        {
          throw new JboException(sqlerr);
        }
        finally
        {
          try
          {
            if (statement != null)
            {
              statement.close();
            }
          }
          catch (SQLException closeerr)
          {
            throw new JboException(closeerr);
          }
        }
        
    }
Note, that there is no any magic to pass contact information as a parameter to Callable Statement. PaymentImpl class has usual getter getContact() for its Contact attribute with TcontactInfo class. And TcontactInfo class inherits oracle.jbo.domain.Struct class which is commonly used to work with complex SQL types. That's all.

No comments:

Post a Comment

Post Comment