26.7 Controlling Entity Posting Order to Avoid Constraint Violations

Due to database constraints, when you perform DML operations to save changes to a number of related entity objects in the same transaction, the order in which the operations are performed can be significant. If you try to insert a new row containing foreign key references before inserting the row being referenced, the database can complain with a constraint violation. This section helps you understand the default order for processing of entity objects during commit time and how to programmatically influence that order when necessary.

Note: The examples in this section refer to the ControllingPostingOrder project in the AdvancedEntityExamples workspace. See the note at the beginning of this chapter for download instructions.

26.7.1 Understanding the Default Post Processing Order

By default, when you commit the transaction the entity objects in the pending changes list are processed in chronological order, in other words, the order in which the entities were added to the list. This means that, for example, if you create a new ServiceRequest and then a new Product related to that service request, the new ServiceRequest will be inserted first and the new Product second.

26.7.2 How Compositions Change the Default Processing Ordering

When two entity objects are related by a composition, the strict chronological ordering is modified automatically to ensure that composed parent and child entity rows are saved in an order that avoids violating any constraints. This means, for example, that a new parent entity row is inserted before any new composed children entity rows.

26.7.3 Overriding postChanges() to Control Post Order

If your related entities are associated but not composed, then you need to write a bit of code to ensure that the related entities get saved in the appropriate order.

26.7.3.1 Observing the Post Ordering Problem First Hand

Consider the newServiceRequestForNewProduct() custom method from an ExampleModule application module in Example 26-13. It accepts a set of parameters and:

1. Creates a new ServiceRequest.

2. Creates a new Product.

3. Sets the product id to which the server request pertains.

4. Commits the transaction.

5. Constructs a Result Java bean to hold new product ID and service request ID.

6. Results the result.

Note: The code makes the assumption that both ServiceRequest.SvrId and Product.ProdId have been set to have DBSequence data type to populate their primary keys based on a sequence.

Example 26-13 Creating a New ServiceRequest Then a New Product and Returning the New Ids

// In ExampleModuleImpl.java
public Result newServiceRequestForNewProduct(String prodName,
                                             String prodDesc,
                                             String problemDesc,
                                             Number customerId) {
  // 1. Create a new ServiceRequest
  ServiceRequestImpl newSR = createNewServiceRequest();
  // 2. Create a new Product
  ProductImpl newProd = createNewProduct();
  newProd.setName(prodName);
  newProd.setDescription(prodDesc);
  // 3. Set the product id to which service request pertains
  newSR.setProdId(newProd.getProdId().getSequenceNumber());
  newSR.setProblemDescription(problemDesc);
  newSR.setCreatedBy(customerId);
  // 4. Commit the transaction
  getDBTransaction().commit();
  // 5. Construct a bean to hold new product id and SR id
  Result result = new Result();
  result.setSvrId(newSR.getSvrId().getSequenceNumber());
  result.setProdId(newProd.getProdId().getSequenceNumber());
  // 6. Return the result
  return result;
}
private ServiceRequestImpl createNewServiceRequest() {
  EntityDefImpl srDef   = ServiceRequestImpl.getDefinitionObject();
  return (ServiceRequestImpl)srDef.createInstance2(getDBTransaction(),null);   
}
private ProductImpl createNewProduct() {
  EntityDefImpl srDef   = ProductImpl.getDefinitionObject();
  return (ProductImpl)srDef.createInstance2(getDBTransaction(),null);   
}

If you add this method to the application module's client interface and test it from a test client program, you get an error:

oracle.jbo.DMLConstraintException:
JBO-26048: Constraint "SVR_PRD_FK" violated during post operation:
"Insert" using SQL Statement
"BEGIN
  INSERT INTO SERVICE_REQUESTS(
    SVR_ID,STATUS,REQUEST_DATE,
    PROBLEM_DESCRIPTION,PROD_ID,CREATED_BY)
   VALUES (?,?,?,?,?,?)
   RETURNING SVR_ID INTO ?; 
END;".
## Detail 0 ##
java.sql.SQLException:
ORA-02291: integrity constraint (SRDEMO.SVR_PRD_FK) violated
           - parent key not found

The database complains when the SERVICE_REQUESTS row is inserted that the value of its PROD_ID foreign key doesn't correspond to any row in the PRODUCTS table. This occurred because:

• The code created the ServiceRequest before the Product

• ServiceRequest and Product entity objects are associated but not composed

• The DML operations to save the new entity rows is done in chronological order, so the new ServiceRequest gets inserted before the new Product.

26.7.3.2 Forcing the Product to Post Before the ServiceRequest

To remedy the problem, you could reorder the lines of code in the example to create the Product first, then the ServiceRequest. While this would address the immediate problem, it still leaves the chance that another application developer could creating things in an incorrect order.

The better solution is to make the entity objects themselves handle the posting order so it will work correctly regardless of the order of creation. To do this you need to override the postChanges() method in the entity that contains the foreign key attribute referencing the associated entity object and write code as shown in Example 26-14. In this example, since it is the ServiceRequst that contains the foreign key to the Product entity, you need to update the ServiceRequest to conditionally force a related, new Product to post before the service request posts itself.

The code tests whether the entity being posted is in the STATUS_NEW or STATUS_MODIFIED state. If it is, it retrieves the related product using the getProduct() association accessor. If the related Product also has a post-state of STATUS_NEW, then first it calls postChanges() on the related parent row before calling super.postChanges() to perform its own DML.

Example 26-14 Overriding postChanges() in ServiceRequestImpl to Post Product First

// In ServiceRequestImpl.java
public void postChanges(TransactionEvent e) {
  /* If current entity is new or modified */
  if (getPostState() == STATUS_NEW ||
      getPostState() == STATUS_MODIFIED) {
    /* Get the associated product for the service request */
    ProductImpl product = getProduct();
    /* If there is an associated product */
    if (product != null) {
      /* And if it's post-status is NEW */
      if (product.getPostState() == STATUS_NEW) {
        /*
         * Post the product first, before posting this
         * entity by calling super below
         */
        product.postChanges(e);
      }
    }
  }
  super.postChanges(e);
}

If you were to re-run the example now, you would see that without changing the creation order in the newServiceRequestForNewProduct() method's code, entities now post in the correct order — first new Product, then new ServiceRequest. Yet, there is still a problem. The constraint violation still appears, but now for a different reason!

If the primary key for the Product entity object were user-assigned, then the code in Example 26-14 would be all that is required to address the constraint violation by correcting the post ordering.

Note: An alternative to the programmatic technique discussed above, which solves the problem at the J2EE application layer, is the use of deferrable constraints at the database layer. If you have control over your database schema, consider defining (or altering) your foreign key constraints to be DEFERRABLE INITIALLY DEFERRED. This causes the database to defer checking the constraint until transaction commit time. This allows the application to perform DML operations in any order provided that by COMMIT time all appropriate related rows have been saved and would alleviate the parent/child ordering described above. However, you would still need to write the code described in the following sections to cascade-update the foreign key values if the parent's primary key is assigned from a sequence.

In this example, however, the Product.ProdId is assigned from a database sequence, and not user-assigned in this example. So when a new Product entity row gets posted its ProdId attribute is refreshed to reflect the database-assigned sequence value. The foreign key value in the ServiceRequest.ProdId attribute referencing the new product is "orphaned" by this refreshing of the product's ID value. When the service request row is saved, its PROD_ID value still doesn't match a row in the PRODUCTS table, and the constraint violation occurs again. The next two sections discuss the solution to address this "orphaning" problem.

26.7.3.3 Understanding Associations Based on DBSequence-Valued Primary Keys

Recall from Section 6.6.3.8, "Trigger-Assigned Primary Key Values from a Database Sequence" that when an entity object's primary key attribute is of DBSequence type, during the transaction in which it is created, its numerical value is a unique, temporary negative number. If you create a number of associated entities in the same transaction, the relationships between them are based on this temporary negative key value. When the entity objects with DBSequence-value primary keys are posted, their primary key is refreshed to reflect the correct database-assigned sequence number, leaving the associated entities that are still holding onto the temporary negative foreign key value "orphaned".

For entity objects based on a composition, when the parent entity object's DBSequence-valued primary key is refreshed, the composed children entity rows automatically have their temporary negative foreign key value updated to reflect the owning parent's refreshed, database-assigned primary key. This means that for composed entities, the "orphaning" problem does not occur.

However, when entity objects are related by an association that is not a composition, you need to write a little code to insure that related entity rows referencing the temporary negative number get updated to have the refreshed, database-assigned primary key value. The next section outlines the code required.

26.7.3.4 Refreshing References to DBSequence-Assigned Foreign Keys

When an entity like Product in this example has a DBSequence-valued primary key, and it is referenced as a foreign key by other entities that are associated with (but not composed by) it, you need to override the postChanges() method as shown in Example 26-15 to save a reference to the row set of entity rows that might be referencing this new Product row. If the status of the current Product row is New, then the code assigns the RowSet-valued return of the getServiceRequest() association accessor to the newServiceRequestsBeforePost member field before calling super.postChanges().

Example 26-15 Saving Reference to Entity Rows Referencing this New Product

// In ProductImpl.java
RowSet newServiceRequestsBeforePost = null;
public void postChanges(TransactionEvent TransactionEvent) {
  /* Only bother to update references if Product is a NEW one */
  if (getPostState() == STATUS_NEW) {
    /* 
     * Get a rowset of service requests related
     * to this new product before calling super
     */
    newServiceRequestsBeforePost = (RowSet)getServiceRequest();
  }
  super.postChanges(TransactionEvent);
}

This saved RowSet is then used by the overridden refreshFKInNewContainees() method shown in Example 26-16. It gets called to allow a new entity row to cascade update its refreshed primary key value to any other entity rows that were referencing it before the call to postChanges(). It iterates over the ServiceRequestImpl rows in the newServiceRequestsBeforePost row set (if non-null) and sets the new product ID value of each one to the new sequence-assigned product value of the newly-posted Product entity.

Example 26-16 Cascade Updating Referencing Entity Rows with New ProdId Value

// In ProductImpl.java
protected void refreshFKInNewContainees() {
  if (newServiceRequestsBeforePost != null) {
    Number newProdId = getProdId().getSequenceNumber();
    /* 
     * Process the rowset of service requests that referenced
     * the new product prior to posting, and update their
     * ProdId attribute to reflect the refreshed ProdId value
     * that was assigned by a database sequence during posting.
     */
    while (newServiceRequestsBeforePost.hasNext()) {
      ServiceRequestImpl svrReq =
        (ServiceRequestImpl)newServiceRequestsBeforePost.next();
      svrReq.setProdId(newProdId);
    }
    closeNewServiceRequestRowSet();
  }  
}

After implementing this change, the code in Example 26-13 runs without encountering any database constraint violations.