Handling assigments with af:selectManyShuttle + Master Detail Hierarchy

The af:selectManyShuttle component belong to the caste of the select components. Those components canbe populated by static or dynamic data, and the user can interact with them, like performing operations, filtering results, and so on. The select many schuttle implements a very common use case : handle assigments.

In my experience, every database schema has a case where a table holds two foreign key, each one referencing one table. For exemple, it could be aircraft and airport, the middle table will be the landings ; employees and projects, the middle table will be the workings. See, there's plenty ! When you create a view object, you can choose attributes and let the one you don't want behind :

So, the af:selectManySchuttle is the adf components that implements this.
But how do you populate it ? Forget ADF native stuff, bindings and wizards. Oh yes, you'll have to put in your hands in the dirt. Because today the only way to use af:selectManySchuttle is to use GOFJ.

1. Tables

For this test case, I’ve created a quite simple workspace. Let’s first take a quick look at the physical and model layers. Well there’s really nothing physical since I build the workspace with an offline database and static view object: so you can test it, modify, try stuff ...

See ? Easy ! An inconvenient is that we cannot create an entity layer. But you’ll see by the end of this paper that it’ll be of no use. Let’s go on with the model layer : with this I’ve created three view objects and one view link. This last one is just to be able to choose on whom people we’re going to assign knowledge.


Here. The model layer is build. Let’s go the view (since thanks to ADF, we don’t really care about controller). So as I wrote, there’s nothing drag’n’droppable for configuring an af:selectManySchuttle ; nothing in the bindings either. Maybe in the next release? In the mean time, your boss wants this.

2. Building and populating af:selectManySchuttle

I’ve build a simple UI : one panelSplitter and the table bound to the People data control, on the left. On the right, let’s click on the source tab to build our own af:selectManySchuttle. Before all this, you should open three files: the jsp, the backing bean and the page definition. When all this is done, we begin the stuff. Use this code in the second facet of panelSplitter to begin with:

Values attributes of both af:selectManySchuttle and f:selectItem are important here.

• Attribute value of af:selectManySchuttle : the selected values (so the values on the right of the select many component in the UI)
• Attribute value of f:selectItem : all possible values.

Each one is bound to a backing bean attribute via EL. The next step is to create these attribute :


The idea here is to write our own getter for mySelectItems. The code will iterate over the knowledge iterator from page definition to build the complete list of knowledge to return. In the meantime, the code will check if the current knowledge is acquired by the current person. If so, it will be added to the both lists. In fact, this one getter will build all we need. And the attribute initialSelectValue will help us match what the end-user has assigned, so we can delete or create new rows in the experience table.

public SelectItem[] getMySelectItems() {
// That way this method will be called only once
if (mySelectItems == null) {
// Getting iterators from bindings
DCIteratorBinding people = (DCIteratorBinding)this.getBindings().get("People1Iterator");
DCIteratorBinding experience = (DCIteratorBinding)this.getBindings().get("Experience1Iterator");
DCIteratorBinding knowledge = (DCIteratorBinding)this.getBindings().get("Knowledge1Iterator");

// Creating the SelectItem array to return and the value attribute of the schuttle itselft
mySelectItems = new SelectItem[knowledge.getAllRowsInRange().length];
myManySchuttleValue = new ArrayList();

// Browsing knowledge
for (int i = 0 ; i < item =" new" j =" 0" initialselectedvalue="myManySchuttleValue;">

Run the application, you'll see that it works, but only for the first people in the iterator. Next step is synchronizing the table and the schuttle.

3. Master table and detail schuttle

Now of course you want to display in the schuttle information related to the selected user in the table. For that, we'll need a selection listener on the table. The method called will change the current row of the iterator in the page definition to point it to the selected row in the table. And since this is PPR, you'll need to enable it on the target component, the af:selectManySchuttle via its partialTriggers attribute.

In the table, add this :
selectionListener="#{backingBeanScope.backing_assignKnowledgeToPeople.peopleSelectionListener}"

Wich points to this method in the backing bean :

public void peopleSelectionListener(SelectionEvent se) {
RichTable rt = (RichTable)se.getSource();
FacesCtrlHierNodeBinding node = (FacesCtrlHierNodeBinding)rt.getSelectedRowData();

DCIteratorBinding people = (DCIteratorBinding)this.getBindings().get("People1Iterator");
people.setCurrentRowWithKeyValue(node.getRowKey().getAttribute(0).toString());

mySelectItems = null;
}

That's it. All you need to add is the partialTriggers attribute in the af:selectManySchuttle component. See that's an important point. Try not to do it : you'll get an error message. One thing is left :

4. Saving changes

Since you saved the initial selected values, you just have to read the the actual selected values ; and process code :

• When a selected value was in the initial list and is not anymore : delete ;
• When a selected value is not in the initial list : add ;
• When a selected value was in the initial list : do nothing.

And that is it.

[download coming soon]

Export dvt:pivotTable to Excel : yes, it's possible

Edit : there's a new component in the new JDeveloper version. You can view it in action here.

-------------------------

I some time ago posted one OTN JDeveloper Forum about how to export the content of a dvt:pivotTable to Excel. Unfortunatly, no reel response appears, even with the help of Frank Nimphius. Concerning this problem, at today's date, the situation is :

• Using af:exportCollectionListener adf component does not work for dvt:pivotTable component ;
  
• This feature is in the roadmap for the next release of JDev ;
  
• To export ADF pivot table, you don't have a choice : using a 3rd party java library (did you say GOFJ ?)
  

So I searched, looked around, and finally figured the situation out. And this is a blog. So let's be story telling-y. Here's what I do :

1. Looking for a way to access the content of the current pivot table displayed in UI

Of course : before investigating in java api for Excel, or how to prompt an "open file" in web browser, this first point had to be figured out since it's the the most critical one. If accessing content of pivot table is not possible, the use case will not be solved.

The component pivot table was bound to the backing bean. My first try was to use methods from oracle.adf.view.faces.bi.component.pivotTable.UIPivotTable : I did not find anything to browse the content of the pivot table.

At this point I posted on the forum and got no answer. Then I abandonned this and put some colors in the pivot table, with the help of this post from AMIS blog. I adapted the class to my needs (colors and style). It's basicly something like this :

public class PivotManager {

public CellFormat getDataFormat(DataCellContext cxt) throws SQLException {

CellFormat cellFormat = new CellFormat(null, null, null);
Object currentData = cxt.getValue();

// My own color and style rules
// ....

return cellFormat;

}

public CellFormat getHeaderFormat(HeaderCellContext cxt) {

CellFormat cellFormat = new CellFormat(null, null, null);
Object currentHeader = cxt.getValue();

// My own color and style rules
// ...

return cellFormat;
}

But then ! Eureka. The whole content plus the headers are accessed in this class. Let me explain.

2. Figuring the way data cells are accessed

When naviguation comes to the page wich encapsulate the dvt:pivotTable component, two things happen in the lifecycle of the application :

• The backing bean scope of the page begin
• One request scope is launched (the bean for colors and styles, PivotManager, is of request scope) each 10 columns.
  

Yes, you read it right. Each cell calls the PivotManager.getDataFormat for the tenth first columns (neverming the number of lines); then, a new request is launched in the lifecycle. To be clearer :

If you put a new attribute and add this in the getDataFormat method :

public class PivotManager {

private int count=1;

public CellFormat getDataFormat(DataCellContext cxt) throws SQLException {

CellFormat cellFormat = new CellFormat(null, null, null);
Object currentData = cxt.getValue();
if (currentDate != null) {
System.out.println("Current Cell Data Accessed in PivotManager class : "+ cxt.getValue() + " ; " + this.count);
this.count++;

// ....

You'll see that no matter how many lines you got, the order in wich the method will be called is :

• The first cell to be called when the page is load for the first time is the one on the upper top left corner ;
• Then it'll go on the same line (so the first), one cell by one cell, to the cell of the tenth column
• Then the first cell of the second line
• To the cell on line two and column ten
• ...
• The the final line and tenth column : then a new instance of PivotManager is created ; scope-wise, it's a new request, since PivotManager is a bean of request scope.
  

To help you understand, one (crapy) diagram of what's happening for a pivotTable with 11 to 20 columns.
I tried to tweak the view object tuning properties, and the iterator in the page definition : it's still ten column * total number of lines at a time (so a "request-time). If someone knows, please share : I'm interessted. That's not really problematic : it's just the Excel export has to be handled in another bean of a different scope, since the backing bean is way too big (there's a lot of stuff on that page).

3. Java Excel API use / Web browser prompt "open or save as"

So I create a bean of scope application (let's be Rambo like ; no I'm kidding. Neither backing, request, pageFlow or session scope were right) named PivotToExcel, wich will handle all exports features. So first, I'll share the PivotManager modified, with new getDataFormat method. You will note that getHeaderFormat does not contain an exportToExcel method. This is because you can get dimensions name of the data currently accessed in PivotManager with cxt.getQDR().getDimMember("xxDimNamexx"). So one cell data is owned by the total number of dimensions.

public class PivotManager {

public CellFormat getDataFormat(DataCellContext cxt) throws SQLException {

CellFormat cellFormat = new CellFormat(null, null, null);

// My own color and style rules
// ....


// Block to get the PivotToExcel bean
FacesContext fc = FacesContext.getCurrentInstance();
ValueExpression ve = fc.getApplication().getExpressionFactory().createValueExpression(fc.getELContext(), "#{PivotToExcel}", PivotToExcel.class);
PivotToExcel pivotToExcel = (PivotToExcel)ve.getValue(fc.getELContext());

// Block to format data ; in my case, there's three dimensions : CodeProjet, Agence // and Trigramme. This first part is for handling null values.
String dimCP = "";
String dimAgence = "";
String dimTrigramme = "";
if (cxt.getQDR().getDimMember("Agence") != null) dimAgence = cxt.getQDR().getDimMember("Agence").toString();
if (cxt.getQDR().getDimMember("CodeProjet") != null) dimCP = cxt.getQDR().getDimMember("CodeProjet").toString();
if (cxt.getQDR().getDimMember("Trigram") != null) dimTrigramme = cxt.getQDR().getDimMember("Trigram").toString();
// The data cells contains numbers in te float format
float valeur = 0;
if (cxt.getValue() != null) valeur = Float.parseFloat(cxt.getValue().toString());
// Calling the method of PivotToExcel bean to add the current cell to the Excel sheet
pivotToExcel.addCell(valeur, dimCP, dimTrigramme, dimAgence);

return cellFormat;

}

public CellFormat getHeaderFormat(HeaderCellContext cxt) {

CellFormat cellFormat = new CellFormat(null, null, null);

// My own color and style rules
// ...

return cellFormat;
}

For creating an Excel file, I'm using JExcel API. It's pretty practical : you can create writable workbook as file or outputStream, create or add worksheet, format cells (numbers, date, boolean ; colors and style). You may find one tutorial, a FAQ and the java docs.

PivotToExcel of application scope is instanciated once : the first time the bean is getted in getDataFormat method of PivotManager. It creates one writableWork when initiated. Workbook.createWorkbook(new File(fileName +".xls")); creates a new Excel sheet in the current directory, which you can get via getCurrentDir() method of this bean. Cells are added with addCell(...) methd : here you'll have to write your own code : mine was to specific. Using the JExcel tutorial, you'll see that it's easy. Cells are added ... voila !

To open the Excel file, just create an action component with actionListener bound to a backing bean that gets the current instance of PivotToExcel (you can use the code of PivotManager, to get PivotToExcel) : then just call exportToExcel(). What will happen :

• The workbook will be written in the current directory with the specified name. To access the current directory, the method getCurrentDir() at the end of the class is here for that !
• The workbook is closed.
• public static synchronized void downloadFile(String filename, String fileLocation, String mimeType, FacesContext facesContext) is called : it basicly copy the Excel file created into the outputStream of ((HttpServletResponse)facesContext.getExternalContext().getResponse()).getOutputStream()
  
• facesContext.responseComplete() : "signal the JavaServer Faces implementation that the HTTP response for this request has already been generated (such as an HTTP redirect), and that the request processing lifecycle should be terminated as soon as the current phase is completed" Javadoc.

public class PivotToExcel implements Serializable {

private WritableWorkbook workbook;
private WritableSheet sheet;
private String fileName;
private String pathToFile=this.getCurrentDir()+"\\";

public PivotToExcel() {
create();
}

public void create() {

// Generating a name
java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy.MM.dd.HH.mm.ss");
this.fileName = sdf.format(new Date());

try {
workbook = Workbook.createWorkbook(new File(fileName +".xls"));
} catch (IOException e) {
}
sheet = workbook.createSheet("First Sheet", 0);
}

public void exportToExcel () {
// you could call a method to put lay your sheet out
// ...

// Writing the workbook and the temporary file
try {
workbook.write();
} catch (IOException e) {
}
catch (java.lang.ArrayIndexOutOfBoundsException aioobe) {
}
try {
workbook.close();
} catch (IOException e) {
} catch (WriteException e) {
}


// Method to prompt in the web browser the download window
downloadFile(fileName +".xls",
this.pathToFile,
"application/vnd.ms-excel",
FacesContext.getCurrentInstance());


// You delete the temporary file
File f = new File(pathToFile + fileName +".xls");
f.delete();

// And create a new one
this.create();

}

public void addCell(float arg1, String dim1, String dim2, String dim3) {
// Your own way to insert in the sheet
// For help see JExcel tutorial
}

public static synchronized void downloadFile(String filename, String fileLocation, String mimeType,
FacesContext facesContext) {

ExternalContext context = facesContext.getExternalContext();
//String path = context.getInitParameter("externalFiles") + fileLocation;
String path = fileLocation;
String fullFileName = path + filename;

File file = new File(fullFileName);

HttpServletResponse response = (HttpServletResponse) context.getResponse();
response.setHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");
response.setContentLength((int) file.length());
response.setContentType(mimeType);

try {
FileInputStream in = new FileInputStream(file);
OutputStream out = response.getOutputStream();

// Copy the contents of the file to the output stream
byte[] buf = new byte[1024];
int count;
while ((count = in.read(buf)) >= 0) {
out.write(buf, 0, count);
}
in.close();
out.flush();
out.close();
// cut lifecycle
facesContext.responseComplete();
} catch (IOException ex) {
System.out.println("Error in downloadFile: " + ex.getMessage());
ex.printStackTrace();
}
}

public String getCurrentDir () {
return System.getProperty("user.dir");
}

3. Result



4. The end

Another solution could have been to use the iterator and construct my own pivot table directly in the Excel sheet. But the way it's done here ensure that the pivot table in the Excel sheet will be the same as the one in your web browser.

GOFJ : An article to bookmark asap

Click and you'll know why it has to be bookmarked : click me !

GOFJ : Retrieving any bean with any scope

GOFJ ? We'll get back to that.

For now I'll just introduce a way of retrieving your managed bean instance in any place of your project's java classes :

public static Object retrieveManagedBean(String beanName, Class beanClass) {
FacesContext fc = FacesContext.getCurrentInstance();
ValueExpression ve = fc.
getApplication().
getExpressionFactory().
createValueExpression(fc.getELContext(), "#{"+beanName+"}", beanClass);

return ve.getValue(fc.getELContext());
}

Your beanName is specified in either adf-config either faces-config depending on what you've choosed. Also, for backing beans, "backingBeanScope.backing_pageName" is the beanName variable to use since it's the way it's generated by JDeveloper.

That's how it's done, in the Good Old Fashionned Java way.