ADF BC: Creating an "EXISTS" View Criteria

The EXISTS keyword in SQL queries is an efficient mechanism for returning record sets from one dataset when they exist in another dataset. For example we can write queries like:

SELECT org.org_id, org.name FROM organisations org WHERE EXISTS
(SELECT 1 FROM events evt WHERE evt.org_id = org.org_id
AND evt.contact_name = 'Eddie Harris')

....which returns all organisations which have a related event whose contact is Eddie Harris.

ADF Business Components in JDeveloper 11g allow the creation of EXISTS subqueries via the View Object named View Criteria feature. They're easy to implement if you already know how to create View Criteria, as long as you know one small trick.

Default Business Components

Given the SQL query above using the one-to-many organisations-to-events example, imagine we have default Entity Objects (EOs), EO Associations, View Objects (VOs) and VO Links, as seen in this picture:

View Object Link Accessors

When created via the Business Components from Table wizard, the VO Link OrgEvtFkLink created, based on the EO Association OrgEvtFkAssoc, will include Accessors options under its Relationship tab in the VO Link editor:

If you select the pencil icon next to the Accessors options it reveals the View Link Properties dialog:

....from which you can see the "Generate Accessor" option selected for the "In View Object: Organisations View". While the selected state is the default option when created, it's this option which is essential for setting up the EXISTS View Criteria.

View Object View Criteria

Once you've ensured the Accessor option is set as described above, when you create a new View Criteria for the View Object, Organisations in our case, we configure the View Criteria as follows. First in the Create View Criteria dialog we should change the View Criteria name to something more suitable to reflect what the View Criteria will do for us:

Next select the Add Criteria button, which will create the basis of the expression used by the query:

On selecting the Attribute drop down, you'll discover a list of attributes from the OrganisationsView VO. In this list you'll note an attribute called "EventsView". This attribute is only available because of the options you configured in the View Object Link Accessors above. If you hadn't gone with the default options, the EventsView attribute would not be available, and you would not be able to create the EXISTS View Criteria:

With the EventsView Accessor selected the dialog for the first time shows the EXISTS clause:

The only think left to do is to select the Criteria Group expression of the EXISTS statement, in the example above this is the "Event No =" option, and change this using the supplied options in the fields below, to the actual expression we want to use in the EXISTS clause. From our example this is matching the Events Contact Name to a String:

Once completed in the right hand side you can see the EXISTS subquery that the View Criteria will apply to the OrganisationsView VO when executed.

Note I've also turned off the Ignore Case and Ignore Null Values options.

Testing

In the Business Components Browser, on opening the OrganisationsView, and selecting the View Criteria via the Find button, we're first prompted for a value for the bind variable:

...which once supplied, returns the only matching Organisations record:

Thanks

Thanks to Eddie Harris at SAGE Computing Services for revealing the technique.

JDev: ADF BC and ADF Libraries: The Library Private property

ADF Libraries are a very useful feature of JDeveloper 11g. They allow a master application, via the Resource Palette, to load Bounded Task Flows (BTFs) and the BTF's associated ADF Business Components from separate applications, without having to include the BTF and ADF BC objects in it's own application.
In the following picture you can see the Resource Palette exposing adflibChildBTF.jar, a BTF application containing both the ChildBTF bounded task flow, and it's associated Business Components including the ChildAppModule Application Module, Events Entity Object and so on:

If this ADF Library is loaded into a consuming application's ViewController project, the consuming application is free to call the adflibChildBTF's embedded Bounded Task Flow. However it's also free to call directly the Business Components of the adflibChildBTF. This is revealed by the Data Control Panel in the Application Navigator, it will by default include the Application Module of the child application as can be seen here:

Yet this isn't always desirable behaviour, the Child Application may not want to expose all its Business Components for easy picking by the consuming application. For example, you may have included a collection of test Business Components in the Child Application which really aren't appropriate for the consuming application to use, or, you may think it inappropriate for the consuming application to even reference the regular Business Components of the Child Application. How to fix?

When you open the editor for most Business Components including Application Modules, Entity Objects and View Objects, you may notice that the Property Inspector also reflects options for the selected and edited Business Component. In the Property Inspector for each Business Component there's typically a boolean property not included in the associated editors called Library Private. In the following diagram you can see the property inspector for the Application Module from the Child Application:

This property, when set to true for each Business Component (the default is false), means the Business Components will not be available in the Resource Palette when the ADF Library is redeployed (note: you must refresh the Resource Palette to see this effect):

As can be seen in the previous picture, the ChildAppModule Application Module is no longer available in the Resource Palette. In addition in the consuming Application's Data Control Palette, as we've hidden the ChildAppModule, simply it doesn't appear in the Data Control Palette:

Returning to the Resource Palette, you may in fact want to hide *all* of the Business Components. This requires you to simply set the Library Private property of each Business Component to true, and then regenerate the ADF Library JAR. We can see the end effect here:

However it must be noted, in the consuming application, this does not stop the consumed BTF from working. All the Library Private option is doing is hiding the Business Components from the IDE for the user to use. The Business Components are still used by Child Application/BTF when called from the caller at runtime.

Caveat

I've yet to fully use this feature, so be careful to check it works for you, your mileage will vary. If you find any issues I'd appreciate a comment to this blog post, to assist other readers.

Addendum

This feature is available in the latest version of JDev 11.1.1.4.0 through 11.1.1.2.0. It's possibly available in earlier versions, but I'll leave readers to check themselves as I no longer have these earlier JDev versions installed.

In addition this feature works within an Application too, such that the Business Components don't show in the Data Control Palette for the same Application's ViewController project.