ADF 11g Business components – Create PL/SQL based entities

While building a (new) ADF 11g application you sometimes have to deal with the fact that you have to use existing PL/SQL procedures for storing your data.

This article describes how you can overwrite your ADF business component’s default behavior to execute its DML statements using PL/SQL stored procedures.

 

There are a few steps we have to make:

• Create a new Fusion Web Application and connect to the HR database
• Create Entity and View for the REGIONS table (or based on a view if you like)
• Create abstract class (PlSqlEntity.java) that will be the baseclass of all entities that use PL/SQL for its DML. This class will also contain a helper method for executing stored procedures
• Make the REGIONS Entity extend our PlSqlEntity and implement the abstract methods

Preparations

Normally you would use existing PL/SQL Packages, sequences and triggers, but for our demo we have to make these ourselves.

 

Create the sequence, the trigger and the package:

CREATE SEQUENCE "HR"."REGIONS_SEQ"
MINVALUE 1
MAXVALUE 999999999999999999999999999
INCREMENT BY 1
START WITH 1000
CACHE 20
NOORDER
NOCYCLE ;


CREATE OR REPLACE TRIGGER REGIONS_TRG
BEFORE INSERT ON REGIONS
FOR EACH ROW
BEGIN
  select REGIONS_SEQ.nextval into :new.region_id from dual;

END;

CREATE OR REPLACE
PACKAGE HR AS
  /** Insert new REGION **/
  PROCEDURE INSERT_REGION (P_ID IN REGIONS.REGION_ID%type, P_NAME IN REGIONS.REGION_NAME%type);
  /** Update a REGION **/
  PROCEDURE UPDATE_REGION (P_ID IN REGIONS.REGION_ID%type, P_NAME IN REGIONS.REGION_NAME%type);
  /** Remove a REGION **/
  PROCEDURE DELETE_REGION (P_ID IN REGIONS.REGION_ID%type);
END HR;
/* Package body: */
CREATE OR REPLACE
PACKAGE BODY HR AS
  PROCEDURE INSERT_REGION (P_ID IN REGIONS.REGION_ID%type, P_NAME IN REGIONS.REGION_NAME%type) AS
  BEGIN
    INSERT INTO REGIONS (REGION_ID, REGION_NAME)
      values
    (P_ID, P_NAME);
  END INSERT_REGION;

  PROCEDURE UPDATE_REGION (P_ID IN REGIONS.REGION_ID%type, P_NAME IN REGIONS.REGION_NAME%type) AS
  BEGIN
    UPDATE REGIONS
    SET    REGION_NAME = P_NAME
    WHERE  REGION_ID   = P_ID;
  END UPDATE_REGION;

  PROCEDURE DELETE_REGION (P_ID IN REGIONS.REGION_ID%type) AS
  BEGIN
    DELETE FROM REGIONS
    WHERE  REGION_ID = P_ID;
  END DELETE_REGION;<

END HR;

Your modifications on the database are done. As you can see, the procedures contain only DML. In real life, these procedures also contain your business logic and may perform much more complex DML, perhaps against multiple tables in a very legacy data model.

 

Create business components

 

Create your business components based on the REGIONS table:

 

Press Query, select REGIONS table and press Next:

 

Updatable view objects, select RegionsView, alter the package name and press Next:

 

Skip the Query-based view objects (click next), add viewobject to Application module:

 

Click Finish

The Entity we just created for the REGIONS table is just a normal Entity with default behavior.

 

Now we create a class that we can use as a baseclass for Entities based on PL/SQL, let’s call it PlSqlEntity. Make the class abstract and put it in a logically named package.

 

The class needs abstract methods for each specific DML action, these methods will be implemented by its children:

 

  protected abstract void callInsertProcedure( TransactionEvent transactionEvent);

  protected abstract void callUpdateProcedure( TransactionEvent transactionEvent);

  protected abstract void callDeleteProcedure( TransactionEvent transactionEvent);

 

We also need to override the doDML method. Richt-click in the source-editor on the newly created PlSqlEntity and select “Override Methods”:

 

Select doDML(int, TransactionEvent) method.

 

The implementation of the doDML method should look like this:

 

    protected void doDML(int i, TransactionEvent transactionEvent) {
        if (i == DML_INSERT){
          callInsertProcedure(transactionEvent);
        } else if (i == DML_UPDATE) {
          callUpdateProcedure(transactionEvent);
        } else if (i == DML_DELETE){
          callDeleteProcedure(transactionEvent);
        }
        //super.doDML(i, transactionEvent); //Do not implement because PL/SQL is doing the job for us
    }

 

Note: Don’t call super.doDML anymore, try this for fun and see what happens when your Entity is based on a table!!!

 

The last thing we need in our PlSqlEntity class is some code to make a call to stored procedures. The Oracle documentation (http://docs.oracle.com/cd/E15523_01/web.1111/b31974/bcadvgen.htm) provides a Helper Method for this:

 

protected void callStoredProcedure(String stmt, Object[] bindVars) {
  PreparedStatement st = null;
  try {
    // 1. Create a JDBC PreparedStatement for
    st = getDBTransaction().createPreparedStatement(“begin “+stmt+”;end;”,0);
    if (bindVars != null) {
      // 2. Loop over values for the bind variables passed in, if any
      for (int z = 0; z < bindVars.length; z++) {
        // 3. Set the value of each bind variable in the statement
        st.setObject(z + 1, bindVars[z]);
      }
    }
    // 4. Execute the statement
    st.executeUpdate();
  }
  catch (SQLException e) {
    throw new JboException(e);
  }
  finally {
    if (st != null) {
      try {
        // 5. Close the statement
        st.close();
      }
      catch (SQLException e) {}
    }
  }
}

 

Our PL/SQL entity baseclass is ready, we can use it in our implementation classes.

Open the Regions entity and in the Java tab click Pencil to edit. You should include the accessors and override row to use our new PlSqlEntity class:

 

If you open the REGIONS Entity in the code editor, you will get this warning:

Click on the red balloon and select “Implement methods”

 

These methods are empty, so we have to make an implementation:

    protected void callInsertProcedure(TransactionEvent transactionEvent) {
      callStoredProcedure(“HR.INSERT_REGION(?,?)”, new Object[]{getRegionId(), getRegionName()});
    }

    protected void callUpdateProcedure(TransactionEvent transactionEvent) {
        callStoredProcedure(“HR.UPDATE_REGION(?,?)”, new Object[]{getRegionId(), getRegionName()});
    }

    protected void callDeleteProcedure(TransactionEvent transactionEvent) {
      callStoredProcedure(“HR.DELETE_REGION(?)”, new Object[]{getRegionId()});
    }

 

We’re almost done now, only one thing to do…

Because we are using a trigger/sequence combination to assign the value for Region Id in the database, REGION_ID is not mandatory at Entity level. Open your REGIONS Entity, select tab Attributes and double-click the RegionId attribute.

Uncheck Mandatory checkbox and press OK:

 

You can now test your application module using the Oracle Business Component Browser.

Click + to add a new record:

 

Enter a region name and click save:

 

The newly created RegionId will be visible after refreshing the tester: