Using SQL UNPIVOT to prepare data for dynamic ADF Faces User Interfaces

The UNPIVOT operator was introduced in Oracle Database 11g – see for example: https://technology.amis.nl/blog/2421/the-oracle-11g-unpivot-operator-turning-columns-into-rows. In Oracle, the UNPIVOT operation is the process of turning Columns to Rows. Put simply, by applying the UNPIVOT operator to a number of columns, every row is split into that same number of rows. Each of these rows has two new columns: one for the column that this row stems from – one of the columns the data set was UNPIVOTed by – and one with the value from the column. The original UNPIVOT-columns are no longer part of the newly created records.

We can use the UNPIVOT operator in ADF BC ViewObjects to derive multiple rows from individual records. Subsequently, these rows can be filtered by the property they represent. This sounds vague – I know. I will use this approach to achieve the following functionality: a page that displays a bar chart with values per department. Depending on the setting of a radio group, the chart will display the number of employees, the maximum salary, the average experience of the average salary per department. The chart is based on a single ViewObject that contains a single query that uses UNPIVOT.g

Click on AvgSal and the graph is instantly refreshed to display:

Create the ViewObject with the UNPVIOT operator to retrieve Department Summary Data

The original query retrieves summary details for all departments:

It returns data as is shown below:

In order to be able to use the same bar chart for EMPCOUNT at one moment, for AVGSAL at another and for AVGEXP at a third moment, one thing we can do is make sure that the query returns these values in the same column. Unpivot is one way of doing that. Note: using a CASE expression and a bind parameter to guide it, we can achieve more or less the same result.

The UNPIVOT query is constructed like this:

The result of this query contains rows for every Department record, for each of the four properties that we have identified:

I have created a ViewObject based on this query:

The ViewObject has a View Criteria, called SelectedPropertySummary. This ViewCriteria filters from the ViewObject only the rows for the selected property – specified through the SummaryProperty bind variable.

The ViewObject is added to the Application Module’s Data Model – and for the VO Usage, I have specified that this ViewCriteria should be applied:

Using this ViewObject – and the Data Control’s Collection derived from it, it will turn out to be quite easy to create the JSF page with the multi-functional bar chart.

Creating the Web Page with the multi-functional bar chart

After creating a new JSF page, drag the ExecuteWithParams operation to the page, binding it as a button.

Drag the SummaryProperty to the page, dropping it as a List Binding (a Single Selection) of type ADF Select One Radio

Edit the List Binding in the dialog that appears next:

Press OK.

Drag the collection itself to the page and drop it as a Graph. Choose the Bar Chart as the graph style (although other graph types will work too). Configure the Bar Graph: drag the Value attribute to the Bars field and the Dname attribute to X-axis:

Press OK to close the dialog.

Your page will look something like this:

We can run it, and it will work. However, after making a selection in the radio group, we still need to press the button to update the graph:

We need to do one final thing to ensure that when the selection in the radio group is changed, the chart is immediately refreshed accordingly.

Set the attribute valueChangeListener on the SelectOneRadio component to:

valueChangeListener=”#{myBean.radioChangeListener}”

Create a Java Class called (for example) MyBean. Annotate it with the ManagedBean annotation. Create method radioChangeListener, like this:

import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.event.ValueChangeEvent;

import oracle.adf.model.BindingContext;
import oracle.adf.model.binding.DCBindingContainer;

import oracle.binding.OperationBinding;

@ManagedBean
@RequestScoped
public class MyBean {

    public void radioChangeListener(ValueChangeEvent vce) {
        BindingContext bcxt = BindingContext.getCurrent();
        DCBindingContainer bc = null;
        bc = (DCBindingContainer)bcxt.getCurrentBindingsEntry();
        OperationBinding oper =  bc.getOperationBinding("ExecuteWithParams");
        oper.getParamsMap().put("SummaryProperty", vce.getNewValue());
        oper.execute();
    }
}

Now we can remove the button from page and toggle the property to display the graph for by simply changing the selected radio button:

and toggle to MaxSal:

Resources

Download JDeveloper 11gR2 application with the sources for this article: UnpivotBasedDynamicBarChart.