How to implement an unique key constraint in ADF BC?

In an ADF project that deals with building a membership administration application for a sporting organization, I ran against the question how I could implement the validation of the uniqueness of an alternative key. For example, an entity organization member has a primary key member_id. However it would be nice when it is avoided that the same person is registered more than once in the system. Let’s assume that the last_name and birth_date combination sufficiently identifies an individual in the context of this sporting club. For an Oracle developer the solution is clear enough: just add a uniqueness contraint to the members table. But when we do not have the availability of this advanced database, it must be implemented in the business tier.
According to the J2EE concept this is the place where the business logic resides. One advantage of this implementation versus the database constraints at least is that when a user creates multiple new rows c.q. entity objects before submitting them to the database and there are validation errors detected by means of database constraints, it could be hard for the end-user to understand which entity object is wrong, resulting in less usability.

When building this common type of validation in ADF BC, it was clear to me that I have to iterate thru a collection of entity objects, in this case members, in order to check whether there is a duplicate object. The Entity Object Editor of JDeveloper can generate an EntityCollImpl class, which represents this collection. The question that puzzeled me was how to get a handle to this collection from the validateEntity() method in the EntityImpl class. The only method that sounded promissing to me was getAllEntityInstancesIterator() in EntityDefImpl, but it didn’t became clear to me how use it… After some time I queried Google and found a very informative web site of Steve Muench: Dive into BC4J and ADF. Here I found the solution.

First lesson to me was that scanning the collection of enity objects is not sufficient, but that the database must be queried too in order to check that the new object doesn’t already exist. According to Steve Muench, best way to implement this database check is to create a special view object that is not based on an entity. In my example this is the MemberExistsView that is based on the query:

   select null as x
   from ittcs_members
   where upper(last_name) = rtrim(upper(:0))
   and birth_date = :1
   and member_id != :2

By the way, the reason why I use rtrim() is that I don’t trust spaces at the end of a string that is entered in a web page form or a fat Swing client. Creating and using such a view will give performance gain because we reuse a PreparedStatement.

Next step, according to Steve Muench, is to generate the EntityDefImpl class. Two methods have to be added to this class:

1. A method to retrieve the view object. An example of this method is:

   private ViewObject getAccountExistsView(DBTransaction t) {
     ApplicationModule root = t.getRootApplicationModule();
     ViewObject vo = root.findViewObject("MemberExistsView");
     if (vo == null) {
       vo = root.createViewObject("MemberExistsView","model.MemberExistsView");
     }
     return vo;
   }

Note that the view object will be created only once. This is done by method createViewObject of the ApplicationModule class.

2. A method to check the existence of a duplicate record in the database and cache. An example of this method is:

   public boolean exists( DBTransaction t
                        , String lastName
                        , Date birthDate
                        , Number id
                        ) {
     ViewObject vo = getMemberExistsView(t);
     vo.setWhereClauseParam(0,lastName);
     vo.setWhereClauseParam(1,birthDate);
     vo.setWhereClauseParam(2,id);
     vo.setForwardOnly(true);
     vo.executeQuery();
     if (vo.first() != null) {
       System.out.println("found member in database!");
       return true;
     }
     // If we didn't find it in the database, try checking against any
     // members in cache
     com.sun.java.util.collections.Iterator iter  getAllEntityInstancesIterator(t);
     while (iter.hasNext()) {
       MembersImpl m = (MembersImpl)iter.next();
       if (m.getLastName() == null) {
         System.out.println("empty cache element");
       }
       else {
         String ln = m.getLastName().toUpperCase().trim();
         Date bd = m.getBirthDate();
         Number pk = m.getMemberId().getSequenceNumber();
         if ( ln.equals(lastName.toUpperCase().trim()) &&
              bd.equals(birthDate) &&
              !pk.equals(id) ) {
           System.out.println("found member in cache!");
           return true;
         }
       }
     }
     return false;
   }

In this method parameters are bounded to the PreparedStatement first. When the view object contains an object, then the record is not unique and the validation fails. Steve Muench points to the tuning possibilities in the View Object Editor of JDeveloper: set to ‘retrieve at most one row from database’. This saves roundtrips.
When we do not find a duplicate record in the database, we must check the cache that is retrieved by (indeed) getAllEntityInstancesIterator(). Iterating tru this collection is straightforward. However I found some things that I think is a bit tricky:
(a) You have to be aware that the cache can also contain ’empty’ objects, that only have the primary key set. This happens for instance when the InsertNewRecord button is pressed in the ApplicationModule tester. The new row appears on the screen, but is also created in the cache. I didn’t expect this and discovered this mechanism in the hard way when NullPointerExceptions were raised. To account for this you should test first if the object retrieved from the cache is such an ’empty’ object, before performing for instance a toUpperCase() function. See code above.
(b) Another attribute to know is the post-state of an entity. When the alternative key fields can not be changed, then it might be tested if e.getPostState() == Entity.STATUS_NEW, where e is the entity object in the cache.
(c) It seems to me that the cache has some kind of ‘intelligence of its own’. That is, sometimes I suddely saw more objects in the cache without understanding why they appeared at that particular moment. I have to test this more closely.
(d) A more general remark is about the oracle.jbo.domain classes like Number and Date. Especially Number can easily be confused with java.lang.Number. I found it handy to create a common utility method like:

   public static oracle.jbo.domain.Number toADFNumber(Long l) {
     oracle.jbo.domain.Number n = null;
     if (l != null) {
       n = new oracle.jbo.domain.Number(l);
     }
     return n;
   }

At many places java wrapper classes need to be converted to the oracle types.

The last step of implementing uniqueness validation in ADF BC is to call the exists() method (see above) from the ValidateEntity() method in the EntityImpl class (wich is generated by the Entity Object Editor).

Steve Muench noted that Oracle is considering implementing a generic, efficient existence check as a base BC4J framework feature for a future release. I couldn’t find such a method, so I think it is not yet implemented. But it might be that I have overlooked that, if so please tell me.