26.4 Basing an Entity Object on a PL/SQL Package API

If you have a PL/SQL package that encapsulates insert, update, and delete access to an underlying table, you can override the default DML processing event for the entity object that represents that table to invoke the procedures in your PL/SQL API instead. Often, such PL/SQL packages are used in combination with a companion database view. Client programs read data from the underlying table using the database view, and "write" data back to the table using the procedures in the PL/SQL package. This section considers the code necessary to create a Product entity object based on such a combination of a view and a package.

Given the PRODUCTS table in the SRDemo schema, consider a database view named PRODUCTS_V, created using the following DDL statement:

create or replace view products_v
as select prod_id,name,image,description from products;

In addition, consider the simple PRODUCTS_API package shown in Example 26-6 that encapsulates insert, update, and delete access to the underlying PRODUCTS table.

Example 26-6 Simple PL/SQL Package API for the PRODUCTS Table

create or replace package products_api is
  procedure insert_product(p_prod_id number,
                           p_name varchar2,
                           p_image varchar2,
                           p_description varchar2);
  procedure update_product(p_prod_id number,
                           p_name varchar2,
                           p_image varchar2,
                           p_description varchar2);
  procedure delete_product(p_prod_id number);
end products_api;

The following sections explain how to create an entity object based on the above combination of view and package.

Note:

The examples in this section refer to the EntityWrappingPLSQLPackage project in the AdvancedEntityExamples workspace. See the note at the beginning of this chapter for download instructions. Run the CreateAll.sql script in the Resources folder against the SRDemo connection to setup the additional database objects required for the project.

26.4.1 How to Create an Entity Object Based on a View

To create an entity object based on a view, use the Create Entity Object wizard and perform the following steps:

In step 1 on the Name panel, give the entity a name like Product and check the Views checkbox at the bottom of the Database Objects section.

This enables the display of the available database views in the current schema in the Schema Object list.
Select the desired database view in the Schema Object list.

In step 3 on the Attribute Settings panel, use the Select Attribute dropdown list to choose the attribute that will act as the primary key, then enable the Primary Key setting for that property.

Note:

When defining the entity based on a view, JDeveloper cannot automatically determine the primary key attribute since database views do not have related constraints in the database data dictionary.

Then click Finish.

26.4.2 What Happens When You Create an Entity Object Based on a View

By default, an entity object based on a view performs all of the following directly against the underlying database view:

SELECT statement (for findByPrimaryKey())
SELECT FOR UPDATE statement (for lock()), and
INSERT, UPDATE, DELETE statements (for doDML())

The following sections first illustrate how to override the doDML() operations, then explain how to extend that when necessary to override the lock()and findByPrimaryKey()handling in a second step.

26.4.3 Centralizing Details for PL/SQL-Based Entities into a Base Class

If you plan to have more than one entity object based on a PL/SQL API, it's a smart idea to abstract the generic details into a base framework extension class. In doing this, you'll be using several of the concepts you learned in Chapter 25, "Advanced Business Components Techniques". Start by creating a PLSQLEntityImpl class that extends the base EntityImpl class that each one of your PL/SQL-based entities can use as their base class. As shown in Example 26-7, you'll override the doDML() method of the base class to invoke a different helper method based on the operation.

Example 26-7 Overriding doDML() to Call Different Procedures Based on the Operation

// In PLSQLEntityImpl.java
protected void doDML(int operation, TransactionEvent e) {
  // super.doDML(operation, e);
  if (operation == DML_INSERT)
    callInsertProcedure(e);
  else if (operation == DML_UPDATE)
    callUpdateProcedure(e);
  else if (operation == DML_DELETE)
    callDeleteProcedure(e);
}

In the PLSQLEntityImpl.java base class, you can write the helper methods so that they perform the default processing like this:

// In PLSQLEntityImpl.java
/* Override in a subclass to perform non-default processing */
protected void callInsertProcedure(TransactionEvent e) {
  super.doDML(DML_INSERT, e);
}
/* Override in a subclass to perform non-default processing */  
protected void callUpdateProcedure(TransactionEvent e) {
  super.doDML(DML_UPDATE, e);
}
/* Override in a subclass to perform non-default processing */  
protected void callDeleteProcedure(TransactionEvent e) {
  super.doDML(DML_DELETE, e);
}

After putting this infrastructure in place, when you base an entity object on the PLSQLEntityImpl class, you can use the Source | Override Methods menu item to override the callInsertProcedure(), callUpdateProcedure(), and callDeleteProcedure() helper methods and perform the appropriate stored procedure calls for that particular entity. To simplify the task of implementing these calls, you could add the callStoredProcedure() helper method you learned about in Chapter 25, "Invoking Stored Procedures and Functions" to the PLSQLEntityImpl class as well. This way, any PL/SQL-based entity objects that extend this class can leverage the helper method.

26.4.4 Implementing the Stored Procedure Calls for DML Operations

To implement the stored procedure calls for DML operations, do the following:

Use the Class Extends button on the Java panel of the Entity Object Editor to set your Product entity object to have the PLSQLEntityImpl class as its base class.
Enable a custom Java class for the Product entity object.
Use the Source | Override Methods menu item and select the callInsertProcedure(), callUpdateProcedure(), and callDeleteProcedure() methods.

Example 26-8 shows the code you would write in these overridden helper methods.

Example 26-8 Leveraging a Helper Method to Invoke Insert, Update, and Delete Procedures

// In ProductImpl.java
protected void callInsertProcedure(TransactionEvent e) {
  callStoredProcedure("products_api.insert_product(?,?,?,?)", 
                      new Object[] { getProdId(), getName(), getImage(), 
                                     getDescription() });
}
protected void callUpdateProcedure(TransactionEvent e) {
  callStoredProcedure("products_api.update_product(?,?,?,?)", 
                      new Object[] { getProdId(), getName(), getImage(), 
                                     getDescription() });
}
protected void callDeleteProcedure(TransactionEvent e) {
  callStoredProcedure("products_api.delete_product(?)",
                      new Object[] { getProdId() });
}

At this point, if you create a default entity-based view object called Products for the Product entity object and add an instance of it to a ProductModule application module you can quickly test inserting, updating, and deleting rows from the Products view object instance in the Business Components Browser.

Often, overriding just the insert, update, and delete operations will be enough. The default behavior that performs the SELECT statement for findByPrimaryKey() and the SELECT FOR UPDATE statement for the lock() against the database view works for most basic kinds of views.

However, if the view is complex and does not support SELECT FOR UPDATE or if you need to perform the findByPrimaryKey() and lock() functionality using additional stored procedures API's, then you can follow the steps in the next section.

26.4.5 Adding Select and Lock Handling

You can also handle the lock and findByPrimaryKey() functionality of an entity object by invoking stored procedures if necessary. Imagine that the PRODUCTS_API package were updated to contain the two additional procedures shown in Example 26-9. Both the lock_product and select_product procedures accept a primary key attribute as an IN parameter and return values for the remaining attributes using OUT parameters.

Example 26-9 Additional Locking and Select Procedures for the PRODUCTS Table

/* Added to PRODUCTS_API package */
  procedure lock_product(p_prod_id number,
                         p_name OUT varchar2,
                         p_image OUT varchar2,
                         p_description OUT varchar2);
  procedure select_product(p_prod_id number,
                           p_name OUT varchar2,
                           p_image OUT varchar2,
                           p_description OUT varchar2);

26.4.5.1 Updating PLSQLEntityImpl Base Class to Handle Lock and Select

You can extend the PLSQLEntityImpl base class to handle the lock() and findByPrimaryKey() overrides using helper methods similar to the ones you added for insert, update, delete. At runtime, both the lock() and findByPrimaryKey() operations end up invoking the lower-level entity object method called doSelect(boolean lock). The lock() operation calls doSelect() with a true value for the parameter, while the findByPrimaryKey() operation calls it passing false instead.

Example 26-10 shows the overridden doSelect() method in PLSQLEntityImpl to delegate as appropriate to two helper methods that subclasses can override as necessary.

Example 26-10 Overriding doSelect() to Call Different Procedures Based on the Lock Parameter

// In PLSQLEntityImpl.java
protected void doSelect(boolean lock) {
  if (lock) {
    callLockProcedureAndCheckForRowInconsistency();
  } else {
    callSelectProcedure();
  }
}

The two helper methods are written to just perform the default functionality in the base PLSQLEntityImpl class:

// In PLSQLEntityImpl.java
/* Override in a subclass to perform non-default processing */  
protected void callLockProcedureAndCheckForRowInconsistency() {
  super.doSelect(true);
}
/* Override in a subclass to perform non-default processing */  
protected void callSelectProcedure() {
  super.doSelect(false);
}

Notice that the helper method that performs locking has the name callLockProcedureAndCheckForRowInconsistency(). This reminds developers that it is their responsibility to perform a check to detect at the time of locking the row whether the newly-selected row values are the same as the ones the entity object in the entity cache believes are the current database values.

To assist subclasses in performing this old-value versus new-value attribute comparison, you can add one final helper method to the PLSQLEntityImpl class like this:

// In PLSQLEntityImpl
protected void compareOldAttrTo(int attrIndex, Object newVal) {
  if ((getPostedAttribute(attrIndex) == null && newVal != null) || 
      (getPostedAttribute(attrIndex) != null && newVal == null) || 
      (getPostedAttribute(attrIndex) != null && newVal != null && 
       !getPostedAttribute(attrIndex).equals(newVal))) {
    throw new RowInconsistentException(getKey());
  }
}

26.4.5.2 Implementing Lock and Select for the Product Entity

With the additional infrastructure in place in the base PLSQLEntityImpl class, you can override the callSelectProcedure() and callLockProcedureAndCheckForRowInconsistency() helper methods in the Product entity object's ProductImpl class. Since the select_product and lock_product procedures have OUT arguments, as you learned in Section 25.5.4, "Calling Other Types of Stored Procedures", you need to use a JDBC CallableStatement object to perform these invocations.

Example 26-11 shows the code required to invoke the select_product procedure. It's performing the following basic steps:

Creating a CallableStatement for the PLSQL block to invoke.
Registering the OUT parameters and types, by one-based bind variable position.
Setting the IN parameter value.
Executing the statement.
Retrieving the possibly updated column values.
Populating the possibly updated attribute values in the row.
Closing the statement.

Example 26-11 Invoking the Stored Procedure to Select a Row by Primary Key

// In ProductImpl.java
protected void callSelectProcedure() {
  String stmt = "begin products_api.select_product(?,?,?,?);end;";
  // 1. Create a CallableStatement for the PLSQL block to invoke
  CallableStatement st = getDBTransaction().createCallableStatement(stmt, 0);
  try {
    // 2. Register the OUT parameters and types
    st.registerOutParameter(2, VARCHAR2);
    st.registerOutParameter(3, VARCHAR2);
    st.registerOutParameter(4, VARCHAR2);
    // 3. Set the IN parameter value
    st.setObject(1,getProdId());
    // 4. Execute the statement
    st.executeUpdate();
    // 5. Retrieve the possibly updated column values
    String possiblyUpdatedName = st.getString(2);
    String possiblyUpdatedImage = st.getString(3);
    String possiblyUpdatedDesc  = st.getString(4);
    // 6. Populate the possibly updated attribute values in the row
    populateAttribute(NAME,possiblyUpdatedName,true,false,false);
    populateAttribute(IMAGE,possiblyUpdatedImage,true,false,false);
    populateAttribute(DESCRIPTION,possiblyUpdatedDesc,true,false,false);
  } catch (SQLException e) {
      throw new JboException(e);
  } finally {
    if (st != null) {
      try {
        // 7. Closing the statement
        st.close();
      } catch (SQLException e) {
      }
    }
  }
}

Example 26-12 shows the code to invoke the lock_product procedure. It's doing basically the same steps as above, with just the following two interesting differences:

After retrieving the possibly updated column values from the OUT parameters, it uses the compareOldAttrTo() helper method inherited from the PLSQLEntityImpl to detect whether or not a RowInconsistentException should be thrown as a result of the row lock attempt.
In the catch (SQLException e) block, it is testing to see whether the database has thrown the error:
```
ORA-00054: resource busy and acquire with NOWAIT specified
```
and if so, it again throws the ADF Business Components AlreadyLockedException just as the default entity object implementation of the lock() functionality would do in this situation.

Example 26-12 Invoking the Stored Procedure to Lock a Row by Primary Key

// In ProductImpl.java
  protected void callLockProcedureAndCheckForRowInconsistency() {
    String stmt = "begin products_api.lock_product(?,?,?,?);end;";
    CallableStatement st = getDBTransaction().createCallableStatement(stmt, 0);
    try {
      st.registerOutParameter(2, VARCHAR2);
      st.registerOutParameter(3, VARCHAR2);
      st.registerOutParameter(4, VARCHAR2);
      st.setObject(1,getProdId());
      st.executeUpdate();
      String possiblyUpdatedName = st.getString(2);
      String possiblyUpdatedImage = st.getString(3);
      String possiblyUpdatedDesc  = st.getString(4);
      compareOldAttrTo(NAME,possiblyUpdatedName);
      compareOldAttrTo(IMAGE,possiblyUpdatedImage);
      compareOldAttrTo(DESCRIPTION,possiblyUpdatedDesc);
    } catch (SQLException e) {
      if (Math.abs(e.getErrorCode()) == 54) {
        throw new AlreadyLockedException(e);
      } else {
        throw new JboException(e);
      }
    } finally {
      if (st != null) {
        try {
          st.close();
        } catch (SQLException e) {
        }
      }
    }
  }

With these methods in place, you have a Product entity object that wraps the PRODUCTS_API package for all of its database operations. Due to the clean separation of the data querying functionality of view objects and the data validation and saving functionality of entity objects, you can now leverage this Product entity object in any way you would use a normal entity object. You can build as many different view objects that use Product as their entity usage as necessary.