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

0

The UNPIVOT operator was introduced in Oracle Database 11g – see for example: http://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

Image

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

Image

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

The original query retrieves summary details for all departments:

Image

It returns data as is shown below:

Image

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:

Image

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

Image

I have created a ViewObject based on this query:

Image

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.

Image

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:

Image

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.

Image

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.

Image

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

Image

Edit the List Binding in the dialog that appears next:

Image

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:

Image

Press OK to close the dialog.

Your page will look something like this:

Image

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:

Image

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:

Image

and toggle to MaxSal:

Image

Resources

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

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

Leave a Reply