26.3 Advanced Entity Association Techniques

This section describes several advanced techniques for working with associations between entity objects.

26.3.1 Modifying Association SQL Clause to Implement Complex Associations

When you need to represent a more complex relationship between entities than one based only on the equality of matching attributes, you can modify the association's SQL clause to include more complex criteria. For example, sometimes the relationship between two entities depends on effectivity dates. A ServiceRequest may be related to a Product, however if the name of the product changes over time, each row in the PRODUCTS table might include additional EFFECTIVE_FROM and EFFECTIVE_UNTIL columns that track the range of dates in which that product row is (or was) in use. The relationship between a ServiceRequest and the Product with which it is associated might then be described by a combination of the matching ProdId attributes and a condition that the service request's RequestDate lie between the product's EffectiveFrom and EffectiveUntil dates.

You can setup this more complex relationship in the Association Editor. First add any additional necessary attribute pairs on the Entity Objects page, which in this example would include one (EffectiveFrom, RequestDate) pair and one (EffectiveUntil, RequestDate) pair. Then on the Association SQL page you can edit the Where field to change the WHERE clause to be:

(:Bind_ProdId = ServiceRequest.PROD_ID) AND
(ServiceRequest.REQUEST_DATE BETWEEN :Bind_EffectiveFrom
                                 AND :Bind_EffectiveUntil)

26.3.2 Exposing View Link Accessor Attributes at the Entity Level

When you create a view link between two entity-based view objects, on the View Link Properties page, you have the option to expose view link accessor attributes both at the view object level as well as at the entity object level. By default, a view link accessor is only exposed at the view object level of the destination view object. By checking the appropriate In Entity Object: SourceEntityName or In Entity Object:DestinationEntityName checkbox, you can opt to have JDeveloper include a view link attribute in either or both of the source or destination entity objects. This can provide a handy way for an entity object to access a related set of related view rows, especially when the query to produce the rows only depends on attributes of the current row.

26.3.3 Optimizing Entity Accessor Access By Retaining the Row Set

Each time you retrieve an entity association accessor row set, by default the entity object creates a new RowSet object to allow you to work with the rows. This does not imply re-executing the query to produce the results each time, only creating a new instance of a RowSet object with its default iterator reset to the "slot" before the first row. To force the row set to refresh its rows from the database, you can call its executeQuery() method.

Since there is a small amount of overhead associated with creating the row set, if your code makes numerous calls to the same association accessor attributes, you can consider enabling the association accessor row set retention for the source entity object in the association. To use the association accessor retention feature, first enable a custom Java entity collection class for your entity object. As with other custom entity Java classes you've seen, you do this on the Java panel of the Entity Object editor by selecting the Entity Collection Class checkbox. Then, in the YourEntityCollImpl class that JDeveloper creates for you, override the init() method, and add a line after super.init() that calls the setAssociationAccessorRetained() method passing true as the parameter. It affects all association accessor attributes for that entity object.

When this feature is enabled for an entity object, since the association accessor row set it not recreated each time, the current row of its default row set iterator is also retained as a side-effect. This means that your code will need to explicitly call the reset() method on the row set you retrieve from the association accessor to reset the current row in its default row set iterator back to the "slot" before the first row.

Note, however, that with accessor retention enabled, your failure to call reset() each time before you iterate through the rows in the accessor row set can result in a subtle, hard-to-detect error in your application. For example, if you iterate over the rows in an association accessor row set like this, for example, to calculate some aggregate total:

// In ProductImpl.java
RowSet rs = (RowSet)getServiceRequests();
while (rs.hasNext()) {
  ServiceRequestImpl r = (ServiceRequestImpl)rs.next();
  // Do something important with attributes in each row
}

The first time you work with the accessor row set, the code will work. However, since the row set (and its default row set iterator) are retained, the second and subsequent times you access the row set the current row will already be at the end of the row set and the while loop will be skipped since rs.hasNext() will be false. Instead, with this feature enabled, write your accessor iteration code like this:

// In ProductImpl.java
RowSet rs = (RowSet)getServiceRequests();
rs.reset(); // Reset default row set iterator to slot before first row!
while (rs.hasNext()) {
  ServiceRequestImpl r = (ServiceRequestImpl)rs.next();
  // Do something important with attributes in each row
}