Saturday, November 8, 2008

ADF Query Component and View Criteria Functionality with Custom Query Listener in 11g

Nice new component in ADF 11g is ADF Query - af:query. This component allows to implement Search functionality in your applications with much smaller effort comparing to Search functionality implementation in 10g. In conjunction with View Criteria, ADF Query provides declarative way for Query Criteria forms development. There is no need anymore to have transient attributes for Query Criteria, since we can base it on View Criteria attributes from View object. In this post I will describe how you can implement user-friendly Search functionality in 11g and how to use custom QueryListener for ADF Query.

You can download developed application - ADFSearchApp.zip. This application provides sample page with ADF Query component and results table. Additionally, this application contains custom behavior - when there are pending changes in results table, re-query is not performed and user is forced to commit or rollback existing pending changes.

Good news about ADF Query - it can use List Of Values defined in Model layer. So, if we have List Of Values declared for DepartmentId attribute:


DepartmentId in ADF Query automatically will appear as List Of Values, since this attribute is declared in View Criteria on View object:


Sample page contains two main components - af:query and af:table with ID = resTab. First component implements ADF Query and second - table for results:


If you will select af:query component in Structure view, Property Inspector will show you all properties of af:query. You should pay attention to ResultComponentId property, its Id that points to component where Search results will be shown. Most important thing in my sample application - custom QueryListener processQuery(). I have created it custom, because I want to control query execution and not to execute query when there are pending changes in results table:


However, custom QueryListener declaration is not enough in order to control query execution regarding pending changes. Additionally, you need to set PartialTrigger on results table and point to ADF Query. This is needed in order to auto submit results table and inform Application Module about pending changes before executing query:


Created custom QueryListener is simple - if there are pending changed, query is not invoked, if there is no - standard QueryListener is invoked. Information about pending changes is retrieved from Application Module, using isDirty() function:


On runtime, you will see such screen - Query Criteria area and results table:


DepartmentId List Of Values is shown, you can use it to provide search criteria parameter:


Results table is populated and if user will decide to change for example HireDate column value:


And re-execute query with new query criteria for DepartmentId:


There will be pending changes message shown, since user didnt saved his/her last changes before re-executing query. When user will save pending changes and will query again, it will be successful:

71 comments:

Chandra said...

Hi Andrejus
Thanks for all the blogs. Great help. I have a unique requirement.

I have 2 tables

Table1:

Attribute LOV


Table 2:


Attribute Values LOV


Attrbute LOV displays attributes like City, State, Country, Product etc., This is not a finite list. The list can expand.

Table 2 displays the values in LOVs

If I chose City in Table 1

I get the LOV to display City values

I could add more rows in table 2 and choose more City values

If I add another row in Table 1

and choose a different Attribute say Country

The Table 2 is PPR'ed with LOVs popuplated with Country LOV.

The SQLs for all these attributes

are stored in a different table with Attribute Id

I need to dynamically bind the SQL from this table. How do I get this??

Vikram Kohli said...

Hi Andrejus,

I am facing a problem related to result table in search page. Here is the description:-

http://forums.oracle.com/forums/thread.jspa?messageID=3156298&tstart=0

Please help to solve this.

teddy said...

Hi Andrejus,
The blog was really too good is there any other alternative to perform the search using find and execute buttons.
I am working on JDeveloper studio edition 11.1.1.0.1 , creating an ADF Search form using the steps followed in this link...

http://download.oracle.com/docs/html/B25947_01/web_search_bc003.htm#sthref1796

Search form works in 11g TP3 and TP4 but not in this new release .
can you help me with a solution attaining the same search form with find and execute buttons.

regards

vinitha

Andrejus Baranovskis said...

Hi,

You can check my previous blog post - http://andrejusb.blogspot.com/2008/09/jdeveloper-11g-crud-in-adf-form.html

Regards,
Andrej

Anonymous said...

Hi,
I want to execute a view criteria in addtion to the view criteria used for search every time the search button is clicked. How can i use query listener for the same?

Andrejus Baranovskis said...

Hi,

You can use setApplyViewCriteriaNames(java.lang.String[] names) method to execute multiple criterias at once.

Regards,
Andrej

Anonymous said...

Hi Andrejus,

Thanks for sharing this information, this blog is really useful.

We have a page which uses the Query Panel in the same way you have defined, however anytime we select a given View Criteria, it automatically executes the ViewObjects executeQuery() method.

I want to suppress this functionality and want the query panel to fetch data only when the Search button is pressed.

Do you think it is possible?

Thanks,
Makrand

Andrejus Baranovskis said...

Hi Makrand,

There is an option to disable automatic query execution, its defined on View Object level. Have you set it?

Regards,
Andrejus

Makrand said...

Hi Andrejus,

Thanks a lot for pointing the solution. Infact I looked at one of your another BLOGS

http://andrejusb.blogspot.com/2009/02/improving-performance-in-adf.html

and unchecked the
Query Automatically property of the View Criteria for the ViewObject and got it work.

Thanks,
Makrand

sv said...

I have an job LOV column in the table. I have a department drop down above the table.The lov values should change based on the department value selected in the drop down. I created the view link,but still im getting all the values in the job LOV.

Jesse said...

Hi Andrejus,

How do I allow the user to select the operators for each attribute in the query component? In your view criteria, you are declaring the operator (= or startswith), but I would like to leave this up to the user. Is this possible using a view criteria, or do I have to setup custom QueryModel and Query Descriptor classes?

Regards,
Jesse

Karen said...

What is the alternative to af:query if you want to search an updatable table? The 11g handbook indicates that this creates r/o tables which I verified.

Thanks, Karen

Andrejus Baranovskis said...

Hi,

By default yes, but you can just drag and drop editable table as result table and it will work the same.

Regards,
Andrejus

Anonymous said...

hi,
In one of the application I am working on, there is an af:query implemented.af:query is embedded in a popup(af:dialog) , there are 2 saved searches listed in the dropdown. With the default selected saved search SS1 in dropdown, when clicked on Search button, some results displayed.
Then selected the other savedsearch SS2, where (bindvar)input xxxx is required, on search gave error, input is required. gave a value in xxxx, on search no results. Removed this value in input field xxxx and then selected back the first saved search SS1which was previously defaulted in dropdown on pageload.

Error thrown complaining about input filed xxxx is required when I have navigated to a different savedsearch SS1, even after going to SS2 & keying some value into xxxx , then coming back to SS1 & doing a search will throw the same error and cant continue with any actions further. User has to close the popup ...

on toggling the saved search ,how can I avoid this .. please help


thanks
Sowjanya

Andrejus Baranovskis said...

Hi Sowjanya,

May be you set as required, Bind Variable itself. You should not do this. You should set as required Query item, but not Bind Variable.

Regards,
Andrejus

mexieandco said...

Hi, Andrejus. Your blog entries are great -- keep it up! I'm currently going through the integration and region integration examples.

I have a yes/no field that I created a "button" binding for. It works fine, even in a table. However, I want to have the checkbox in a search form (af:query) connected to that table, and although it does display as a checkbox, it doesn't function properly as a search criterion. Is there a way to make that work?

Thanks for any help you can provide.

Larry

Andrejus Baranovskis said...

Hi,

I will take a look into this.

Regards,
Andrejus

Anonymous said...

Hi,

I have a bunch of pre-defined system queries and each one of those has different fields, but each one also has the same field. I need to get this field/value from whatever query the user is running. How can we do this? For example, I have 5 saved ViewCriterias and on each one there is a field called ProcessType and I need to get the value for that field (e.g. can be "Process" or "Validate") in my backing bean.

I can get as far as finding the search field (ProcessType), but I can't get the value from it...

QueryModel varQueryModel = getQueryHeader().getModel();
List attributeDescriptors = varQueryModel.getAttributes();
Iterator itr = attributeDescriptors.iterator();
while(itr.hasNext()){
AttributeDescriptor attributeDescriptor = (AttributeDescriptor)itr.next();
String attributeName = attributeDescriptor.getName();
if(attributeName.equals("ProcessType")){ get the Value?? }

Any suggestions? Thanks.

harikiran said...

I'm trying to get the values entered in the queryPanel... As mentioned, I can get the handle of the UIXComponent of the field, but not the entered data.. As a work around, I'm iterating over the QueryIterator and picking the field i wanted from first row. (b'coz this fields are results of the query execution. This workaround only works for LOVs/PickLists). But, i'm really looking for a way to add my custom fields (binded to BackBean) into query panel and refer to same for my purpose later in the code. Is it possible?

Thanks,
Harikiran.

Ganesh Parthasarathi said...

Hi, i'm having 2 issues with af:query.

1. The auto refresh of search fields.
Here is what i did...

1. In a view, few attributes are in view criteria, which is used in af:query.
2. The attributes used in the view criteria are defined as LOV accessed via view-link from a separate view to display distinct values in the LOV.
3. The LOV's VO is set to auto refresh.
4. DB user given the permission.
5.The attribute is set as Key attribute.
6. In the GUI, if i click the Reset button of the search panel, then the new records are inserted in the table are visible in the LOV.

*But i want the LOV of the search field to be updated automatically or while accessing the LOV... *
Documentation says how to set the auto refresh. But i didn't get any clue , when and how the lov will be updated.


Issue # 2.

I'm using VO's readXML to update the VO from an xml file. If i enable the auto refresh , then this readXML of the VO throws error "ORA-29983: Unsupported query for Continuous Query Notification "

The acutal query in VO is "SELECT DISTINCT XXXX FROM YYYY"

But the run time query by optimizer is

"SELECT * FROM (SELECT DISTINCT XXXX FROM YYYY) QRSLT WHERE ( ( (XXXX LIKE :vc_temp_1 ) ) )"


Could you please help me to solve this. Thanks .

Ganesh Parthasarathi said...

Could you help me to call Reset operation of af:query , programaticly. Thanks .

Joe D G said...

I have 3 issues with af:query:

1. Refresh button clears af:query fields but not the table that displays the [last set of] results? This seems odd to me (and my user). We think refresh should clear the page.

2. In my popup lov triggered from the af:query the column width of the content displayed is not wide enough. I have tried setting display width on: the lov attribute itself, the attribute that is taking the LOV in the VO (which is part of the viewcriteria) and the viwcriteria bind vars. Nothing seems to fix this. I have been told this is change din next release (11.1.1.4) but until then, any ideas?

3. How do I get access to the ui components and their events in the af:query? My client wants the query to execute when the last query field in af: query is populated (too lazy to press search button or press enter it would seem) and they want the whole form cleared when they click into the first af: query field to start a new query. Refresh seemingly would solve this if it did what wee expected (see #1)

Excellent posts, Andrejus, nicely put together, thank you.

Andrejus Baranovskis said...

Hi,

1) There is no Refresh button, probably you mean Reset. In order results to be cleaned automatically, you need to set Query to be auto-executable.

2) PS3 is coming very soon, not much to wait

3) Yes, such functionality would be cool. For now its not available, but I'm sure Oracle will implement it and enhancement request.

Thanks...

Andrejus

Joe D G said...

1) There is no Refresh button, probably you mean Reset. In order results to be cleaned automatically, you need to set Query to be auto-executable.

Yes, reset, thank you.

Not sure where to set query to auto-executable. if it set it on the view criteria itself, then the page auto queries with no search criteria when it opens (all rows returned).
Reset does clear the search but then the page queries again.

Andrejus Baranovskis said...

Hi,

Reset must clear search block as well.

In order to prevent results population on page load, there can be workarounds. I will post something in near future.

Andrejus

Joe D G said...

exactly, it does clear the search block as well...then an "open search" is executed. If you can post that would help.
thanks you and happy holidays.

Andrejus Baranovskis said...

Hi Joe,

Actually, I already had blog post on this topic - http://andrejusb.blogspot.com/2009/09/programmatical-reset-for-query-results.html

Hope this will help.

Andrejus

Joe D G said...

perfect, thank you!

Andrejus Baranovskis said...

Hi,

Also there is a way to execute your query, when user is typing search parameter. See recent post from Steve Muench - http://blogs.oracle.com/smuenchadf/2010/12/conditionally_auto-executing_a.html

Regards,
Andrejus

Joe D G said...

thanks - Steve's post was prompted by my question! Works great.

Andrejus Baranovskis said...

As always - Oracle is great ! :)

Andrejus

Anonymous said...

Hi Andrejus,

Let's say I have 3 VOs with several LoVs defined on each of them, actually the View Accessors defined on each VO are based on the same set of VOs (e.g. each VO has attributes like RegionId, CountryId, etc) ... What I need in the UI is ONLY one search criteria section targeting 3 different tables (a table per VO).
How could be achieved such a behavior? Do you have any idea?

Thanks,
Livakis

Andrejus Baranovskis said...

Hi,

If you was to use single ViewCriteria, you need to join all required attributes into View Object and then you will be able to define one ViewCriteria for all these attributes.

Andrejus

Anonymous said...

Hi Andrejus,

I guess I was not very clear... so, there are 3 different VOs and I could create 3 different jsp pages with an element 'triggering' an on each of these pages.
The problem is, I need to have only one page (not 3 pages) with 1 'triggering' 3 elements.
I hope now I explained it better.

Thanks,
Livakis

Andrejus Baranovskis said...

You can't merge ViewCriterias from different VO's into one single ViewCriteria.

Andrejus

Sumit Yadav said...

Hi Andrejus,
I have one requirement,wanted your advise on it. It is with respect to Search page built with a view Criteria.
I enter the user_name in the query panel and hit Search and the result panel shows the results.

Now If I gave a user_name which does not have any record in the VO, no data is displayed. At this point I want to capture the user_name from the query panel and on the click of a button ("Add" button I have created for Adding Records if not present) want to navigate to a different Add page .

So how to capture the user_name from the query panel. I am using Jdev 11.1.1.4.0

Thanks
Sumit Yadav

Lisa said...

Hi Andrejus

If you have advice on the following I would most appreciate it.

1. If using an input text with list of values in a query component (too many items to use a choice list, such as thousands of employees), is there a way to display name rather than code once the selection has been made from the list of values? More friendly and would confirm to the user that the correction selection has been made.

2. In an application with one search page (query and results tree) and one edit page (navigate to the edit page from clicking a row in the table on the search page), when the user returns to the search page how can I have the query and table still showing what they were showing previously. I would like the contents of the query component to be unchanged and the results table to still be there and the current row to be unchanged.

thank you for your time
regards
Lisa

Lisa said...

Replying to my own post.

The second item in my query I have resolved. To get the property TrackQueryPerformed to have an effect, and keep the contents of the query when returning to that screen, I had to change my design so that search and edit fragments were part of the same flow rather than two separate flows. Works as I want it to now.

Andrejus Baranovskis said...

Great. I'm now on ODTUG conference, so didnt had time to advice :)

Andrejus

karnnitesh said...

Hi Andrejus,
I am using the saved search functionality and using it to save results layout. It works fine.
However when I navigate to a different task flow from the search ui and cancel hence return on the search ui. The ui is refreshed and the results fetched but the layout is not as per the custom saved search criteria while it is selected in the saved search list.
Any suggestion to retain the saved layout as associated with the custom saved search critera.
Thanks,
Nitesh

Adrian said...

is there a way to enable table filtering without connecting to a database a build a datacontrol ? I want to enable it for a table with some sample data ?

Andrejus Baranovskis said...

You can use static VO probably with dummy data.

Andrejus

Assem Sherif said...

Dear Andrejus,

I tried to change the width of the components in ViewCriteria, but it didn't changed ..
I tried to change the width from its' attribute in ViewObject, also from bindVariable, but No changes happened to it ..

Thanks for your help,

Shankar's said...

Hi Andrejus
Thanks for all the blogs. Great help
i have some requirement in query panel i created query panel with table in query panel by default one of DataBase field as Timestamps while drag from named query its displaying simple date with popup for that filed i need time stamp which mean Date-time-am/pm. if i use date formatted in VO its giving some AD like formats if i wnat to date asking like in b2b reports section with timezone please explain to achive that ...

Anonymous said...

Hello Andrejus, Your help is very much reqd. Pls help.

In our AF:Query component, We have two LOVS. One dependent of the other one values selected. Any ideas how the data can be passed. It wud be simpler to use listener in Non-AFQuery way to set the Bind Variables but not able to find solution in AF:Query.

Your help wud be much appreciated. Thanks.
Sachin

Anonymous said...

Hi Andrejus,
I have a field in the database as clientid which is of the type varchar2. This field is exposed as a field in the query panel in the front end.

The problem is that the client wants the search option "between" (found in the advanced option of the query pane) to work like a number.
That is if the client searches for a value between 5 and 7 , it should not return values such as 51,52,5232 etc . (which is actually the correct behaviour for varchar fields)

But the client does not want the field to be changed to number instead of varchar2 (since he wants to enter alphabet characters in the future as well).

So is there a way to make the search functionality to work like numbers instead of characters for the field clientid.

J dev version Studio Edition Version 11.1.1.4.0
Thanks
Sameera

Yakup Y√úCE said...

Hi Andrejus,
I created a ADF query and result table from LOV.
I wonder how can I listen to query and make a popup if query returns 0 rows.

Thx

Tripuresh said...

Hi Andrejus,

I have a adf table based on list object from my bean which i show in the UI, is there a way to add query panel based on list object from the bean?

Prakash G said...

Hi Andrejus,
Can you please elaborate why we should not perform search when there are pending changes in the table?
Should we not perform QBE also then?
Is it inconvenient or entirely impossible?
I have a requirement in which we need to perform qbe on a table which contains uncommitted data but we are facing problems if we try to save whilst results are filtered.

Thanks.

Andrejus Baranovskis said...

Hi,

Mainly because transaction will remain dirty, and when next time user press Commit - it may cause unexpected results of data being changed which is not currently on the screen.

Technically its possible, but it might be risky to make data dirty.

Andrejus

Prakash said...

Thanks for reply. I will post my problem in case you might be knowing something useful about that.

In our case we need to have QBE enabled on a af:table in which we can insert rows. Suppose I have 3 uncommitted rows and 2 committed rows and now I filter the table using some value then the filter results are correct (and say 1 uncommitted row is being shown) but when i clear the filter the I won't get back all the uncommitted rows in the table. So now I will only have the 2 committed rows and that 1 filtered uncommitted rows back. 2 uncommitted rows are lost. And even if i commit the changes to db only that 1 uncommitted row will be added to the DB. Any idea?

Andrejus Baranovskis said...

Quite interesting - I will test it as well and let you know. Which JDev version are you using? This might be ADF bug...

Andrejus

Prakash G said...

Please do. Appreciate your help.
I am using an internal build of Jdev v11.1.1.7.0
Sorry for the late reply.

Prakash G said...

Another thing-
You will have to use this command vo.setQueryMode(ViewObject.QUERY_MODE_SCAN_ENTITY_ROWS | ViewObject.QUERY_MODE_SCAN_DATABASE_TABLES) so that QBE starts working for uncommitted rows at all.

Prakash G said...

Hi,
Were you able to test this?

Thanks

Andrejus Baranovskis said...

No time yet - busy in projects. Will do.

Andrejus

Praksash said...

Thanks.
Its an open issue for us. It got me curious. Please look whenever you have time.

Fazel Farnia said...

Hi,
What happen if I want to use af:Quickquery with customize named view criteria?

abhishek maurya said...

Is there any way of getting the view criteria query that is fired on the click of search button?

Andrejus Baranovskis said...

Yes, you can get it in VO Impl class.

Andrejus

Anonymous said...

Hi Andrejus,

I know this is a very old post but i am stuck in a scenario of af:query.

We have a requirement where we need to display say two LOVs in search criteria corresponding to same attribute. Our database table is something like follow:

ColumnName -> Lookup_Type Lookup_Value
Data -> BusinessUnit BU1
BusinessUnit BU2
ClientCode CC1
ClientCode CC2

So its a simple look up table where we can provide the lookup type and get the values. On top of this table we have created one query based view object "select Lookup_type, Lookup_Value from Lookup_table". Now the requirement is to display each lokup type as drop down with all lookup values in search criteria. Means in search criteria we should have two drop downs , one for businessUnit and one for ClientCode.

What we tried is, we created LOV on Lookup_type attribute, created two bind variables with default values as BusinessUnit and ClientCode and created one view criteria. In view criteria we added two items,like, lookuptype =:pBusinessUnit and lookupType=:pClientCode. On dropping the view criteria on page, we can see two drop downs. But problem is they both are populated with all the values. BusinessUnit drop down is populated with all the values but BU1 as selected value and ClientCode is populated with all the values but CC1 as selected value. However they should be populated with there corresponding values.

Please suggest if we can achieve this in single VO or we need to create separate VO for looup type, then create LOV in all the VO and drop them on the page.

Using JDev11.1.1.6.
Appreciate your help..
Thanks

Anonymous said...

Hi, we have a requirement where we have to handle ADF query component with custom search query in advance mode.
Currently we have the query component in basic mode and the query clauses are constructed dynamically based on the entered search criteria values. We have a requirement recently to enable advanced mode for this adf query component. Could you please help in understanding how could we handle custom query in advance mode especially when a view criteria item is added multiple times.

Richard Burk said...

Andrejus, thanks for all of your blog postings! They have been very handy since I started with ADF programming Dec 2013. There is so much more to learn.

My question: How do I default a mandatory query date field to the current day's date?

I have been searching for a solution for a few days without any solutions that have actually worked. I am hoping for a declarative solution versus a programmatic solution. It seems intuitive to me that I should be able to provide SYSDATE as the value, but the only choices of operand provided by the Edit View Criteria window of the query is Literal Value and Bind Variable value. What I'd like to choose for Operand is SQL and value of SYSDATE, but that is not available.

Any ideas? I will take a programmatic solution, but declarative is preferred.

Thanks in advance for your time!

Richard Burk

Andrejus Baranovskis said...

Hi,

Try to check this post: http://andrejusb.blogspot.com/2013/03/default-dynamic-value-for-adf-query.html

Regards,
Andrejus

Richard Burk said...

Thanks Andrejus, that got me further, but I am still running into the problem of converting apparently a groovy date to a oracle.jbo.domain.Timestamp. I have been trying different data types and different date formats to make it work, but nothing yet. It seems like date conversion is difficult when it comes to basically prepopulating an af:query search field of date/timestamp data type.

Richard Burk said...

Andrejus, I found a work-around. Even though I find oracle.jbo.domain.Timestamp in the hierarchy search, the xml file still shows java.sql.Timestamp which then causes the type conversion problem. So I updated the xml file from java.sql.Timestamp to oracle.jbo.domain.Timestamp and it worked. Now if I play around with it, I have to keep setting it back as jDeveloper wants to keep changing it on me. But once I set it and leave it alone it is fine.

Unfortunately using the bind variable gave me a side effect that I wasn't expecting which was that the bind variable then showed up as one of the search parameters. In fact the variable that I wanted to pre-populate while it was populated with the Bind variable's value, the search form was using the value set on the bind variable rather than the value beside the date field that I had prepopulated. So I had to abandon this solution. Fortunately I have found another possible solution using CompOper directly in the xml code. I found it here: http://docs.oracle.com/cd/E25178_01/web.1111/b31974/web_search_bc.htm in section 27.3.3 How to Create Custom Operators or Remove Standard Operators. So far I have yet to get it to work, but I know that I am close. The db date field generates code or not depending upon what I place in the TransientExpression. Now I am testing converting SYSDATE to a timestamp using the TO_TIMESTAMP function to match the code that was generated during the last run when the code wrapped the REQUEST_DATE field with TO_TIMESTAMP and wrapped that in TO_CHAR when I just passed SYSDATE. Hopefully I will find a solution before I leave from work in about 3hrs.

Thanks again!

Richard

Tokando said...

hi ...
can i replace the department id like 60 with the matching name like Administration after the search ....
Thank you

vimal said...

Hi,
Nice post....
I have a requirement to add cancel button at bottom for consistency.
Is it possible to customize view criteria in search form so that we can have search,reset and one more cancel button.

please let me know.

Atif Dar said...

Hi,

Is it possible to put more than one ids in resultcomponentid? i want to have a single af:query and with those search parameters i want to show result in more than one result tables. is it possible?

actually we have 4 different views and all of them have three common fields and i want to built a single search for all four views.

Regards
Atif

Unknown said...

Im having problem with this specific situation everytime i click on the search for my rows it say "no data to display" need help

Pavan Chebolu said...

Hi Andrejus,

It's been a wonderful experience with ADF learning from your blog in this short span of 4 months started learning on ADF. I want the latest code for reset button action where it has to empty the inputs textboxes even after the when we take the cursor out of the fields.

This code is certainly not working when we input some data and come out of the fields and click somewhere on the screen. then the reset functionality is not working.

My requirement is not just bringing the search form. I am using execute with parameters in operations from the table, not queryable attributes. So, I will not get reset button with execute with parameters.

if you can please give the solution this will be a great help.

Best wishes,
Pavan.