Bug in ADF 11g R2 View Criteria Bind Variable Timestamp Type Configuration

I was blogging last year about new type configuration in ADF 11g R2 and issue with BigDecimal - ADF BC 11g R2 - Java Extended For Oracle Data Type Map. There is another bug related to Java Extended For Oracle Data Type Map - reproduced with Bind Variable defined from View Criteria for Timestamp type (JDeveloper 11g R2 generates different Timestamp in EO and for Bind Variable in VO).

Download fixed sample application - SearchDateApp.zip. This sample contains View Criteria with Date field (based on Timestamp type generated in EO). View Criteria renders out of the box ADF Query:

You can search by Date:

After the first search, click on calendar icon again. You will get error: java.lang.IllegalArgumentException: Cannot convert date of type java.sql.Timestamp to class oracle.jbo.domain.Timestamp:

Let's understand why this error happens - it can be very frustrating to developers and disappointing as well. Stay cool, try to debug it - compare generated Timestamp type in EO and VO Bind Variable.

Open EO and select HireDate attribute:

Open source XML and check generated type for HireDate. You will see that JDeveloper generates automatically oracle.jbo.domain.Timestamp:

Check what type was auto-generated for Bind Variable in VO (should be also Timestamp):

Open source XML - you will see java.sql.Timestamp was generated for Bind Variable:

There is no way to change this through the wizard, only possible through source XML view. This is what we call - JDeveloper magic, powered by artificial intelligence :) Obviously on runtime we get error, because generated types doesn't match.

Set Bind Variable to match type auto-generated in EO for date attribute - oracle.jbo.domain.Timestamp:

After this change, ADF Query will work without issues (at least related to described problem).

How to Search in Range with Single ADF Query Field

ADF Query supports out of the box between search operator. This operator is applied for single VO attribute, ADF renders two field (from and to) on UI to enter search range. We may have different use case - to display on UI only one query field and in the background to search by two different attributes (minimum and maximum range value). I will describe how to implement such use case, sample application is available for download as usual.

Here is use case for sample application, you can download - SearchInRangeApp.zip:

Jobs table contains two attributes - Minimum and Maximum Salary. We would like to provide to the user only one field - Salary. User will type value, execute search and get results where entered Salary value is in between Minimum and Maximum Salary.

ADF UI looks like this - Salary field from ADF Query and results table with both Minimum and Maximum salary values:

When ADF Query is executed, it generates query for both MIN_SALARY and MAX_SALARY:

How to achieve this? There is a trick you can apply for View Criteria definition. View Criteria contains both - MinSalary and MaxSalary attributes. Pay attention - both attributes point to the same Bind Variable, this is needed because we pass only one single value from ADF Query for both attributes:

Both attributes are included into View Criteria, but we need to display only one - set second attribute (MaxSalary for example) to be Never rendered:

MinSalary attribute label is renamed to Salary - to show it in ADF Query. We need to display MinSalary label in results table, for this purpose - duplicate attribute MinSalaryResults with label Minimum Salary is defined:

Of course you could use same attribute MinSalary in both ADF Query and for results table - however, then would need to define label for MinSalary appearing in results table inside ViewController. I prefer to keep ADF BC elements labels inside Model project.

JDeveloper 11g R2 Bug - Wrong Scope for Managed Bean

During recent training event, I have noticed students were struggling with JDeveloper 11g R2 issue - sometimes Action method from the button was throwing exception about invalid Action method name. This happens because there is a bug in JDeveloper 11g R2 IDE, it sets wrong scope for Managed Bean expression call, when Managed Bean is defined in Backing or PageFlow scopes. I decided to document this and describe the reason for Managed Bean error in JDev 11g R2, because is not so obvious for new ADF developers how to identify and resolve this issue. This bug is registered in our Ora-Bug Tracker.

Download fixed sample application - JDev11R2ScopeApp.zip.

This application contains one Managed Bean, this bean is declared in Backing Bean Scope:

Bean is pretty simple, it contains basic Action method:

We want to set this Action method to be called from the button, edit button Action property and set it to point to the method from the bean:

JDeveloper 11g R2, ignores Backing Bean Scope and sets bean reference as simple one - without referencing Backing Bean Scope - this will cause runtime error:

Runtime error - method is not found from the bean:

Workaround is to type backingBeanScope prefix manually:

This bug is reproduced only, when we are editing Action property and assigning existing method from the Managed Bean - not when we create new method by generating Action binding (right click on the button and choose Create Method Binding).

Comparing Number of SQL Executions to get LOV Description With Entity Association Available

This post is logical continuation for my last post - Comparing Number of SQL Executions to get LOV Description Without Entity Association Available, where I was comparing number of SQL executions for different methods without involving EO association. Today I will check how it works when EO association is available. Keep in mind, all what is discussed in previous and this post is related to the case when we define LOV on description field directly, without showing ID value (most common use case, from my experience).

Two methods will be compared:

1. Standard ADF approach to use Association for VO join and retrieve description. Documented long time ago here - Defining the LOV on a Reference Attribute in Oracle ADF 11g. Updated sample application to JDev 11g R2 - LOVByName_v2.zip. (Association VO Join)

2. Transient EO attribute approach with Groovy, based on EO association. Documented previously as well - LOV Description Text with Groovy. Updated sample application to JDev 11g R2 - LOVByNameGroovyAssoc_v2.zip. (Association Groovy EO)

From the first look, standard approach seems to perform better (4 SQL execution vs. 5). However, I'm a bit disappointed with ADF bug in standard approach - it executes some weird SQL statements and can cause real runtime performance issues (read below for detail description, registered in Red Samurai Oracle bugs Wiki - WHERE by Description for LOV). Number of SQL executions to retrieve Job description for test case described below:

As we saw from previous post, inline SQL execution to get LOV description still runs much better - just 1 SQL execution vs minimum 4 execution for LOV description with EO association:

However, you may get easier maintenance when using EO Associations, don't underestimate this. There always must be common sense, choose optimized solution, only where performance is critical.

Both sample applications from this post are tested in the following way - initial page load with LOV component, navigation to the next record, LOV opening, LOV value selection (same as applications without EO Association available):

A. Initial page load with LOV component

B. Navigation to the next record, with different description (Next button)

C. LOV list opening

D. Value selection from LOV list

A. Initial page load with LOV component

Association VO Join - 0 separate SQL for Jobs, description retrieved from join:

Association Groovy EO - 1 SQL for Jobs:

B. Navigation to the next record, with different description (Next button)

Association VO Join - 0 separate SQL for Jobs, description retrieved from join (total = 0):

Association Groovy EO - 1 SQL for Jobs (total = 2):

C. LOV list opening

Association VO Join - 1 SQL for Jobs (total = 1):

Association Groovy EO - 1 SQL for Jobs (total = 3):

D. Value selection from LOV list

Association VO Join - 3 SQL for Jobs (total = 4). LOV value selection executes weird SQL's for this use case:

Association Groovy EO - 2 SQL for Jobs (total 5). Sometimes executes duplicate SQL, searching by key (still can be considered to be better approach comparing to standard, because executes select by key only):

Standard approach (Association VO Join) works well, until you need to select value from LOV. At this point, it executes multiple SQL's with WHERE clause ignoring DB indexes. For some reason, it scans DB records by description you are returning (in addition to the search by key). And even worse, it appends all fields shown in LOV to the WHERE clause through separate SQL.

First it executes in addition to regular SQL - scan by description field:

And then it executes one more SQL with WHERE clause containing all attributes visible in LOV:

All attributes declared as display attributes are included into WHERE clause - this may slow down complex query significantly:

To summarize, this is reproduced with VO based on joined EO:

Description field is added from joined EO, LOV is defined on description field:

Transient EO attribute with Groovy approach (Association Groovy EO), doesn't generate SQL on description, but it invokes more SQL statements with search by key. If standard approach is slow for you, consider this one - create transient EO attribute and reference description from EO Association using Groovy (documented here):

There will be no VO join created:

Display attributes will not be included into WHERE clause:

Comparing Number of SQL Executions to get LOV Description Without Entity Association Available

When speaking about performance, ADF developers should not rely only on the framework and do blind development. Its a must to check form performance proactively using different methods - AM pool disabled, JMeter stress test and finally monitor number of SQL executions. As for the basis for today post I will take bad practice for LOV implementation description - Bad Practice Use Case for LOV Performance Implementation in ADF BC, and will describe how to improve it.

Bad practice use case (AM custom method execution from Groovy VO attribute), you can download from link above. Improved solutions are available for download here - LOVByNameViewAccessor.zip and LOVByNameSQLEO.zip. So, there will be three solutions compared: AM custom method execution from Groovy VO attribute, direct LOV VO View Accessor Find By Key access and finally inline SQL approach to retrieve LOV description value. Keep in mind, all these implementations are not using EO Associations, this is because not always we can use EO Associations and I would like to make you think with this post about performance - so, you will not wonder why ADF system is slow. In my next posts, I will compare number of SQL executions for default LOV with EO Association available.

All three applications are tested in the following way - initial page load with LOV component, navigation to the next record, LOV opening, LOV value selection:

A. Initial page load with LOV component

B. Navigation to the next record, with different description (Next button)

C. LOV list opening

D. Value selection from LOV list

Here we have SQL execution results for all three methods (JOBS table access): Groovy script invocation from VO attribute for the custom AM method fails big time (Groovy value for VO attribute is initialized many times, this is causing to execute many times referenced method to retrieve LOV description):

Number in the graph presents total number of SQL executions (A-D tests).

A. Initial page load with LOV component

Groovy script invocation from VO attribute to call custom AM method - 3 SQL executions:

View Accessor and Find By Key - 1 SQL execution:

Inline SQL approach - no additional SQL for Jobs:

B. Navigation to the next record, with different description (Next button)

Groovy script invocation from VO attribute to call custom AM method - 5 SQL executions (total 8):

View Accessor and Find By Key - 1 SQL executions (total 2):

Inline SQL approach - no additional SQL for Jobs:

C. LOV list opening

Groovy script invocation from VO attribute to call custom AM method - 4 SQL executions (total 12):

View Accessor and Find By Key - 1 SQL executions (total 3):

Inline SQL approach - 1 SQL execution (total 1):

D. Value selection from LOV list

Groovy script invocation from VO attribute to call custom AM method - 9 SQL executions (total 21):

View Accessor and Find By Key - 0 SQL executions (total 3):

Inline SQL approach - 0 SQL execution (total 1):

Finished with statistics.

How to implement View Accessor and inline SQL statement approaches?

In both cases, you must have transient attribute - JobTitle. Just in the case of inline SQL - I prefer to have transient attribute on EO level - is easier to manage custom SQL statements and make them reusable. From maintenance point of view, I would recommend to use View Accessor approach. LOV declared for transient attribute should return both - key and description values:

In case of View Accessor approach, you should go to the getter method for JobTitle and include findByKey method there (search directly through LOV View Accessor RowSet to retrieve LOV description):

In case of inline SQL statement, just include SQL logic for attribute value on EO level and inherit it inside VO, where LOV will be defined: