Default Dynamic Value for ADF Query Search Field

What if you face requirement, which tells to give default value for ADF query field in the search box. This is not just for some static value, but default search parameter must come from another LOV view object. As example in this post, I will implement use case where first value from LOV view object will be set as default value for the search field.

Download sample application - ADFQueryDefaultValueApp.zip. First thing you would consider is to uncheck "Include No Selection Item" for LOV configuration. This works for regular LOV's, but not for those ones included into ADF query search:

See - DepartmentId choice list is rendered without any default value assigned (we would assume, first value should be set by default):

With a bit of coding, we can make it work. Let's include Groovy code expression and reference custom method from VO implementation for Bind Variable default value:

When Bind Variable is assigned with default value, this allows to show this value in the LOV search field included into ADF query.

Default value is calculated dynamically - custom method is responsible for this action. Method gets instance of LOV VO from Application Module, retrieves first row and returns value for LOV key attribute. Make sure to use VO first() method and don't use executeQuery(). Groovy expression for Bind Variable value calls custom method multiple times, executeQuery() would invoke SQL each time when method is called. first() method works differently, it will retrieve already fetched row from cache:

Don't forget to register LOV VO instance in Application Module, otherwise custom method will fail to find it. This instance will be used to retrieve default value for LOV rendered in ADF query search block.

Here we can see how it looks on runtime, default value is set for the choice list automatically:

User can change choice list value and search:

If reset operation is invoked for ADF query - default value is set back again:

Recommendation for Custom Method in ADF BC View Object Implementation Class

When working in different projects, reviewing ADF code and doing quality audits - one of the most often encountered issues is oversized Application Module Implementation class. Let's say there are 20 - 30 use cases based on the same Application Module (which is perfectly fine) and all the custom methods from these use cases are implemented in the single class - Application Module Implementation class. It becomes hard to maintain oversized class and even hard to read. Very often these custom methods are even not reusable across different use cases, it just developer decided to implement them all in the same place. In some cases we must implement custom methods in Application Module Implementation class: method is accessing different VO instances, method is not really related to some specific VO, etc. But in many cases, custom method works only with single VO instance - then it can be moved into VO Implementation class.

Here is the example - instead of letting grow Application Module Implementation class without any limit:

Try to move as much as possible of custom code related to single VO instance into VO Implementation class:

You can expose custom method from VO Implementation class to the client interface and make it available in Data Control:

Custom method from VO Implementation class is available in Data Control under VO instance:

Call Test Method button from ADF UI invokes custom method created in VO Implementation class and referenced through Data Control:

Here you can download sample application for this post - VOImplCustomMethodApp.zip.

Oracle Coherence for ADF BC - Performance Test

As you already know (Oracle Coherence Integration and ADF BC Programmatic VO) - Oracle Coherence can be integrated relatively easy into ADF BC and can help to improve ADF BC runtime performance for cached data. This is true, but it was interesting to do a quick research and check in practice - how much optimization Coherence caching gives for ADF BC application. I was using fairly simple application (ADFCoherenceApp_v2.zip) from my previous Coherence post (mentioned above). This application comes with two exactly same screens - one is based on regular SQL View Object, another is based on programmatic Coherence enabled View Object. I was running identical JMeter stress test for both screens.

Here you can download JMeter stress test scripts:

1. ADF screen with regular SQL View Object - AMTestDefault.jmx

2. ADF screen with programmatic Coherence enabled View Object - AMTestCoherence.jmx

Scripts are configured to run 50 parallel threads, 1850 requests in total. Recorded script actions - use Next, Last, First and Previous buttons - navigate through rowset. Scroll down/up and select rows in af:table component:

I executed 30 runs for each of the screens - default SQL VO and Coherence cache based VO. Average request execution result for both screens: 1.34 seconds for Coherence cache VO and 1.62 seconds for SQL VO. This is logical result, because data is cached with Coherence and retrieved directly from the cache for each of the 50 users. While with SQL based VO, data is retrieved from DB each time new user (50 in total) opens a new session:

This shows - to retrieve data from Coherence cache is faster comparing to retrieving the same data from DB with SQL. Sample application is fairly basic, but the same tendency will be reflected in production enterprise systems. Of course we can't use Coherence for every use case, but for such use cases where fast data access is required - Coherence will be great help.

Below I show you samples from the stress test.

Coherence cache based VO:

SQL based VO:

Lessons learned: runtime performance is much faster when using programmatic VO as a wrapper to access data from Coherence cache, comparing to POJO bean (Data Access Optimization in ADF with Oracle Coherence) acting as a wrapper to access Coherence cache. This seems because ADF BC Data Control runtime performance is faster comparing to POJO Data Control performance - this is on my list to verify with another JMeter stress test.

Oracle Coherence Integration and ADF BC Programmatic VO

Based on blog reader request for my previous post - Data Access Optimization in ADF with Oracle Coherence, I will describe sample application where Oracle Coherence caching functionality is integrated directly into ADF BC. This gives couple of benefits - single Model structure implementation with ADF BC for Coherence based data sources and non Coherence. ADF BC Data Control runtime performance is better comparing to POJO Data Control.

Here you can download sample application - ADFCoherenceApp_v2.zip. Application implements Employee serializable class optimized for Coherence caching - based on Coherence PortableObject:

This class is supported with Coherence POF for optimized serialization:

Sample application contains POF configuration for Employees class:

Main configuration file for Coherence contains reference related to POF configuration:

Coherence cache is constructed only one time per all user sessions from ADF BC AM prepareSession() method. This method is invoked by ADF BC framework automatically, during AM preparation and before any data is shown on UI - perfect place to populate Coherence cache:

Data from Coherence cache is displayed on ADF Faces UI through programmatic ADF BC VO. See another sample for ADF BC programmatic VO here - Fusion Middleware 11g Security - Retrieve Security Groups from ADF 11g. Rows for this programmatic VO are populated from Coherence cache, already initialized in overriden prepareSession() method:

Every attribute for each row is populated from the cache:

Application is started - Coherence cache is populated during AM initialization:

Data is loaded on ADF Faces UI from Coherence cache through programmatic ADF BC VO:

ADF BC - Scrolling To The Last Row

There is last() function in ADF BC - it allows to get last row from the rowset (be aware, it will fetch all rows until the last one - Method - createInstanceFromResultSet() for ADF BC Database Fetch Monitoring). Besides returning last row from the rowset, last() function sets last row to be the current row. Depending on the use case, we may need only to get last row - without setting it to be current. This is possible, I will describe how to scroll to the last row in the rowset (scrolling also fetches all rows in between, same as last() function).

Here is example of last() function call - it retrieves last row:

Get Last button was pressed - last row was retrieved by last() function and in addition on ADF UI table row selection was changed to point to the last row:

If we want to avoid last row becoming current - here we have last row retrieval implementation with scrolling. Scrolling is implemented for rowset range page count. We scroll first to the last range page and then retrieve last row from that page:

Table row selection on ADF UI remains as it was before getting last row data:

Download sample application - ADFScrollingToLastApp_v2.zip.

ADF BC Groovy with Java Imports

ADF BC Groovy expressions support is really great and often saves a lot of time in development of complex functionalities. It comes very handy when retrieving referenced data or calling custom ADF BC methods. There is one more hidden gem offered by ADF BC Groovy - option to import Java packages directly into Groovy and use Java functionality.

Here is the example - I'm importing java.text.SimpleDateFormat directly from Groovy expression and then using Java formatting to retrieve month and year portion from current date. This expression is defined for default value of the transient attribute:

Simple and no headache - it just works:

Here you can download sample application for this post - AdvancedGroovyApp.zip.

Oracle Analytic Functions for Total and Average Calculation in ADF BC

What is great about ADF BC - this framework is very close to the DB and makes data management and analysis operations implementation really easy. There are different ways to implement total and average value calculation. The best way probably is to use Oracle DB Analytic Functions. Using analytic functions minimizes custom Java code, simplifies handling search from Query criteria of from table filter. There is no need to specify GROUP BY in SQL when using analytic functions, this makes easier ADF BC VO implementation.

You are welcome to download sample ADF 11g R2 application - ADFAnalyticFuncApp.zip. Two calculated attributes are added to the Employees VO, both of them are initialized from SQL statement calling analytic function. Here is example for total calculation - check SQL statement for calculated attribute:

Here is for the average:

This is how resulting SQL statement looks for ADF VO with two calculated attributes based on analytic function:

This is all about ADF BC. On ADF UI you need to define in page definition two attributes for calculated fields:

Reference these attributes from table column footer for example:

It works smooth on runtime - search is executed from ADF query, total and average reflect salary data:

If you want, type search criteria into table filter and execute additional filtering - total and average values will be automatically recalculated based on filtered table data: