Saturday, March 13, 2010

Applying View Criteria from Application Module

Often we need to define and invoke View Object filtering. In ADF 11g we can do this by adding WHERE clause to View Object SQL statement or by defining View Criteria and invoking it later. I prefer second approach, why I should play with SQL statement, if ADF 11g can generate it for me. Okej, when there is View Criteria, it should be invoked. There are different approaches to do this - from declarative to programmatic. Today I will describe my preferred approach, I'm not saying you should use it as well.

Download sample application - AppModuleQuery.zip. This sample implements View Criteria to filter employees based on their department:


Here is the first trick - I have defined Bind Variable setter method to pass variable value and exposed this method through client interface:


This means I will call it later from Controller layer to pass correct Bind Variable value. Its important to say, you should do ExecuteQuery when new Bind Variable is passed, this will ensure it will be correctly reinitialized:


Now most interesting part starts, you can declare View Criteria to be executed automatically, each time when View Object is accessed. You can do this in Application Module - select View Object from Data Model section and click Edit:


Select View Criteria you want to trigger automatically - it will be invoked each time when View Object will be accessed:


Model part is done, now its time to look into Contoller. As you remember, Bind Variable setter method was exposed through client interface, it is present in Data Control:


Drag and drop it into ADF Bounded Task Flow, it is needed to set correct Bind Variable value. In my case it is defined as Default Activity:


On runtime, JobId value is passed as Input Parameter for ADF Bounded Task Flow, where it is used for Bind Variable setter method parameter:


ADF Bounded Task Flow is opened, Bind Variable value is set and View Criteria applied automatically:

18 comments:

Timo said...

Nicely done Andrejus,
the only part I don't agree to is calling executeQuery each time the setter of the bind variable is called.
While this is suitable if you only have one variable it is not wise to do so if your criteria has i.e. three variables. This would cause three executions of the query.
In this case I expose a method in the application module or the VO and call this.

Timo

Andrej Baranovskij said...

Hi Timo,

I agree with you, but its how it works currently.

Andrejus

Marcos Ortega said...

Well done Andrejus....

using default values for binding variables is a nice feature too;

you could use groovy expression like...

adf.userSession.userData.idCenarioComp

Andrej Baranovskij said...

Hi Marcus,

Yes, agree. I have blog post about this case as well - http://andrejusb.blogspot.com/2010/01/storingaccessing-objects-in-adf-bc.html

Thanks,
Andrejus

Anonymous said...

Andrejus,

I am getting a class cast exception message when I am using my view criteria for "ADF Query panel with table".

Here are the steps I followed:

1. created a view criteria on a view object
2. all binding parameters are set to be optional
3. all binding parameters are set as Strings
3. Dragged the view criteria as a query component (ADF Query panel with Query table) on to the design layout

and the error when I clicked the Search button is:

javax.el.ELException: java.lang.ClassCastException: oracle.jbo.common.ViewCriteriaImpl cannot be cast to oracle.jbo.ViewCriteriaRow

Anonymous said...

What the heck is ensureVariableManager????

I can't find it at all in the zip file. :(

Andrej Baranovskij said...

Anonymous, where you see the heck? :) You can search for ensureVariableManager, its easy.

Look into EmployeesViewImpl.java available in Model project.

Andrejus

Anonymous said...

Ok, I didn't realize that ensureVariableManager is an inherited method!

My bad.

Thanks for the reply!

Andrej Baranovskij said...

Happens, no worry ;)

Andrejus

Joe The Jet said...

Ok, so this is pretty cool. How would you recommend doing this when calling it from a autoSuggestBehavior tag?

Thanks,

Jet

Apple Grew said...

I guess using the groovy method is better. We have done it similarly but sometimes ADF directly refreshes the iterator bound to the VO, without giving our code a chance to set bind values. Resulting in error as that bind variable is required.d

Andrej Baranovskij said...

Hi,

This is different. Here we just enforce VO instance to be filtered always.

Andrejus

Dinesh said...

Hi Andrejus,

Could you please tell me how to pass a list as bind variable.

If suppose in the below query I need to give the three values from Bind Variable.

SELECT * FROM emp where empno in (7369,7566,7782);

Please give your suggestions.

Thanks,
Dinesh

Anonymous said...

Hi Andrejus,

Based on your post i have created two named criterias for my view. One is for Search By Id and the other is for Search By Date. I want the View query to be executed based on: first named criteria if Id is not null and second named criteria if Date is not null but, not both at a time. So, how and where are we deciding which named criteria to be executed?

Anonymous said...

Hi Andrejus,
I am trying a similar task, Could you please guide.
In a jspx page I have two sections, left hand side showing Department ADF Form with Navigation , and on other side I want the Employee table to be displayed with department id filtered based on the left hand side
Department Navigation..

Anonymous said...

Hi Andrejus...I am rudra
I tried this scenario in my system, But i'm getting this exception..

oracle.jbo.SQLStmtException: JBO-29114 ADFContext is not setup to process messages for this exception.

please help..

Andrej Baranovskij said...

I can't see why would this not work. Your error message is too generic.

Andrejus

Smith said...

Hello Andrejus,
What is way out, if I don't want to apply the same view criteria everytime I run the VO. Is it possible to declaratively add this specific view criteria only when I need it?