Wednesday, February 4, 2015

Creating ADF Tree Table Programatically, ArrayList cannot be cast to JUCtrlHierBinding

ADF tree table can easily be created using POJO as data source in the way described in these blogs Blog1, Blog2

But if you try to access data of that tree from a managed bean, you get java.util.ArrayList cannot be cast to oracle.jbo.uicli.binding.JUCtrlHierBinding exception. And this error is caused because the tree data is not being accessed through the ADF binding layer. To resolve this issue, instead of using a POJO class we need to use a POJO Data Control as the ADF model for tree data

Here is the updated way of creating tree table based on POJO data:

- Create an Employee class
public class Employee {
 private String name;
 private String location;
 private List<Employee> directs;

 public Employee(String name, String loc, List<Employee> directs){
    this.name = name;
    this.location = loc;
    this.directs =  directs;
  }
  public String getName() {
    return name;
  }
  public String getLocation() {
    return location;
  }
  public List<Employee> getDirects() {
    return directs;
  }
}

- Create an EmployeeBean Class
public class EmployeeBean {
    private List<Employee> allEmployees;
    public EmployeeBean() {
        super();
    }
    public void setAllEmployees(List<Employee> allEmployees) {
        this.allEmployees = allEmployees;
    }

    public List<Employee> getAllEmployees() {
        List<Employee> directList = new ArrayList<Employee>(); 
        directList.add(new Employee("Ken", "New York", new ArrayList<Employee>()));
        Employee manager1 = new Employee("John", "London", directList);
        directList= new ArrayList<Employee>();
        directList.add(new Employee("Ramesh", "London", new ArrayList<Employee>()));
        Employee manager2 = new Employee("Ravi", "Bangalore",directList);
        directList= new ArrayList<Employee>();
        directList.add(new Employee("Rakesh", "Pune", new ArrayList<Employee>()));
        Employee manager3 = new Employee("Raju", "Pune",  directList);

        allEmployees = new ArrayList<Employee>();
        allEmployees.add(manager1);
        allEmployees.add(manager2);
        allEmployees.add(manager3);
        return allEmployees;
    }
}


- Right click on EmployeeBean class and select 'Create Data Control'. This will create an EmployeeBean data control that can be used to create the tree table.
- Drag and drop allEmployees from EmployeeBean data control on to the page as a tree component.

Now, you can access tree data without any issues

Assigning default search values for LOV Search and Select popup

Values for search attributes on LOV Search and Select popup can be preset using a launchPopupListener method

<af:inputComboboxListOfValues id="inpClv2"
    popupTitle="Search and Select: #{bindings.StateProvince.hints.label}"
    value="#{bindings.StateProvince.inputValue}"
    label="#{bindings.StateProvince.hints.label}"
    model="#{bindings.StateProvince.listOfValuesModel}"
    required="#{bindings.StateProvince.hints.mandatory}"
    columns="#{bindings.StateProvince.hints.displayWidth}"
    shortDesc="#{bindings.StateProvince.hints.tooltip}"
    launchPopupListener="#{backingBeanScope.lovBean.stateLaunchPopupListener}"> 
</af:inputComboboxListOfValues>


Suppose if we want to preset CountryId in Search and Select popup for State LOV, the launchPopupListener code will look like:
public void stateLaunchPopupListener(LaunchPopupEvent launchPopupEvent)
{
    UIXInputPopup lovComponent = (UIXInputPopup)launchPopupEvent.getSource();
    ListOfValuesModel model = lovComponent.getModel();
    if (model != null)
    {          
        QueryDescriptor queryDesc = model.getQueryDescriptor();
        ConjunctionCriterion conCrit = queryDesc.getConjunctionCriterion();
        List<Criterion> criterionList = conCrit.getCriterionList();
        for (Criterion criterion: criterionList)
        {
            AttributeDescriptor attrDesc = ((AttributeCriterion) criterion).getAttribute();
            if (attrDesc.getName().equalsIgnoreCase("CountryId")) 
            {
                List values = ((AttributeCriterion) criterion).getValues();
                values.set(0, "US"); 
            }
        }
    }           

}

Sunday, January 11, 2015

Change bar data based on selected slice: ADF Pie Graph

Let us see how we can programmatically change Y1Axis bar data based on the selected slice on a pie graph.

First we need to set up the data for pie graph
 - Create Countries, Restaurants and Revenue tables and populate some data in those based on the script, which has been  taken from  AMIS blog
 - Create a viewObject named CountryFinanceVO with below query:
SELECT countries.id country_id, countries.name country_name, SUM(revenue) country_revenue, SUM(costs) country_costs
FROM dve_countries countries, dve_restaurants  restaurants, dve_revenue revenue
WHERE  countries.id   = restaurants.country_id
AND    restaurants.id = revenue.restaurant_id
GROUP BY countries.id, countries.name
  - Create an application module named GraphAM and add a CountryFinanceVO instance into it

Drag CountryFinanceVO on to a page as pie graph of Ring-Bar type.
In the next window use following configuration


Add a shapeAttributes component to pieGraph and specify a clickListener, which will listen to the click event on a pie slice
<dvt:pieGraph id="pieGraph1"
                      value="#{bindings.CountryFinancesVO1.graphModel}"
                      subType="RING_BAR" shortDesc="Pie graph"
                      binding="#{pageFlowScope.graphBean.pieGraphBinding}"
                      dataSelection="single">
    <dvt:shapeAttributesSet>
        <dvt:shapeAttributes component="GRAPH_SLICE" clickable="true"
                     clickListener="#{pageFlowScope.graphBean.pieClickListener}"/>
    </dvt:shapeAttributesSet>
    <dvt:background>
        <dvt:specialEffects/>
    </dvt:background>
    <dvt:graphPieFrame/> 
    <dvt:y1Axis/>
    <dvt:sliceLabel/>
    <dvt:pieLabel rendered="true"/>
    <dvt:legendArea automaticPlacement="AP_NEVER"/>
</dvt:pieGraph>



Pie graph will always show Y1Axis bar corresponding to the data of first row in the view object rowset. So, we will need to make the row corresponding to the slice clicked as the first row of the rowset in order to show the related data on the Y1Axis bar. To achieve this, we will modify our View Object query as below

SELECT countries.id   country_id,      countries.name country_name,      SUM(revenue)   country_revenue,      SUM(costs)     country_costs
FROM   dve_countries    countries,      dve_restaurants  restaurants,     dve_revenue      revenue
WHERE  countries.id   = restaurants.country_id
AND    restaurants.id = revenue.restaurant_id AND countries.id = :querySelectedId
GROUP BY countries.id       , countries.name
union all
SELECT countries.id   country_id,      countries.name country_name,      SUM(revenue)   country_revenue,      SUM(costs)     country_costs
FROM   dve_countries    countries,      dve_restaurants  restaurants,     dve_revenue      revenue
WHERE  countries.id   = restaurants.country_id
AND    restaurants.id = revenue.restaurant_id AND countries.id != :querySelectedId
GROUP BY countries.id       , countries.name


We will use a bind variable which will contain the value of countryId for the selected slice.  We have the union of two queries, first gets the row with the selected country_id and second gets the all other rows. The bind variable has been defaulted with the value of first country_id.

In pieClickListener we will retrieve country_id of the selected slice and then invoke ExecuteWithParams with that as parameter.

  public void pieClickListener(ClickEvent clickEvent)
  {
    ComponentHandle handle = clickEvent.getComponentHandle();
    SeriesComponentHandle handleSeries = (SeriesComponentHandle)handle;

    if (handle instanceof SeriesComponentHandle)
    {
      Attributes[] seriesInfo =
        ((SeriesComponentHandle) handle).getSeriesAttributes();    

      String idValue = null;
      if (seriesInfo != null)
      {
        for (Attributes attrs: seriesInfo)
        {       
          idValue=
              (String) attrs.getValue(Attributes.ID_VALUE);
        }

        OperationBinding op =  ADFUtils.findOperation("ExecuteWithParams");
        op.getParamsMap().put("querySelectedId",idValue);
        op.execute();

        AdfFacesContext.getCurrentInstance().addPartialTarget(pieGraphBinding);

      }

    }

  }

 
  Now when you click on any slice, the slice will move to extreme right which is the location of the first series in the graph, and Y1Axis bar will display corresponding data.

Sql for pie graph

Script taken from AMIS-blog
It is used in my another post Change bar data based on selected slice: ADF Pie Graph  
CREATE TABLE DVE_COUNTRIES 
(
  ID NUMBER NOT NULL 
, NAME VARCHAR2(20) NOT NULL 
, CONSTRAINT DVE_COUNTRIES_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);

ALTER TABLE DVE_COUNTRIES
ADD CONSTRAINT DVE_COUNTRIES_UK1 UNIQUE 
(
  NAME 
)
ENABLE;

INSERT INTO "DVT_USER"."DVE_COUNTRIES" (ID, NAME) VALUES ('1', 'Netherlands');
INSERT INTO "DVT_USER"."DVE_COUNTRIES" (ID, NAME) VALUES ('2', 'Sweden');
INSERT INTO "DVT_USER"."DVE_COUNTRIES" (ID, NAME) VALUES ('3', 'England');
INSERT INTO "DVT_USER"."DVE_COUNTRIES" (ID, NAME) VALUES ('4', 'France');
INSERT INTO "DVT_USER"."DVE_COUNTRIES" (ID, NAME) VALUES ('5', 'Australia');
INSERT INTO "DVT_USER"."DVE_COUNTRIES" (ID, NAME) VALUES ('6', 'China');
INSERT INTO "DVT_USER"."DVE_COUNTRIES" (ID, NAME) VALUES ('7', 'Italy');

CREATE TABLE DVE_RESTAURANTS 
(
  ID NUMBER NOT NULL 
, NAME VARCHAR2(30 BYTE) NOT NULL 
, COUNTRY_ID NUMBER NOT NULL 
, CONSTRAINT DVE_RESTAURANTS_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);

ALTER TABLE DVE_RESTAURANTS
ADD CONSTRAINT DVE_RESTAURANTS_UK1 UNIQUE 
(
  NAME 
)
ENABLE;

ALTER TABLE DVE_RESTAURANTS
ADD CONSTRAINT DVE_RESTAURANTS_COUNTRIES_FK1 FOREIGN KEY
(
  COUNTRY_ID 
)
REFERENCES DVE_COUNTRIES
(
  ID 
)
ENABLE;

INSERT INTO "DVT_USER"."DVE_RESTAURANTS" (ID, NAME, COUNTRY_ID) VALUES ('1', 'The Frying Dutchman', '1');
INSERT INTO "DVT_USER"."DVE_RESTAURANTS" (ID, NAME, COUNTRY_ID) VALUES ('2', 'The Cheesecake Factory', '1');
INSERT INTO "DVT_USER"."DVE_RESTAURANTS" (ID, NAME, COUNTRY_ID) VALUES ('3', 'The Frosty Palace', '2');
INSERT INTO "DVT_USER"."DVE_RESTAURANTS" (ID, NAME, COUNTRY_ID) VALUES ('4', 'Dorsia', '3');
INSERT INTO "DVT_USER"."DVE_RESTAURANTS" (ID, NAME, COUNTRY_ID) VALUES ('5', 'Tom’s Restaurant', '3');
INSERT INTO "DVT_USER"."DVE_RESTAURANTS" (ID, NAME, COUNTRY_ID) VALUES ('6', 'Jackrabbit Slim’s', '3');
INSERT INTO "DVT_USER"."DVE_RESTAURANTS" (ID, NAME, COUNTRY_ID) VALUES ('7', 'Gusteau’s', '4');
INSERT INTO "DVT_USER"."DVE_RESTAURANTS" (ID, NAME, COUNTRY_ID) VALUES ('8', 'Texas Chili Parlor', '5');
INSERT INTO "DVT_USER"."DVE_RESTAURANTS" (ID, NAME, COUNTRY_ID) VALUES ('9', 'Big Kahuna Burger', '5');
INSERT INTO "DVT_USER"."DVE_RESTAURANTS" (ID, NAME, COUNTRY_ID) VALUES ('10', 'Bronto Burger', '5');
INSERT INTO "DVT_USER"."DVE_RESTAURANTS" (ID, NAME, COUNTRY_ID) VALUES ('11', 'Teriyaki Donut', '6');
INSERT INTO "DVT_USER"."DVE_RESTAURANTS" (ID, NAME, COUNTRY_ID) VALUES ('12', 'Nuovo Vesuvio', '7');
INSERT INTO "DVT_USER"."DVE_RESTAURANTS" (ID, NAME, COUNTRY_ID) VALUES ('13', 'Chotchkies', '7');

CREATE TABLE DVE_REVENUE 
(
  ID NUMBER NOT NULL 
, RESTAURANT_ID NUMBER NOT NULL 
, REVENUE NUMBER(10, 0) DEFAULT 0 NOT NULL 
, COSTS NUMBER(10, 0) DEFAULT 0 NOT NULL
, CONSTRAINT DVE_REVENUE_PK PRIMARY KEY (ID) ENABLE 
);

ALTER TABLE DVE_REVENUE
ADD CONSTRAINT DVE_REVENUE_RESTAURANTS_FK1 FOREIGN KEY
(
  RESTAURANT_ID 
)
REFERENCES DVE_RESTAURANTS
(
  ID 
)
ENABLE;

Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (1,1,30000,20000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (2,2,20000,10000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (3,3,30000,10000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (4,4,15000,10000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (5,5,25000,20000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (6,6,30000,0);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (7,7,10000,0);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (8,8,20000,10000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (9,9,30000,5000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (10,10,15000,15000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (11,11,20000,5000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (12,12,30000,10000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (13,13,5000,0);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (14,1,20000,10000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (15,2,10000,0);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (16,3,50000,30000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (17,4,10000,5000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (18,5,30000,25000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (19,6,40000,30000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (20,7,0,0);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (21,8,10000,1000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (22,9,50000,30000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (23,10,15000,10000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (24,11,25000,20000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (25,12,80000,30000);
Insert into DVT_USER.DVE_REVENUE (ID,RESTAURANT_ID,REVENUE,COSTS) values (26,13,5000,1000);

commit;