Thursday, December 31, 2009

Examining View Object Attributes in Oracle ADF 11g

In Oracle ADF 11g, you use entity objects to represent tables or UML entities and to implement business rules. And you use view objects to retrieve data from a data source and to expose and shape the data for clients. Using an Entity Fascade design pattern, view objects provides a restricted view of data and behavior of one or more entity objects.
In this article, we'll take a look at different kinds of view object attributes you can create in Oracle ADF.

Different View Object Attributes

View objects provide the means to retrieve data from a data source. In the majority of cases, the data source will be a database and the mechanism to retrieve data is the SQL query. At runtime each view object manages its own query result set. If you connect two or more view objects in master-detail relationships, that coordination is handled automatically. While defining a view object, you can link any of its query columns to underlying entity objects.
The easiest way to create view objects is to launch the Create Business Components from Tables Wizard, which allows you to create multiple entity objects, multiple view objects and other Business Components from database tables.

However, sometimes you need to add new columns to your tables or UML entities or you need to create calculated columns, then you must follow the steps described in this article to create new view object attributes incrementally. But, our focus here is to examine different kinds of view object attributes you can set up in Oracle ADF.

The classification of a view object attribute is mainly based on whether you would map it to a column/SQL or not.

If you map it to a column or SQL, you need to define a query column which either link it to an underlying entity attribute or provide it a SQL expression. View object attributes that are mapped to columns or SQL expressions will be included in the SQL SELECT list.
If your view object attribute is not mapped to a column or SQL, it's a transient attribute. A transient attribute can have a default value which could be either a literal or a Groovy expression.

How to Create a New View Object Attribute

  1. In the Application Navigator, double-click the view object for which you want to add a new attribute.
  2. In the overview editor, click the Attributes navigation tab and click the Create new attribute button (i.e., "+"). Note that Create new attribute button provides a dropdown list with two options:
    • New Attribute...
    • Add New Attribute from Entity...


Entity-Based Attribute

The simplest way to create a new view object attribute is to link it to an entity attribute (i.e., selecting "Add New Attribute from Entity..."). After creating it, its definition will look like this:

As you can see it, "Mapped Column or SQL" is checked and an alias (i.e., "ENAME") is provided in "Query Column." Also notice that it includes an Entity Attribute navigation tab. In this case, view object attribute properties are inherited from underlying entity object.

Transient Attribute

If you choose "New Attribute... " option, it will bring up New View Object Attribute wizard. To define a transient attribute, you can provide its default value using either a literal or a Groovy expression. In the following example, we set ValueUsingVORowFunction attribute's default value to be a Groovy expression:
  • adf.object.myFunctionAtVORowLevel(Sal)

Because a transient attribute is not mapped to a column or SQL, you need to uncheck "Mapped to Column or SQL" (notice that Query Column is grayed out after unchecking). adf.obj can be used to reference the object on which the expression is being applied. Depending on the context in which the Groovy script is applied, adf.obj could point to different objects. The context here is an instance of the view row implementation class (i.e., EmpViewRowImpl.java). Through this object, you can reference custom methods of the custom view row implementation class.

If you want to reference custom methods of the custom view implementation class (i.e., EmpViewImpl.java) from the Groovy script, you specify this instead:

  • adf.object.viewObject.myFunctionAtVOLevel(Sal)
To learn more on Groovy expression, see references in the More Readings section. For the sample implementation of transient attributes, you can download it from Steve Muench's blog here.

SQL-Calculated Attribute

To define SQL based calculated attribute, you also bring up New View Object Attribute wizard by choosing "New Attribute..." In the following example, it shows a SQL-calculated attribute which computes total number of tests in a detail table that is linked to the mater table that this attribute is defined in:
SQL-calculated attribute is mapped to SQL. Therefore, you need to check "Mapped to Column or SQL." Also, you need to provide a SQL expression and its type:
  • select count(*) from xtm_sel_tests xst where xst.test_run_id = TestRunEO.test_run_id

Note that TotalTests is defined in TestRunVO (i.e., a View Object) and TestVO (detail object) is linked to TestRunVO (master object) via a view link. Here detail table is named xtm_sel_tests and referenced in the expression. A Where clause which filters the result set with entries matching the test_run_id value of master table row is specified.
The query column you specified corresponds to an inner query which is embedded in the outer query. The final query generated by Oracle ADF looks like this:
SELECT TestRunEO.TEST_RUN_ID,
        ProductEO.PRODUCT_NAME,
        ProductEO.PRODUCT_ID,
        TestRunEO.START_TIME,
        TestRunEO.END_TIME,
        TestRunEO.TEST_URL,
   (select count(*) from xtm_sel_tests xst  where
    xst.test_run_id = TestRunEO.test_run_id) AS TOTAL_TESTS
FROM XTM_SEL_TEST_RUNS TestRunEO, XTM_SEL_PRODUCTS ProductEO, XTM_SEL_TESTS TestEO
WHERE (TestRunEO.PRODUCT_ID = ProductEO.PRODUCT_ID) AND (TestRunEO.TEST_RUN_ID = TestEO.TEST_RUN_ID)
Note that, in this example, there is also a join between XTM_SEL_RPODUCTS and XTM_SEL_TEST_RUNS tables. If you specify something wrong (for example, changing TestRunEO.test to TestRunVO.test), you can find out the error using Oracle Business Component Browser:

More Readings

  1. Groovy
  2. Overview of Groovy Support
  3. Groovy Scripting Tips for ADF Business Components
  4. Introduction to Groovy Support in JDeveloper and Oracle ADF 11g
  5. Adding Calculated and Transient Attributes to a View Object
  6. Oracle/PLSQL: Subqueries

Thursday, December 17, 2009

Three SQL Modes in Oracle ADF

There are three SQL modes in Oracle ADF:
  1. Declarative SQL mode
  2. Normal SQL mode
  3. Expert SQL mode
where declarative SQL mode is a new addition in Oracle ADF 11g. The declarative SQL mode for view objects lets you create SQL-Independent view objects . You can define query filter predicates by using view object attribute names without having to type in SQL yourself. At runtime, Oracle ADF translates the logical filter expressions into the appropriate SQL automatically.

Declarative SQL mode selection is supported in JDeveloper as a setting that you can apply either to the entire data model project or to individual view objects that you create. After you enable declarative SQL mode, the basic procedure to create a view object is the same as you would follow to create any entity-based view object.

When you create a new view object, keep the default setting Updatable access through entity objects enabled to indicate that you want this view object to manage data with its base entity object. Any other choice for the data selection will disable declarative SQL mode in the Create View Object wizard.

The alternatives to declarative SQL mode are normal mode and expert mode. When you work in either of those modes, the view object definitions you create at design time always contain the entire SQL statement based on the SQL flavor required by your application module's defined database connection. Thus the capability of SQL independence does not apply to view objects that you create in normal or expert mode. For view objects created in normal or expert mode, you must edit the WHERE clause to filter the data as required.
In declarative SQL mode, the view object's metadata causes the ADF Business Components runtime to generate the SQL query statements as follows:
  • Generates SELECT and FROM lists based on the rendered web page's databound UI components' usage of one or more entity objects' attributes
  • Optionally, generates a WHERE clause based on a view criteria that you add to the view object definition
  • Optionally, generates an ORDERBY clause based on a sort criteria that you add to the view object definition.
  • Optionally, augments the WHERE clause to support table joins based on named view criteria that you add to the view object definition
  • Optionally, augments the WHERE clause to support master-detail view filtering based on a view criteria that you add to either the source or destination of a view link definition

Enable Declarative SQL Mode Globally

You can enable declarative SQL mode as a global preference so that it is the Create View Object wizard's default mode. If you did not select Enable declarative SQL mode for new objects, in the Preferences dialog, the wizard displays the default query mode, Normal.
Typically, when you define a declarative SQL mode view object, the attributes that get queried at runtime will be determined by the requirements of the databound UI component as it is rendered in the web page. This is the runtime-generation capability that makes view objects independent of the design time database's SQL flavor. However, you may also need to execute the view object programmatically without exposing it to an ADF data binding in the UI. In this case, you can enable the Include all attributes in runtime-generated query option to ensure that a programmatically executed view object has access to all of the entity attributes.

To enable both options (i..e, "Enable declarative SQL mode for new objects" and "Include all attributes in runtime-generated query") for the entire data model project. Go to:

  • Tools > Preferences > Business Components > View Objects

Enable Declarative SQL Mode per View Object

To enable Declarative SQL Mode per view object, select the View Object you want to edit. Choose Query tab and click on edit icon (i.e., pencil). On Edit Query dialog, you can choose Declarative SQL Mode (vs. Normal or Expert Mode).

Comparisons of Three SQL Modes

  • Normal Mode
    • You can manually enter custom WHERE and ORDER BY clauses in a view object query. However, by default, the FROM clause and SELECT list are automatically derived. When you require full control over the SELECT or FROM clause in a query, you can enable expert mode.
    • The automatic cooperation of a view object with its underlying entity objects depends on correct attribute-mapping metadata saved in the XML component definition. This information relates the view object attributes to corresponding attributes from participating entity usages. JDeveloper maintains this attribute mapping information in a fully automatic way for normal entity-based view objects.
  • Expert Mode
    • In Expert Mode, you are given full control over the entire SQL statement. However, it provides limited Attribute Mapping Assistance.
    • When you enable expert mode, the read-only Generated Statement section of the Query page becomes a fully editable Query Statement text box, displaying the full SQL statement. Using this text box, you can change every aspect of the SQL query.
  • Declarative SQL Mode
    • The view object in declarative SQL mode generates all SQL clauses entirely at runtime. The runtime-generated SQL statements will be determined by the SQL flavor specified in the Business Components page of the Project Properties dialog. Currently, the runtime supports SQL92 (ANSI) style and Oracle style flavors.
    • In declarative SQL mode, since the wizard and editor do not allow you to enter WHERE and ORDERBY clauses, you provide equivalent functionality by defining a view criteria and sort criteria respectively.
      • In declarative SQL mode, view criteria and sort criteria appear in the view object metadata definition and will be converted at runtime to their corresponding SQL clause.
      • In this mode, Query Clauses section changes to show declarative controls for the Where filter expression and the Order By attribute list.
    • Just as with normal mode view objects, you can link view objects that you create in declarative SQL mode to other view objects to form master-detail hierarchies of any complexity. However, in the case of view objects that you create in declarative SQL mode, you can further refine the view object results in the Source SQL or Destination SQL dialog for the view link by selecting a previously defined view criteria in the Create View Link wizard or the overview editor for the view link.

More Readings