JSF/Creator – Single table, Multiple data sources

I’ve had the
question come up several times from customers, so I though I write a
quick entry on this.

The problem is rather simple, I
want to show one table aggregating data from several datasources (two
different rowsets, a rowset and a web service, etc.). The key to
achieving this is to figure out how to get a hold on the “foreign key”
to be able to fire the second query.

Here are the
steps for a simple Customers/Orders table – the JSF table component
shows all customers and the last column shows the total number of
orders for each customer. These steps use the bundled database and the Order
DataSource.

1/ Drop a JSF table on the page grid
2/ Drop the CUSTOMER_TBL table on the component
3/ Drop the ORDER_TBL table on the page.
4/ Edit the order_tblRowSet query to add the CUSTOMER_TBL table and a
(=?) criteria for CUSTOMER_NUM.
5/ Create the following method in your page (or session) managed bean:

   
public int getNumberOrders() {
       
// Key operation: get the foreign key value for the current row
       
Integer idClient = (Integer)getValue("#{currentRow['CUSTOMER_NUM']}");

       
// Execute the query with the foreign key value
 
      // The strategy here is simple and can be improved

       
// optim #1: remove most of the columns in the order_tblRowSet query

     
// optim #2: use an database-specific SQL command to retrieve
the tuples count.
       
int result = 0;
       
try {
           
order_tblRowSet.setObject(1, idClient);
           
order_tblRowSet.execute();
     
      // count number of tuples
           
while (order_tblRowSet.next()) {
               
result++;
           
}
       
} catch (Exception ex) {
           
log("Error Description", ex);
       
} finally {
           
order_tblRowSet.close();
       
}
       
return result;
    }


6/ Finally,
bind the last column of the table to the numberOrders
property. The source code for the JSP should look like this:


<h:column binding="#{Page1.column1}" id="column1">
  <h:outputText binding="#{Page1.outputText1}" ... value="#{Page1.numberOrders}"/>

In this case, we’re using two tables in the
database, but we could be calling a web service, an EJB or simply a
JavaBean. The key again here is to get the foreign key for the current
table row and bind the additional column(s) to a method that makes use
of that key to retrieve more data.

These steps
should work fine in Creator
2 EA
(but I haven’t tested).

Author: alexismp

Google Developer Relations in Paris.