Thursday, June 7, 2012

CRUD Use Case Implementation and ADF Query Search

One of the most important topics to research when rewriting legacy Oracle Forms system into Oracle ADF 11g/Oracle Fusion is how to implement CRUD operations in smooth way for the new system. CRUD operations play one of the central roles for Oracle Forms and Oracle ADF applications, because most of the applications implemented with Oracle Forms and Oracle ADF are designed to process/maintain data. CRUD implementation should be efficient for usability and performance. With today post I will provide updated sample application based on old post from 2009 - Oracle ADF Tuning: Preventing SQL Query Execution on Page Load. I have improved handling of Rollback operation when executing ADF Query search operation.

Download sample application -

Generally I would recommend such flow for CRUD implementation:

1. CRUD screen must have ADF Query block and CRUD operations
2. CRUD screen must be opened with applied executeEmptyRowSet() - to prevent default SQL query
3. On page load CRUD screen must insert new blank record
4. ADF Query must rollback pending changes

Updated sample application implements all 4 recommendations, it handles 4th recommendation in much better way comparing to original from 2009. With my next posts, I will provide more improvements for CRUD use case implementation - stay tuned. Below is description for current updated sample app.

As per original sample app, users would get standard ADF validation errors when hitting Search and having form with newly inserted blank row. This is logical - ADF Query would try to refresh new row and it will fail because of validation errors:

Let's look how we can fix it and implement better user experience for CRUD/Search. There are number of steps to follow how to refactor original application - I will describe now step by step.

Firstly select ADF Query component in Structure window:

With ADF Query component selected - clear ResultComponentId property value. This will prevent declarative refresh of CRUD form and will not trigger early validation for required fields - its what we need for this specific use case:

Refresh for results block will be triggered later - programmatically.

Next make sure we are using CreateInsert operation to insert blank empty row, and now Create operation (original app is using Create). With CreateInsert applied, there will be no validation errors after executing ADF Query:

Once user invokes search operation from ADF Query, we want to intercept this action and call Rollback (to clear inserted blank row). We can do this through ADF Query Listener, it can be overriden in the backing bean:

Here we have overriden ADF Query Listener code in backing bean. It checks if there are any pending transactions available and executes Rollback operation only in the case of dirty data present. Next it calls original ADF Query operation to perform Search action and triggers programmatic refresh for the results block:

It works perfectly now. CRUD screen is opened with blank row - optimized for fast page load:

User press Search button - Rollback operation is called from custom ADF Query Listener, blank row is removed and Search result is displayed:


thegame said...

Great información Andrejus. Greats from South América !

Grandiosa información Andrejus, Saludos desde Sud América !

thegame said...

Hi again Andrejus, I have a question.

¿ How I can capture the value from a field placed in a af:Query?

By example : in image 1 ( Query Panel ) I want capture the values from FirtsName and LastName Fields introduced by the user.

How I can do it that ?

Thanks in advance.

Anonymous said...

Hi Andrejus. As usual making easier ADF implementation.

I have the same question: How I can capture the value from a field placed in a af:Query?

Thanks in advance.

Andrej Baranovskij said...

Yes, I guess this post will help:


Anonymous said...

Thanks Andrejus, it's a good example, but I' wondering if the search also have the Add field choice, should all those fields be based on bind variables too?

Thanks in advance.

Andrej Baranovskij said...

Regarding Add Fields - in most of the cases customers prefer to disable this function, it sometimes just confuses users.

Add Fields is dynamic - it will generate system bind variable by ADF for you.


matuche said...

Hi Andrejus, you provied a great information about this tool.

I have a problem with the databindings..

I cant get the ADF values to used it in the process...

Cause my form its binding with an EntityView and i cant bind it with my DataControl Payload object...

Sorry for my bad english,..

This tool its seems amazing but its difficult to understand.

Thx in advance.