ADF 10g Dynamic Columns: Or how to implement an updatable dynamic table

Although it is all about ADF 11g these days there are still some challenges in ADF 10g projects that run at some of my customers. Today I finished a task in which I had to create an updatable table in which the number of shown columns wasn’t known at design time. There is the possibility to create an ADF read only dynamic table, that works more or less like the richfaces columns <rich:columns/> http://livedemo.exadel.com/richfaces-demo/richfaces/columns.jsf?tab=usage&cid=3305454  element. However, I needed an updatable table, but ADF doesn’t know such a component. I had to come up with a different solution.
Have a look at the use case: I have a table in which I need to show revenue data of a department. I do not know how many years I have to show neither do I know if the user wants to see years or quarters. I have to start showing data from the first year the department has revenue, and grouped by year, untill the last year which might be 2010, but can also be 2004. The columnheader shows the year. When I click on the header, the table needs to refresh, and display more or less the same data except now grouped by quarter for the selected year, and eventually drill further down to month level.

Preparation of the database.

The datamodel for this post is not that complex. I used the HR schema which I extended with a couple of tables and types.

The department table is linked to a new table which holds revenue data. There is also a timeframe table. This table contains definitions of timeframes. For this blog post it is not very relevant how the database part is implemented, however if you want to implement the database objects that I used, you can find the scripts to implement this datamodel and some data here:technology.amis.nl/blog/wp-content/uploads/prepareDatabase.txt
The script will do several things; First it will create the two extra tables, add some data in these tables. After that it will create 4 database types that can hold the data that is needed to show in the ADF application. Finally a package is created to query the database tables and to return the data to the calling ADF-BC method.

Creation of the ADF application.

I started with the creation of a new ADF application in JDeveloper. Usually the next step would be the creation of of ADF Business Components. However, in this case the model project initially only contains an empty application module. There are no entity objects or view objects needed for this use case. What I do need, is a class that contains the methods to implement the functionallity. For that I made sure to create the applicationModuleImpl class as well.

Preparation of the ADF Model.

The ADF model implementation is partially based on two posts by colleague Lucas Jellema: use sql type objects part I and use sql type objects part II. Both describe how to base an ADF application on SQL types. For the example used in this post I created three new objects in the model project. These objects will represent the database types created in the previous step (prepareDatabase.txt). The objects are created in a separate package within the model (revenue.types).

Next step is the creation of two simple methods in the applicationModuleImpl: One to get the revenue data form the database, and one to save the data. getRevenue(String deptId, String year) and saveRevenue(List<RevenueData> newRevenue). These are only very simple methods because the actual logic will be implemented in a class outside the application module, but in the revenue package where the types are in. The class that holds the getRevenue and saveRevenue is the RevenueHandler class.

Initially I wanted to use the revenueDataTable object (revenueDataTable List <revenueData>). Saddly this object is not of a type that is supported by the ADF binding framework. Instead of I used a List<revenueData> (which in the end is exactly the same). With the methods on the application module in place it is time to publish them to the client by shuttling them from available to selected.

Preparation of the ADF Bindings and ADF Faces.

The final application contains just one page in which the updatable dynamic table is used. The page is called revenueOverview.jspx. Since the two methods (getRevenue and saveRevenue) are published to the client, they can now be dropped on the page as an “ADF parameter form”. By doing so a button is created to call the method and two input fields are created for the parameters.

The button will not be used “as is” however, I mis-used it only to create the appropriate method binding in the page Definition file.

The most important part however is the table that will display the revenue data. I used a backing bean (revenueOverViewBB) to process the data that is retrieved from the method. This bean will also take care of saving the data it.

The backing bean also contains a method for the action Listener on the button that was created on the parameter Form. The actionListener now points to this new method in the revenueOverViewBB bean.

Here is where the created method in the pageDef come’s in handy. I will make a call to this method in the getRevenue method via de binding framework. Simply get the binding container, find the operation binding, and execute it. The result of the method is used to set the value of the revenueData property which in fact is a List of <RevenueData>.

DCBindingContainer bindings = getCurrentBindingContainer();
OperationBinding operationBinding = (OperationBinding)bindings.getOperationBinding("getRevenue");
operationBinding.getParamsMap().put("deptId", this.deptId);
operationBinding.getParamsMap().put("year", this.year);
setRevenueData((List<RevenueData>)operationBinding.execute());

With the getRevenue method in place, it’s time to create the table that will display the data. For that I first created a simple empty table tag () on the page, and via de binding editor the table was bound to the backingBean. This binding is necessary to programmatically refresh the table.

The number of columns that are being displayed in the table depends on the number of periodData rows in the RevenueData object. Remember: Every set of Revenue data has dynamic number of periods. After getting the revenue data (see previous code fragment), I simply determine the number of periodeData.

// if there is revenue data, set number of columns to add.
// be aware; This number is used in an af:forEach which index is zero based
if (revenueData.get(0).getPeriodData().size() > 0) {
setNumberOfColsToAdd(revenueData.get(0).getPeriodData().size() -
1);
}

This I can now use in an to add the appropriate number of columns to the table. The for each will start at zero, and continue untill the last period has been processed.

....................
<af:forEach begin="0" end="#{revenueOverViewBB.numberOfColsToAdd}"
varStatus="index">
<af:column formatType="text" sortable="false" id="CoLiCol">
....................

In the column header I want to display the name of the displayed period, for instanbce 2008 and 2009, or 2008-Q1……2008-4 or even 2008-1… 2008-12.
To achieve this I created one more object; The columnsHeader object which is a simple object with only one property being “value”.

package nl.amis.demo.view.type;

public class ColumnHeader {
private String value;
public ColumnHeader() {
}
public void setValue(String value) {
this.value = value;
}
public String getValue() {
return value;
}
}

In the backing bean the value for the column header is retrieved from the periodName, and for every period in the collection a header is added to the columnHeaders list.

for (int i = 0; i < _RevenueDataLine.getPeriodData().size(); i++) {
String _year =
_RevenueDataLine.getPeriodData().get(i).getPeriodName();
ColumnHeader header = new ColumnHeader();
header.setValue(_year);
columnHeaders.add(header);
}

In the <af:forEach> I now read the value of the columnheader for the appropriate period. Notice the columnHeaders[index.current] construction where I point to the correct columnHeader and get its value, simply by using the for each index.

.................
<af:forEach begin="0" end="#{revenueOverViewBB.numberOfColsToAdd}"
varStatus="index">
<af:column formatType="text" sortable="false" id="CoLiCol">
<f:facet name="header">
<af:commandLink text="#{revenueOverViewBB.columnHeaders[index.current].value}"
.................

I use a nested column construction because that enables me to display more information, like percentage of total revenue, within the same period. In this example it is not really of any use, however, I kept it in for possible future use.
In the end, the code for the dynamic table looks like this:

<af:table emptyText="No items were found" var="regels" banding="row"
varStatus="status" bandingInterval="1" id="myTab"
value="#{revenueOverViewBB.revenueData}"
binding="#{revenueOverViewBB.revenueDataTable}">
<af:column headerText="Id" formatType="text" noWrap="true"
id="IdCol" sortable="false">
<af:outputText value="#{regels.departmentId}" id="IdTx"/>
</af:column>
<af:column headerText="Department" formatType="text" noWrap="true"
sortable="false" id="NmCol">
<af:outputText value="#{regels.departmentName}" id="NmTx"></af:outputText>
</af:column>
<af:forEach begin="0" end="#{revenueOverViewBB.numberOfColsToAdd}"
varStatus="index">
<af:column formatType="text" sortable="false" id="CoLiCol">
<f:facet name="header">
<af:commandLink text="#{revenueOverViewBB.columnHeaders[index.current].value}"
actionListener="#{revenueOverViewBB.getRevenue}"
inlineStyle="text-align:center" id="CoLi"
disabled="#{revenueOverViewBB.year != null}">
<af:setActionListener from="#{revenueOverViewBB.columnHeaders[index.current].value}"
to="#{revenueOverViewBB.year}"/>
</af:commandLink>
</f:facet>
<af:column width="30px" id="BdCol">
<f:facet name="header">
<af:outputLabel value="revenue" id="BdLa"
inlineStyle="color:rgb(51,102,153); font-weight:bold;"/>
</f:facet>
<af:inputText value="#{regels.periodData[index.current].revenue}"
autoSubmit="true" required="true" id="BdTx"
partialTriggers="PeTx" columns="8"
valueChangeListener="#{revenueOverViewBB.cellContentChanged}">
<af:convertNumber type="number" locale="nl_NL"
pattern="0.00"/>
<f:attribute name="type" value="bedrag"/>
</af:inputText>
</af:column>
</af:column>
</af:forEach>
</af:table>

Saving the changed table content should be easy, however there is a catch. The binding framework does not support the object type, so I need to invoke the saveRevenue method directly on the application module. Be very careful with that! Because the value of input text fields are a direct reference to the periodeData collection, I just call the saveRevenue method and send the whole revenueData object to the application module.

try {
String EL = "#{data.DynaColsServiceDataControl.dataProvider}";
FacesContext fc = FacesContext.getCurrentInstance();
ValueBinding vb = fc.getApplication().createValueBinding(EL);
DynaColsServiceImpl svc = (DynaColsServiceImpl)vb.getValue(fc);

result =
svc.saveRevenue(revenueData);

} catch (JboException e) {
JsfUtils.getInstance().addFormattedError(e.getMessage());
return result;
}

How the actual translation of the revenueData object to the database sql types in the applicationModule is performed is not relevant for this article, however this is described in the posts by Lucas Jellema that I mentioned earlier.

Summary.

This post combines some of the posts that where published here in the past, and adds some new stuff. It describes how to implement an updatable dynamic table. It is about creating an adf application based on plsql-api and reading/writing data as a collection of sql type objects. The post also describes how to use information from a backing bean (or actually the data collection returned by a method call) to determine the number of columns in a table, or the header of table columns. After reading this post you should have some understanding of the implementation of this functionality. The use case described in this post is a simplified version of the actual functionality implemented in my project. To get going you download a version of the workspace here. Add the jsf-impl.jar and the adf-faces-impl.jar before running.

Potential issues after deployment to iAS.

After deployment It works perfectly……………. on your embedded OC4J. If you deploy this to a IAS you will possibly encounter two bugs.
The first one is described at http://forums.oracle.com/forums/thread.jspa?threadID=876599. You will find a workaround here as well. It’s about using typed arguments in the application modules client interface.
The second one is only encountered when using database access based on roles instead of instant direct grants.
This bug is described on metalink Bug 4439777 – JDBC SQLException binding user ADT to PLSQL. A workaround and solution is described as well. This bug is about using DB object types in ADF. JDBC Driver does not support access via synonyms. You must use the fully qualified name.