How to create a linking analysis overview

Technology: ADF11g
Developed in: JDeveloper 11.1.1.3.0
Browsers tested: Firefox 6.0.1 and Internet explorer 8 (8.0.6001.18702 64 bit edition)

Summary

I received a question is it possible to create a representation like shown below in ADF? In this blog a description how to solve this.

Database

In a database a table tst_cases is created:

Column name	Datatype	Mandatory	Constraint(s)
ID	Number(10,0)	Yes	Primary key
Name	Varchar2(100)	Yes	Unique key
PARENT_CSE_ID	Number(10,0)	No	Foreign key to ID

It's filled with content:

ID	NAME	PARENT_CSE_ID
1	Case 1
10	Case 10	1
20	Case 20	1
30	Case 30	1
101	Case 101	10
102	Case 102	10
201	Case 201	20
202	Case 202	20
203	Case 203	20

Model layer

Create with the Business Components for Tables an entity for the table (name Case), a view object for the entity (name CasesView) and an application module (name TestAppModule). An association and view link will be created as well.

Create another view object with name RootCasesView that uses the Case enity object but with where clause PARENT_CSE_ID IS NULL. Create a view link from the RootCasesView to the CasesViwe (using the Case_ParentCase_FK association):

View controller

1. Create a page (HierachyViewer.jspx) and add it to the adfc-config.
   
2. Drag and drop from the data controls palette the RootCasesView as hierarchy viewer:
   
   
   
3. Choose radial:
   
   
   
4. Select in hierarchy all 3 levels (and accept all other default values):
   
   
   

If this created page is run the page opens like this:



You can click on the + sign underneath the Case 10 box and then the page looks like this:



I would like to display all children on page opening. To achieve this you can just set the displayLevelsChildren property on the dvt:hierarchyViewer tag to 100 then it will display 100 children (if they exist). However I choose to set this property to the max number of levels that are currently present in the database table.

Determine the max number of levels

To determine the max number of levels 2 functions are created (implementation of the functions are found at the end of the blog):

1. Get_levels_to_root (i_n_cse_id IN tst_cases.id%TYPE); this function returns the number of levels between i_n_cse_id and the root tst_cases (with parent_cse_id null)
   
2. Get_max_number_of_children; this functions loops over all tst_cases and executes get_levels_to_root for the current id, the max number found is returned.
   

A view object (read only) is created that executes this function (and is added to the application module):



A managed bean (request scope) is created to execute this view object and return the MaxNumber attribute as int:



The implementation of this bean:

package nl.capgemini.marianneHorsch.view;



import javax.faces.context.FacesContext;

import nl.capgemini.marianneHorsch.model.services.TestAppModuleImpl;

import nl.capgemini.marianneHorsch.model.views.MaxNumberOfChildrenViewImpl;

import nl.capgemini.marianneHorsch.model.views.MaxNumberOfChildrenViewRowImpl;



import oracle.adf.model.binding.DCBindingContainer;

import oracle.adf.model.binding.DCDataControl;

import oracle.binding.BindingContainer;



public class TestHierarchyViewBean {

public TestHierarchyViewBean() {

super();

}



public int getMaxNumberOfChildren() {

TestAppModuleImpl service = getService();

MaxNumberOfChildrenViewImpl view = service.getMaxNumberOfChildrenView();

view.executeQuery();

MaxNumberOfChildrenViewRowImpl row =

(MaxNumberOfChildrenViewRowImpl)view.first();

return (row == null ? 1 : (row.getMaxNumber() == null ? 1 :

row.getMaxNumber().intValue()));

}



public TestAppModuleImpl getService() {

DCBindingContainer bc = (DCBindingContainer)FacesContext

.getCurrentInstance().getApplication().evaluateExpressionGet

(FacesContext.getCurrentInstance(), "#{bindings}",

BindingContainer.class);

if (bc == null) {

return null;

}

DCDataControl dc = bc.findDataControl("TestAppModuleDataControl");

if (dc == null) {

return null;

}

return (TestAppModuleImpl)dc.getDataProvider();

}

}

Add to the <dvt:hierarchyViewer> tag the property displayLevelsChildren set the value to #{hierarchyViewerBean.maxNumberOfChildren}.

If we now run the page it opens like:

Function: get_levels_to_root

CREATE OR REPLACE FUNCTION get_levels_to_root

( i_n_cse_id IN tst_cases.id%TYPE)

RETURN NUMBER

AS

--

CURSOR c_cse (b_parent_id tst_cases.parent_cse_id%TYPE)

IS

SELECT id, name, parent_cse_id

FROM tst_cases

WHERE id = b_parent_id;

r_cse c_cse%ROWTYPE;

l_b_found BOOLEAN;

--

l_n_id tst_cases.id%TYPE;

l_n_levels NUMBER(10,0) := 0;

--

BEGIN

--

l_n_id := i_n_cse_id;

--

WHILE (l_n_id IS NOT NULL)

LOOP

--

OPEN c_cse (b_parent_id => l_n_id);

FETCH c_cse

INTO r_cse;

l_b_found := c_cse%FOUND;

CLOSE c_cse;

--

IF (l_b_found)

THEN

l_n_levels := l_n_levels + 1;

l_n_id := r_cse.parent_cse_id;

ELSE

l_n_id := NULL;

END IF;

--

END LOOP;

--

RETURN l_n_levels;

--

END get_levels_to_root;

Function: get_max_number_of_children

CREATE OR REPLACE FUNCTION get_max_number_of_children

RETURN NUMBER

AS

--

l_n_number NUMBER(10,0);

l_n_max_number NUMBER(10,0) := 0;

--

BEGIN
--

FOR r IN (SELECT id

FROM tst_cases)

LOOP

--

l_n_number := get_levels_to_root(i_n_cse_id =>r.id);

IF (l_n_number > l_n_max_number)

THEN

l_n_max_number := l_n_number;

END IF;

--

END LOOP;

--

RETURN l_n_max_number;

--

END get_max_number_of_children;