Software Engineering

Object Types in ADF 12c Using View And Entity Objects (Part 1 of 2)

This article will show you how to use Oracle PL/SQL object types in ADF 12c with view and entity objects. You will get the full support of the framework and can handle the entity and view objects like any other view or entity object! In this part you will learn how to use entity objects and view objects in combination with object types and stored procedure.

Requirement

Step by Step

Set Up the Test Project

  • either use the project from this [Link zum vorherigen Artikel] article or
  • create a new project
    • create a new application
      • ADF Fusion Web Application
      • connection to the database with HR schema and user
      • an open SQL worksheet with user SYS as SYSDBA
    • create the object type (execute the following script in the SQL worksheet)

    • create the domain for the object type
      • right click on Module -> New -> From Gallery… -> Business Tier -> ADF Business Components -> Domain
      • choose your database connection and click ok
      • check Domain for an Oracle Object Type and select  EMPLOYEE_WITH_JOB_TITLE  from Available Types and click next
      • select the attribute EmployeeId and change Type to BigDecimal
    • create the object table that consists of the object type

Create the Entity Object

  • right click on the model project -> New -> Entity Object
  • deselect Existing Object at the bottom and change the name to EmployeeWithJobTitleEO
  • press Next and a dialog pops up with a suggestion for the Schema Object name
  • press Yes to accept the suggestion
    create a new entity object accept the suggestion
  • add attributes to the entity object
    • respect the order of the attributes!
      NameTypeMisc
      EmployeeIdBigDecimalPersistent, Queryable, Primary Key, Mandatory
      FirstNameStringPersistent, Queryable
      LastNameStringPersistent, Queryable
      JobTitle
      StringPersistent, Queryable

      adapt employee id overview of the attributes of the entity object

  • you can skip  Attribute Settings  (or check if everything looks good) and go to Java, select Generate Entity Object Class and click Finish generate entity object class

Create the View Object

  • right click on the model project -> New -> View Object
  • use as the name EmployeeWithJobTitleVO and select Entity as Data Source and click Next
  • shuffle the new created entity object to the right and click Next
    create a new view object select entity object
  • shuffle all the attributes to the right, make sure they are in the right order (they probably will be not) and click Next
  • the Attribute Settings should be fine, go directly to Query
  • deselect Calculate Optimized Query at Runtime, instead select Write Custom SQL, clear the Select statement and click Next
    view object attributes query settings of the view object
  • the Attribute Mapping and Bind Variables should be fine so go directly to Java
  • select Generate View Object Class, deselect the rest if any and click Next
  • select Add to Application Module and click Next
    java settings of the view object application module settings
  • check the summary and click Finish
  • your project tree should look like this now:
    structure after EO and VO creation

Create Stored Procedures for Querying

  • create the following stored procedure

    • this procedure uses the object type HR.EMPLOYEE_WITH_JOB_TITLE as an input parameter and the object table HR.EMPLOYEE_WITH_JOB_TITLE_TABLE as an output parameter
    • the object type is used to pass the search parameters
    • note that the all the criteria of the WHERE statement are using LIKE where possible (it’s not possible for NUMBERs)
      • workaround for NUMBERs:  if pin_employee.EMPLOYEE_ID is null, no criteria will be used for the EMPLOYEE_ID otherwise the EMPLOYEE_ID
    • have a look at the last criteria of the WHERE statement
      • a nested query is used to get all the JOB_IDs
    • the rest of this procedure should be clear
  • create another stored procedure for counting all employees (I’ll explain later the use for it)

  • you should see both of your stored procedure in the database
    stored procedures

Adapt the View Object to Use Stored Procedures

  • first of all you need a basic understanding how the view object works and how it relates to the entity object
    • from the documentation:
      • The view object is the data source: it retrieves the data using SQL.
      • The entity object is the data sink: it handles validating and saving data changes.
    • here are some of the key methods; the descriptions are copied from the documentation
      • executeQueryForCollection()
        • This method is called whenever the view object’s query needs to be executed (or reexecuted).
      • hasNextForCollection()
        • This method is called to support the hasNext() method on the row set iterator for a row set created from this view object. Your implementation returns true if you have not yet exhausted the rows to retrieve from your programmatic data source.
      • createRowFromResultSet()
        • This method is called to populate each row of „fetched“ data. Your implementation will call createNewRowForCollection() to create a new blank row and then populateAttributeForRow() to populate each attribute of data for the row.
      • getQueryHitCount()
        • This method is called to support the getEstimatedRowCount() method. Your implementation returns a count, or estimated count, of the number of rows that will be retrieved by the programmatic view object’s query.
  • now, get your hands dirty
  • according to the documentation – you have to override the create() method of your view object class to wipe out all traces of a query

  • next, you need to override the executeQueryForCollection(…) method
    • the documentation gives an example, so we follow the trail including the helper methods storeResultSet and retrieveResultSet

      • try to understand these three methods (before I explain the generateEmployeeFromViewCriteria() method)
        • basically, the storeResultSet(…) method is calling the setUserDataForCollection(…) — which will store the data retrieved from retrieveResultSet(…) — and making the initial call on the hasNextForCollection(…) method
        • the retrieveResultSet(…) method is the actual database access method
          • it will call the stored procedure and return the result set
    • now look at the generateEmployeeFromViewCriteria() method and it’s helper method

      • if there is no view criteria on this view object — which means, there is no specific query — all employees will be retrieved by setting all the String attributes to the wildcard % and leaving the ID attribute null
      • if there are view criteria the first one will be used for the query by filling in the values of the attributes — yes, it’s not perfect to use only the first view criteria, but you can change this in your implementation

OK, probably this is much to take. But if you got this, you got most of the interesting parts.

Adapt the View Object to Cooperate With the Entity Object

  • you only need to adapt two methods which cooperate together — handled by the framework
  • the first method is the hasNextForCollection(…) method which you already called in your storeResultSet(…) method
    • this method is called before the createRowFromResultSet(…) method which creates a new row for the view object and populates it
    • so the framework checks with hasNextForCollection(…) if there are still unprocessed query results and then calls createRowFromResultSet(…)
    • only for the first call of createRowFromResultSet(…) the hasNextForCollection(…) is skipped — that is why you have to call it in storeResultSet(…) yourself
  • have a look at the overridden method

    • this is also taken directly from the documentation
    • if there are no more unprocessed query results the setFetchCompleteForCollection(…) is called to tell the view object that this collection is done
  • the other method is the createRowFromResultSet(…) so we can tell how to actually process our retrieved data

    • this method first gets the result set
    • it creates a basically empty ViewRow by calling the createNewRowForCollection(…) method
    • it fills this row by calling the populateAttributeForRow(…) method
    • in the end it returns the newly created row

Last Adaptions to the View Object

  • only two more changes are needed and then you can enjoy the fruit of your hard work
  • override the releaseUserDataForCollection(…)

    • taken from the documentation: „Once the collection is done with its fetch-processing, the overridden releaseUserDataForCollection() method gets invoked and closes the ResultSet cleanly so no database cursors are left open.“
    • this method is copied, too, from the documentation
    • it just closes the result set
  • then, finally, the last method to override

    • taken from the documentation: „Lastly, in order to properly support the view object’s getEstimatedRowCount() method, the overridden getQueryHitCount() method returns a count of the rows that would be retrieved if all rows were fetched from the row set.“
    • here you need to call the stored procedure that you created before — and now you can see, why you need it in the first place

Run the Application Module

run the application module results

Go For a Walk: now!

Sitting in front of the computer too long is not healthy. Not even with the best ergonomic chair you can get.

Follow this blog and read the second part of this article (out soon)! It will explain how to adjust the entity object to make it really ADF conform.