In this post we will use Groovy scripting language with ADF BC in order to fetch and calculate values for exposed (child/parent) view objects attributes.
With respect to ViewLinkAccessor Type attribute we will focus on two cases: oracle.jbo.Row and oracle.jbo.RowIterator.
The accessor type is generated with respect to the Cardinality of the View Link.
So, if we define a view link with cardinality 1 to * the ViewLinkAccessor will have Type=”oracle.jbo.RowIterator“.
In case we define the view link with cardinality * to 1 or 1 to 1 the ViewLinkAccessor will have Type=”oracle.jbo.Row“.
The Type of the ViewLinkAccessor or the Cardinality of the ViewLink must be taken into consideration when Groovy expressions are used in order to fetch the attributes values for the nested or nesting view objects.
1 to * or RowIterator
Create a 1 to * View Link between DepartmentsView and EmployeesView as below (Dept_1ToEmp_n):
At step 3 of the Create View Link Wizzard be sure that Generate Accessor In View Object is checked:
Go to DepartmentView -> Source and you will notice that a new ViewLinkAccessor having the Type = “oracle.jbo.RowIterator“ has been successfully created:
The Name attribute is also important because it will be used with Groovy in order to get the needed attributes from EmployeesView.
Now, based on the newly created relationship we will use Groovy to fetch the first name and last name of the last hired employee for each department:
Open DepartmentsView – > Attributes -> New Attribute… and configure it as below:
Make sure that the Value Type equals Expression.
Next, open your AppModule -> Data Model and add an DepartmentsView instance.
Finally, run Oracle Business Component Browser and check that for each department in DepartmentsView the first name and last name of the employee that was most recently hired is computed.
Hint: Notice that the EmployeesView is treated as a RowIterator; if instead of the above code we would have used:
EmployeesView.getAttribute(“FirstName”)
the following error would have been thrown when running Oracle Business Component Browser:
* to 1 or Row
Create a * to 1 View Link between EmployeesView and DepartmentsView as below (Emp_nToDept_1):
At step 3 of the Create View Link Wizzard be sure that Generate Accessor In View Object is checked:
Go to EmployeesView-> Source and you will notice that a new ViewLinkAccessor having the Type = “oracle.jbo.Row” has been successfully created:
Again, keep in mind the value of the Name attribute.
Now, based on the newly created relationship we will use Groovy to fetch the name of the department for each employee row:
Open EmployeesView - > Attributes -> New Attribute… and configure it as below:
Again, make sure that Value Type = Expression.
Next, open your AppModule -> Data Model and add a EmployeesView instance.
Now, run Oracle Business Component Browser and check that for each employee in EmployeesView the department name is computed.
The sample application – implemented using JDeveloper IDE version 11.1.1.3.0 and built on HR schema – can be downloaded from HERE
For a programatic approach please check the link below:
http://www.adftips.com/2010/12/adf-model-getting-attribute-values-from.html
Useful article on ADF Groovy support:
http://www.oracle.com/technetwork/developer-tools/jdev/introduction-to-groovy-128837.pdf
how can I get the maximum salary in the department from the employee table
I want to get the maximum salary from employee table in certain department
I want to get this in department view
Can i do this.
Hi Mohamed,
Yes, you can definitely do this.
ADF BC provides aggregate functions and one of this functions is max .
Download the sample application and open it in your JDeveloper IDE.
Go to Model Project -> model -> vo -> DepartmentsView -> create a new transient attribute and set its value to:
EmployeesView.max(“Salary”)
The newly created attribute will contain the salary of the most well paid employee of every department.
Regards,
Bogdan.
Thanks Bogdan For reply and many thanks for the great post
very very useful post
I’m using ADF from one year but in cases like this I was using very difficult methods but this really very great post.
Hi Bogdan, very good your blog
I have a query relating to the Business Components
how to assign as the default, for example the result of an SQL query, an attribute of an entity or Vista.
I was looking for information relating to such assignment and could not get anything, in your example you use transient attributes to show these results.
an example of my query would be:
entity: Comprobantesgasto.
attributes: cgid, noejercicio, noentrada,……..
the attribute “Ejercicio”by default is the result of an SQL query (select max (Ej.ejercicio) FROM WHERE Ejercicio Ej.Activo = ‘S ‘), where the table contains information on Ejercicio Ejercicio = years.
One solution could be groovy, and some of its functions # RowSetAttr.max (GroovyExpr)?
or implement a solution in java, in setter methods?
Could be directed to a solution?
Thank you very much.
Hi Luciano,
As you already said the implementation could be done in a lot of ways: SQL, Groovy EL, java…
Groovy could be a solution because is simple and elegant, but in the end is all up to you and what you prefer, which one of the implementation paths is quicker and offers what you need and so on….so it is up to you :)
Regards,
Bogdan.
Another excellent post!
Very much appreciated:)
Lewis
Excelent post!
Thank you very much!!
I have an AM with multiple view instances
MyAM
– ViewInstance1
– ViewInstance2
ViewInstance1 is from ViewObject1
ViewInstance2 is from ViewObject2
ViewObject2 has a viewCriteria (say, VO2_ViewCriteria), which is something like SomeID=:BindId
In the AM’s view instance, can I get hold of ViewInstance1′s attribute and set it to ViewInstance2′s default view criteria using groovy?
In the AM’s view instance, can I get hold of ViewInstance1’s current row’s ID attribute and set it to ViewInstance2’s default view criteria using groovy?