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.

MDS Seeded Customization Approach with Empty External Project

Great feature in ADF - MDS Seeded customisations support. This is particularly useful for independent software vendors, who are developing their own products on top of ADF framework. With MDS Seeded customisation, maintenance for multiple customers becomes easier. We could create customisation, instead of changing original source code - this makes it easier to maintain a product. I would like to share one important hint, related to technical architecture for MDS Seeded customisations - this will be related to the way how MDS Seeded customisations are organised and maintained. By default, you would create MDS Seeded customisation files in the original application, however this is not very clean approach. There is a way to create and keep all MDS Seeded customisation files in empty external application. I will describe in the post, how this can be achieved with a few easy steps.

If you are going to test sample application - MDSCustomizationsApp.zip, with integrated WLS instances in JDeveloper, make sure to read this post (it describes how to setup local MDS repository in file system) - How To Setup MDS Repository for Embedded WLS Instance.

Let's start - you can download initial version of sample ADF application from the blog post mention above. Employees form and empty Submit button:

I don't want to create any MDS Seeded customisation files inside, rather I would build and deploy ADF library out of main application:

Sample comes with special application - CustomizationApp (you can find it in the archive). This application was created to keep MDS Seeded customisation files, no other purpose. Initially, empty project was created - where ADF library was imported (the one we have just deployed):

Empty project is enabled with MDS Seeded customisation support:

Restart JDeveloper in customisation mode - so we could create some customisations for the content from imported ADF library:

If MDS Seeded customisation mode was successfully applied, you should see some special icon, next to the application name. Choose 'Show Libraries' to see a list of libraries - so, we could see contents from imported ADF library:

All attached libraries will be displayed, you should locate our ADF library. Expand it and you should see application packaging:

We could apply several customisations now. Let's open Employees VO and define View Criteria (filter employees by salary >= 1000). This customisation will be stored inside our empty project:

There will be a change in AM - View Criteria will be applied for VO instance:

We could go and review XML files for applied MDS Seeded customisations. There is one for VO and AM. XML for AM contains delta information about applied View Criteria for VO instance:

One more customisation on UI level now - drag and drop Commit operation for Submit button:

This change creates two additional XML files with MDS Seeded customisations - for JSF fragment and Page Definition:

You must define MDS Seeded customisations deployment profile (MAR) for application with empty project (containing XML's):

Development is completed, now will be a last bit - deployment. Make sure WLS is started (you should start it separately, if you want to test MAR profile deployment):

Go ahead and deploy main application first - you should get a list of MDS repositories (see a hint how to define local test repository in the blog post mentioned above):

Once main application was deployed, you can apply MDS Seeded customisations and export them through a MAR file from external application:

You should see main application name in the wizard, MDS Seeded customisations will be applied for this application:

All the changes applied through MDS Seeded customisations, will be visible in the log:

Good point - there is no need to restart main application, after MDS Seeded customisation changes were applied. Here you can see, original application with changes as per above:

If applied changes should be removed, this could be simply removed from MDS Seeded customisation XML file and re-applied. Main advantage of this approach - no need to store XML files with MDS Seeded customisations inside original project, we could keep them outside.

Hide All Search Operators for ADF View Criteria Item

During this week work, I received interesting question from ADF developer - how to hide all operators for specific ADF View Criteria item. There is an option to go and hide different operators one by one, following API guide for JboCompOper. You could follow this approach, but there is one smarter way - to hide all operators at once. See below - how.

Sample application is available for download - ADFQueryNoOperatorsApp.zip. This application implements View Criteria with several items to search for (FirstName, LastName and Salary):

Search operators are configured to be displayed in both Basic and Advanced modes:

This is how it looks on UI - ADF Query with standard list of search operators:

I would like to hide all search operators for FirstName and LastName attributes. This is pretty easy - what we need to do, is to open VO attribute and define CompOper tag. If you want to hide all search operators, simply provide "*" for Name and Oper properties. Set "-2" for ToDo property (this is OPER_TODO_CLEAR, meaning - remove all operators).  Name and Oper are required properties, but we could provide "*" sign, indicating it will be applied for all. Repeat the same for FirstName and LastName attributes:

Search operators will be hidden now for FirstName and LastName items in both modes - Basic:

And Advanced mode:

Sounds pretty easy, isn't it? You should remember a trick with "*" symbol.

ADF 11g PS6 Table Pagination and Displaying Selected Row Issue - Solution

While ago, I had a blog post about new feature in ADF 11g PS6 (11.1.1.7) - table pagination support. There is an issue, when we want to open specific row and display it automatically in the table - required table page for the selected row is not opened correctly. However, blog reader suggested a fix, received from Oracle Support. Blog reader was kind enough, to post a comment with suggested fix, you can read it here - JDev/ADF sample - ADF 11g PS6 Table Pagination and Displaying Selected Row Issue. I decided to test this fix myself and provide updated sample application. The fix is to use range start from the iterator and set to it for the first property of the table with pagination. Actually, this fix does the job, but not completely perfect. Current row is displayed, only if Range Size for the iterator is set to 25, probably there is some hard coding somewhere. Ok, but at least it works.

Download sample application - TablePaginationApp_v5.zip. This application contains two fragments, second fragment with the table is opened from the first - where current row is selected. In the first fragment, we call setPropertyListener for navigation button and save required information in pageFlowScope (to be used in the second fragment):

This information - range start, once we move current row in the row set, range start is also changed. We are going to use range start, to set first property for the table - in such way, we could force table to display required page with selected row:

Here you can see, how table first property is set - we are using range start saved in pageFlowScope in the first fragment. This would force ADF table with pagination to display required page of rows:

Let's see how this works. Select a row belonging to the first range (I have configured Range Size to be 25):

Table with pagination is loaded and selected row is displayed in the first page, this is correct:

Navigate to some row from the second range (this should be after first 25 rows):

Selected row is displayed in the second page, as expected. There is one small issue - selected row is displayed at the bottom, while it should be somewhere in the middle. Well, this is another issue related to ADF table pagination:

If you navigate back to the first page and then again navigate to the second page - selected row will be displayed correctly in the middle:

In my opinion, ADF table pagination is not yet very tested and stable feature. Perhaps we should wait for improvements in the next release, until using it in the complex scenarios.