EntityLevelOnly Property To Delay Unique Key Validator

Idea for this post was kindly suggested by real ADF BC guru and evangelist - Steve Muench. You can check famous ADF Not Yet Documented Examples on Steve's blog - Dive into ADF.

The main idea is about composite Unique Key, based on multiple EO attributes. Ideally, users should be allowed to change composite Unique Key values, without getting validation errors too early (before trying to save changes). However, this is not the case by default - and I will explain in this post, how to make it work properly with EntityLevelOnly = true property.

Sample application - ComplexUniqueKeyApp.zip, implements alternate key for two attributes from the EO. These attributes are Letter and Digit (SQL script to create required DB table is provided with the sample):

Based on alternate key, Unique Key validation rule is created for Letter and Digit:

Both attributes are set with AutoSubmit = true on the UI, to make sure new value will be submitted immediately:

DB table is populate with unique values for Letter and Digit columns. I could try to change first row and set B for Letter and 3 for Digit. Obviously, I would change one of the values first, if I'm going to change A to B - this could force ADF to raise Unique Key error (there is already B for Letter with Digit 1). So, Unique Key validation error would be generated too early, before I would change Digit to 3:

First row is rendered on UI, with A for Letter and 1 for Digit. I'm going to try to change A to B:

As soon as I would leave Letter field and try to move to Digit, ADF would raise validation error about broken uniqueness:

This behaviour can be improved with EntityLevelOnly = true property in EO definition class. Override standard method - addUniqueKeyValidation to catch Unique Key validation rule definitions. Here we could check by name and apply setEntityLevelOnly(true) method for our Unique Key validator:

Validation rule name:

Can be verified using actual validation rule definition in the EO (this could be made generic, if required):

With EntityLevelOnly = true applied, I can change first part of the Unique Key, and move to change second part of the key to set new unique value:

Check Box Support in ADF Query (11g R2 and 12c)

Few years passed since my previous post about check box support in ADF Query - Yes-No Check Box in Query Criteria. It was not working before, but there are improvements in the latest ADF releases. I will describe approach to handle multiple values from the same column, using check box in ADF Query. By default, you could render multiple values with choice list - but there is a way to use a group of choice lists (when column values will be known in advance).

Sample application - ADFQueryCheckboxApp.zip, implements ADF Query with a criteria to search by employee status. This status could be of three types - internal, external and consultant. All three check boxes are rendered in ADF Query, under Status group (check box with Internal status is ticked by default):

You could tick External in addition and bring both Internal and External employees:

Tick External and Consultant - this will bring different results:

Status for Internal, External and Consultant is set through Status field in Employee table:

As you can see, status is encoded through I, E and C values accordingly. This would mean, I would need to define three attributes in the VO, pointing to the same column in DB. Each attribute should handle different status. I would need to have LOV VO, to to define a check box. In order to simplify it and avoid creating three different VO's for each attribute LOV - I'm going to create only one VO with bind variable. This SQL query would always return bind variable value back - this is what I'm going to control through View Accessor:

We are done with LOV VO. There are three attributes defined in VO, based on the same EO attribute. Each attribute for different status:

Make sure to set attribute Control Type to be Check Box, before defining LOV. I'm using UI Category - EmployeeStatys, to group attributes in ADF Query:

Each status attribute is assigned with LOV. Keep in mind - each LOV is configured through different View Accessor (this is needed to pass a proper bind variable, based on a status represented by the attribute):

Here you can see an example - 'E' external flag variable is configured for View Accessor used in LOV configured for External employee status attribute:

UI groupings visible in ADF Query UI, are configured through UI Categories section in VO:

Check box for Internal status was ticked, using default value in View Criteria - 'I':

Ready-To-Use SOA/BPM 11.1.1.7.1 Virtual Machine and Case Management Sample

There is a new excellent Virtual Box VM for the latest SOA/BPM 11.1.1.7.1. This one is my favourite, it includes latest Oracle products for SOA/BPM and WebCenter. All is configured, fine tuned and ready to use. Read more and download from Oracle OTN site - Pre-built Virtual Machine for SOA Suite and BPM Suite 11g.

The same as in previous release of this VM, you can start each server with one click in the console window:

UI for the standard BPM Worklist application is refined in SOA/BPM 11.1.1.7.1 release - it looks lighter and cleaner. Definitely, something users would like to use, everything can be customised if needed:

This new VM comes with a sample application for Case Management - Car Rental Sample. What I didn't liked about this sample - it doesn't implement proper ADF UI. There are two screens, first one brings a form to fill a car rental request. This is a proper ADF UI:

Press a button - Reserve Car. This will bring you to the next screen, with login dialog (this doesn't look like ADF anymore - login dialog UI reminds some plain HTML):

Provide jcooper/welcome1 credentials and login. You will be redirected to the screen with multiple tables. However, none of these tables are build with ADF:

It turns out, there is a basic inline frame included into ADF shell. Why to build separate application and include it into ADF with inline frame (while the same tables could be implemented in ADF):

Someone could get confused by this sample, and think of such design as a best practice. Or even worse - ADF not capable to implement a pair of tables. I hope this sample will be improved in the future and based fully on ADF.

Case Insensitive Search in LOV - Effective and Generic

Search in LOV dialog window, by default is not case insensitive. You could define View Criteria for LOV VO with case insensitivity and select this criteria to be displayed in LOV dialog. You could do this for one or two, may be for ten LOV's - but I'm sure you are going to get tired pretty soon. Much more effective is to implement generic solution to convert LOV search criteria to be UPPER case automatically.

I'm using sample application from my previous post about dynamic ADF BC and new dynamic ADF UI component in ADF 12c - ADF Dynamic ADF BC - Loading Multiple Instances (Nr. 100 in 2013). The same technique as described below can be applied also for design time ADF BC, across different ADF versions. Download sample application, updated for this post - ADFDynamicReportUI_v6.zip.

Default search dialog is case insensitive, you could test it quite easily. Try to search for lower case value, when you know there are upper case matching values - there will be no results:

SQL query is generated with a WHERE clause as it should, it is trying to search for matching values - but there are no such records in DB:

We could solve it with generic class - our custom View Object implementation class. My sample is using dynamic ADF BC, so I register this custom class programmatically with VO instance (typically you could do it through the wizard for design time ADF BC):

As I mentioned above, sample application is using ADF UI dynamic component, however the same works with regular ADF UI also - it doesn't matter:

Here is the main trick how to make search from LOV dialog to be case insensitive. You must override buildViewCriteriaClauses method in View Object implementation class. If current VO instance represents LOV (if you don't want to rely on naming convention, you could create additional View Object implementation class, intended to use only for LOV's), we should invoke setUpperColumns method applied for View Criteria. This converts entire View Criteria clause to use UPPER for both criteria item and bind variable:

Now with automatic conversion to UPPER case, try to execute the same search - you should see results:

View Criteria clause is constructed with UPPER and this is why it allows to perform case insensitive search. Of course, for runtime DB performance optimisation, you need to make sure there is functional index created for searchable columns:

The same works for any number of View Criteria items. Here I search using both attributes:

View Criteria clause contains both attributes and both are changed to use UPPER - this is perfect:

Case insensitive auto completion works as well with the technique described above. Try to type a value, existing in LOV - but with lower case (it_prog):

Such value is located and is automatically changed to use the case as it is originally stored in DB (IT_PROG):

View Criteria clause was constructed with UPPER in the case of auto completion as well as with regular search in LOV dialog:

ADF Query Design Revisited with ADF 12c Panel Drawer

My goal of this post is to take a closer look into ADF 12c and check how ADF Query layout can be optimised, using Panel Drawer component. There are several items, sample application is focusing on:

1. Panel Drawer component in ADF 12c and its usage for ADF Query

2. Declarative mode support for VO Query optimisation

3. Dynamic bindings in Page Definition for ADF Query

4. View Object query logging

Here you can download sample application - ADFQueryPreviewApp.zip. This is how UI looks initially, when Employees fragment is opened:

You should notice magnifying glass icon on the top right, this is rendered by ADF 12c Panel Drawer component. User could click on this icon and ADF Query would slide and become available, click on the same magnifying glass or anywhere else on the page - it will slide out automatically. This is really convenient, as it allows to save space on the screen - ADF Query is rendered on top of other components in a drawer:

Obviously, you could have as many panel drawers as you would prefer - this is not only for ADF Query. So, as you can see in the screenshot above, search is executed. There are only three columns in the results table - SQL query is generated accordingly with a key and the attributes for these three columns. This is a feature provided by VO declarative mode:

Move to the second accordion group and search for different value - this group displays more columns:

SQL query is different as well - it includes now all visible attributes. Such search implementation is especially useful for the cases, where many attributes must be displayed in the result set. It makes sense first to display result set with several attributes only and give user an option to see all the attributes using additional table. This would mean, initially SQL query would be lighter and it would fetch all attributes later, as in this example:

Let's check now technical details. VO is set to run in declarative mode and all the attributes (except primary key) are marked with Selected in Query = false. This allows to calculate displayed attributes on runtime, based on ADF bindings and construct SQL query accordingly:

There is one hidden gem in the sample application - generic class to provide detail logging for executed SQL queries and rows fetched. You could use it immediately in your project, without any change:

ADF Query is integrated into Panel Drawer using regular ADF Faces Show Detail Item component, there is custom magnifying glass image set:

Each accordion item, where results are displayed, is set with disclosure listener - this is needed to update current context and apply ADF Query to filter results either in Preview or Complete accordion items:

Accordion item is configured with JSF attribute, to identify itself:

Accordion item disclosure listener is updating currently opened item index, this will be used in ADF bindings - to map dynamically ADF Query binding with proper results iterator:

ADF Query in ADF Bindings is represented by Search Region. Unfortunately, Search Region property Binds doesn't work with dynamic expression. I resolved this with one trick - have defined new iterator, where Binds property is calculated dynamically (using current accordion item index). Search Region points to this this artificial iterator, and iterator in turn points to the VO instance. Both results tables are pointing two the same VO instances:

You can spot attribute definitions in the table bindings - VO declarative mode decides which attributes to include into SQL query, based on these attribute bindings defined.