Matthew Wilson

Posts tagged with "ADF BC"

Improving the SRDemo Shuttle Adapter

Monday, 11. June 2007, 14:03:32

In the ADF Developer's Guide and in the SRDemo sample application, there is a backing bean class to help populate a SelectManyShuttle component from two tables with a foreign key association.

This is already fairly flexible as you can specify the underlying value, the label and even a description attribute for each item that is retrieved. There is a level of flexibility that is missing though, and that is the ability to specify more than one attribute to serve as the label (or description).

Say you wanted to be able to assign several employees to a project using the shuttle adapter as backing for a SelectOneShuttle component. You would obviously choose the EmployeeId as the value attribute, but what attribute would you choose for the label? Either FirstName or LastName is likely to be repeated, but a combination of both (with maybe another attribute in the description to differentiate employees with the exact same name) would make it much easier to select the correct employee.

It is actually fairly simple to modify the existing shuttle backing described in the Developer's Guide. The end result of this being that you can specify the allItemsDisplayAttrName attribute in the following form:

LastName, FirstName

where any word that resolves to an attribute name in the row will be replaced by the value of that attribute, and any other charaters will stay as they are. So this example might give you a list of employees looking like this:

Abel, Ellen
Ande, Sundar
Atkinson, Mozhe
Austin, David
Baer, Hermann
Baida, Shelli
Banda, Amit
Bates, Elizabeth
Bell, Sarah
etc.

The work for this is done in the ShuttlePageBackingBeanBase class and in the getAllItems method. Before we do anything, it looks like this:

public List getAllItems() {
  if (allItems == null) {
    allItems = ADFUtils.selectItemsForIterator(allItemsIteratorName,
                                               allItemsValueAttrName,
                                               allItemsDisplayAttrName,
                                               allItemsDescriptionAttrName);
  }
  return allItems;
}

I've replaced this code completely with my own, but you could easily move it into the ADFUtils so as to make this generic for anywhere that uses the selectItemsForIterator method.

Here's the modified method:

public List getAllItems() {
  if (allItems == null) {
    allItems = new ArrayList();
    
    ArrayList attrNameList = new ArrayList();
    DCIteratorBinding iter = ADFUtils.findIterator(allItemsIteratorName);
    AttributeDef[] attrDefs = iter.getAttributeDefs();
    for (int i = 0; i < attrDefs.length; i++)
    {
      AttributeDef attrDef = attrDefs[i];
      String attrName = attrDef.getName();
      if (allItemsDisplayAttrName.matches(".*\\b" + attrName + "\\b.*") ||
          allItemsDescriptionAttrName.matches(".*\\b" + attrName + "\\b.*"))
      {
        attrNameList.add(attrName);
      }
    }
    
    for (Row r: iter.getAllRowsInRange())
    {
      for (int i = 0; i < attrNameList.size(); i++)
      {
        String attrName = (String)attrNameList.get(i);
        allItemsDisplayAttrName = allItemsDisplayAttrName.replace(attrName,
          r.getAttribute(attrName).toString());
        allItemsDescriptionAttrName = allItemsDescriptionAttrName.replace(attrName,
          r.getAttribute(attrName).toString());
      }
      
      allItems.add(new SelectItem(r.getAttribute(allItemsValueAttrName),
        allItemsDisplayAttrName,
        allItemsDescriptionAttrName));
    }
  }
  return allItems;
}

This code finds the specified iterator, then extracts the attribute definitions. From there it uses a regular expression match to find if the attribute name is used in either the display or description strings. If an attribute is found, it is stored for later use.

The code then iterates through each of the rows in the iterator and then uses the stored attribute names to do a global replace on the strings with the actual values from the current row.

I hope this is simple enough not to need an example project. If you have any questions, just pop a reply in here and I can get back to you.

2 comments

How To Create a Super-Intersection View

Friday, 4. May 2007, 09:37:49

Intersection Table, ADF BC

When you use ADF Business Components, you can create them from database tables. This is an extremely useful feature and helps map foreign key relationships. It also means that for a many-to-many relationship with an intersection table, you can easily extract all children of a specific parent.

One use case that appears quite regularly with large database schemas is not covered by this method however. That is when you have a many-to-many relationship with more than one table inbetween them. A diagram of this situation is shown below. In this case you may want to retrieve a list of all the "grandchildren" of a "grandparent". That is, all the children of all the children of an item. In the example below, you may want to find all the books in all the collections for one library.

As it stands, in ADF you would need to use the LIBRARY_COLLECTIONS intersection to get a list of collection IDs and then apply that as a view criteria on the COLLECTION_BOOKS table (e.g. WHERE COLLECTION_ID IN (12, 23, 65, 32)). You would then have a list of the book IDs.

This is not the best solution, nor a very desireable one. A much better way would be to design a business component that represents an imaginary "super" intersection table, encompassing all three tables between LIBRARIES and BOOKS.

First you need to design an entity object to hold the needed attributes. Create a new entity object.

Call it LibraryBooks, and at first map it to the LIBRARY_COLLECTIONS intersection table. This is simply so that there is some database table that is referenced. Plus it helps in mapping the first attributes needed.

On the next step the wizard automatically adds all the columns from the selected table (in this case LIBRARY_ID and COLLECTION_ID). You want to keep all the foreign keys and discard any other columns. In this case we're keeping both columns as they are foreign keys and will help in creating a primary key later on.

At this point you will also need to add some attributes. Use the New... button rather than the New from Table... one. Add columns for the foreign keys of the COLLECTION_BOOKS table, unless they are already represented. In this case the COLLECTION_BOOKS table has 2 foreign keys: COLLECTION_ID and BOOK_ID, however COLLECTION_ID is already in the list, so we only need to add BOOK_ID.

Make sure that the Primary Key check box is selected. On the next step, make sure all the other foreign keys also have this check box selected. Basically, the IDs from each of the three main tables will be sufficient to define a primary key on this new table. You can also have any fields you need from the two intersection tables (e.g. a status column).

Then click Finish. Right-click on the entity object and select New Default View Object....

Go with the default name of LibraryBooksView. Open up the view object editor for the newly created LibraryBooksView.

Obviously the default SQL query is not going to work, since there is no column named BOOK_ID on the LIBRARY_COLLECTIONS table. To get this right, you will need to select "Expert Mode", which is a posh way of saying "do it yourself"!

The important points to note here are that the FROM clause has the two intersection tables: LIBRARY_COLLECTIONS and COLLECTION_BOOKS, and there is a WHERE clause that equates the COLLECTION_ID column on both tables. This means that a join is performed between the two tables.

I've renamed the alias for LIBRARY_COLLECTIONS from LibraryBooks to LibraryCollections so it is easier to read. We don't need the COLLECTIONS table in this query because we don't have any columns from it. If we did then it should be included. In this example I've taken the COLLECTION_ID column from the LIBRARY_COLLECTIONS table, but it could have easily come from the COLLECTION_BOOKS table instead.

Now that you have the view object representing this "super" intersection, you can use it to create associations and links with the LIBRARIES and BOOKS tables as you would with any other entity and view objects.

Have fun!

No comments

Showing a list of items based on the contents of another list in ADF BC

Friday, 26. January 2007, 10:16:23

ADF BC

This is a query rather than a solution, although if someone works out how to do it, I'll update this post with the solution.

I have constructed a very simple example schema to illustrate my query. Here is a picture of the schema.

It has three entity tables: Employee, Department and Location, and two intersection tables: Employee_Location and Department_Location.

What I want to do is have a creation process for an employee where you first fill in the details (forname and surname), then assign them to department, then depending on that department assign them to one or more possible locations.

So each department is based in one or more locations and an employee can also be based in one or more locations, but only those where the department is based (imagine that the employee is a door-to-door salesman and the department has several areas of a town that it covers).

The first two steps are easy - the details step can be modelled using a form on the employee view object and the department assignment can be done with a list of values drop down on the department view object.

The third step is a little more complex because you can't just show a list of all locations, since some of these will not apply to the department. So my question is how do I model this using ADF Business Components?

Update: 21st March 2007
I have now been able to solve this problem thanks to a blog entry by Frank Nimphius: Working with the afselectManyListsbox (Part I). This describes how to use the ViewCriteria object to perform a search on a view using a list of values, which is exactly what I needed - thanks Frank.

2 comments