ADF: Attribute Defaulting

Problem Description: Recently I came across a forum question where requirement was to make an EO attribute to be fetched value from database but should be ignored completely while saving data. User should definitely be able to update attribute from UI but it should not be posted to database.

https://community.oracle.com/message/14677169#14677169

Solution: Let us see what are our options

In ADF either we can make attribute persistent or transient. If we make persistent, it will be mapped to a database column and its value will be fetched from database and also it will get posted back to database. There is no option to say only fetch from database but ignore while posting to database.

As a solution we can mark our attribute transient so that its value will be ignored while posting to database. Now to default its value from database field we can specify default value. Value can be picked from SQL and good part is we can directly specify column name from which we want to default its value. Of course column must be present in same table.

What if we want to fetch default value from some other table.

You have two option

1. Write a complete SELECT statement on default value field. You can pass values from current row of EO. For example let say you are querying employee table (which has manager id) and now you want to fetch his manager name. You can simply write SQL query as [SELECT MGR.FIRST_NAME FROM EMPLOYEES MGR WHERE MGR.EMPLOYEE_ID = EmployeeEO.MANAGER_ID]

2. You can also use a ViewAccessor with expression to default value. In this case you will use VO and add it as view accessor in EO. Now in default value, you can write groovy expression to get value from view accessor.

Same thing is applied for VO transient attributes as well.

Out of option 1 and 2, I would chose option 1 as preference. Reason for that is if you write SQL based default expression, a single query is fired against database and result is used to set default value of all rows. Query if formed something like

SELECT EmployeeEO.EMPLOYEE_ID, EmployeeEO.FIRST_NAME, EmployeeEO.LAST_NAME, EmployeeEO.EMAIL,

EmployeeEO.PHONE_NUMBER, EmployeeEO.HIRE_DATE, EmployeeEO.JOB_ID, EmployeeEO.SALARY,

EmployeeEO.COMMISSION_PCT, EmployeeEO.MANAGER_ID, EmployeeEO.DEPARTMENT_ID,

(SELECT MGR.FIRST_NAME FROM EMPLOYEES MGR WHERE MGR.EMPLOYEE_ID = EmployeeEO.MANAGER_ID) AS MGR_NAME

FROM EMPLOYEES EmployeeEO

In option 2, View accessor will be fired once for every row fetched. For example if we fetch 100 employee records and for each record a manager-vo query will fire to get manager name. You will end up with 101 queries. To improve View accessor queries, you can set RowLevelBinds = false if same view-accessor query result is ok for all records. In above case it will not fit. You need to fire separate view-accessor query with bind parameter as employee-id from source row to get correct manager name.

Thats all