Wednesday, June 9, 2010

Build Custom Search in ADF 11g

Although ADF 11g provides us with declarative search component for a search functionality, in many cases it still cannot satisfy the business requirements. Here I am presenting a way to build a custom search page without using build-in search component in ADF 11g. This example was built under HR schema to search employees using tables: employees, departments, jobs, locations, countries. Normally, we will catch the user input in managed beans and send the user input back to our model, and explicitly run the executeQuery() against the iterator that binds to the results table.

here I did something differently....
I am using another VO with transient attributes to catch the search criteria user inputs, and send these inputs into the VO iterator which binds the search results table. So when user inputs search criteria, the changes will retain in a VO Row and I will user getter method to catch the changes and send the changes to the VO that I can run executeQuery() to do the search.

To begin, I have wrote the query for the search:
select  EMP.EMPLOYEE_ID,
        EMP.LAST_NAME || ', ' || EMP.FIRST_NAME Name,
        EMP.EMAIL,
        EMP.PHONE_NUMBER,
        EMP.HIRE_DATE,
        JOB.JOB_TITLE,
        EMP.SALARY,
        MGR.FIRST_NAME || ' ' || MGR.LAST_NAME Manager,
        DEPT.DEPARTMENT_NAME,
        LOC.CITY || ' ' || COUNTRY.COUNTRY_NAME ADDRESS
from    employees emp,
        jobs job,
        employees mgr,
        departments dept,
        locations loc,
        countries country
where   EMP.JOB_ID = JOB.JOB_ID
  AND   EMP.MANAGER_ID  = MGR.EMPLOYEE_ID (+)
  AND   EMP.DEPARTMENT_ID  = DEPT.DEPARTMENT_ID (+)
  AND  DEPT.LOCATION_ID = LOC.LOCATION_ID (+)
  AND  LOC.COUNTRY_ID = COUNTRY.COUNTRY_ID (+)
  AND  EMP.EMPLOYEE_ID = :1
  AND  UPPER(EMP.FIRST_NAME) LIKE '%' || UPPER(:2) || '%'
  AND  UPPER(EMP.LAST_NAME) LIKE '%' || UPPER(:3) || '%'
  AND  UPPER(EMP.EMAIL) LIKE '%' || UPPER(:4) || '%'
  AND  EMP.HIRE_DATE = TO_DATE(:5, 'YYYY-MM-DD')
  AND  JOB.JOB_TITLE = :6
  AND  EMP.SALARY BETWEEN :7 AND :8
  AND  UPPER(MGR.FIRST_NAME || ' ' || MGR.LAST_NAME) LIKE '%' || UPPER(:9) || '%'
  AND  DEPT.DEPARTMENT_NAME = :10
  AND  LOC.CITY = :11
  AND  COUNTRY.COUNTRY_NAME = :12
 
The where clause will not included in the VO and will be attached to the query at run time.
First, create our criteriaVO. Choose option "Rows populated programatically, not based on a query".
Then manually create attribute in this VO, note you have to choose option "updatable: always", because we will create an empty row on this VO in order to hold user inputs.



After creating all the attributes in CriteriaVO, we will have to create the ResultVO which is the base VO for the search result data model. Since it's a search query and we are not about to edit the data, we will use read-only VO based on query option.


In the query panel, we will just put the query without where clause, since we will attach the where clause at run time with other search parameters and user inputs.


After creating the two VOs, we will create the initiate Search method and execute search method in Application Module.

The first method we created was the initPage() method. One thing worthy to note, to avoid the resultsVO gets executed when the page renders, we use VO method executeEmptyRowSet() to reduce the rendering time.



We also inserted an empty row in the CriteriaVO because when the page renders, we will have to let the user to input in the search critiera. In the meantime, I have created a RowSetIterator at run time to delete any existing rows left over in the previous user inputs (if user want to do a reset, clear the search inputs and do an another search)

The second method is the executeQuery() method:

There is one important method in executeSearch we used: "setNestedSelectForFullSql(false)" to suppress the inline view for expert mode queries. Please check Steve Muench's article about it.

Then you will need to expose the two methods to the client interface.

Next would be the UI implementation part.

I have created a bounded task flow for the search functionality. The default activity is the initPage() method we defined in the application module, which will avoid ResultsVO automatic query and create an empty row in CriteriaVO. After execution of initPage(), is Employee search page fragment.


In the Search page, we have search criteria, search results table, and a search button and reset button.



The Search button binds with the action method "executeSearch" and reset button binds with "initPage" action method.

Please check the action methods bind to the button in the actionListener: #{bindings.executeSearch.execute} and #{bindings.initPage.execute}.

If let's say, we don't have actionListener binding to #{bindings.initPage.execute}, instead we will have a navigation flow to initPage() action activity in the task flow for the reset button. This will lead to unexpected situations that the reset button will not work (not clearing out the user input in the search criteria). This is because the execution sequences between action and actionListener in the button property inspector.

The final search page looks like this:





We can use "#{bindings.SearchResultsIterator.estimatedRowCount}" to show number of rows that have been returned against the search.

 Downlink for the applications: CustomEmpSearch.zip

11 comments:

oscarmjv said...

Great idea, but i have some doubts ... could you share your example ?

JayJay Zheng said...

The application now can be downloaded. Please share your doubts if you have.

Thanks.

vishal said...

Hi Zheng,

I am very new to ADF and your post seems to be very interesting.
i downloaded your project but i am not able to understand what are the steps to create this.

it would be very helpful if you can share the all the steps to create this project.

vishal said...
This comment has been removed by the author.
vishal said...

when i am creating page,in task-flow-definition.xml, i am not getting binding(in ADF Binding) to bind the init method to execute method.
what am i missing?

vishal said...

please share the steps

JayJay Zheng said...

Vishal, I apologize for the late response. I actually didn't setup the comment notification. After you exposed the client method in AM, refresh the data control and you will see it there. Drag and drop it on the task flow and the binding will automatically get created. Otherwise right click on the method in task flow and choose create page definition. Hope it helps.

Anonymous said...

Hi Jay I have downloaded you app and found it to be very interesting and useful. I am a begineer in ADF/JDeveloper.

I dragged and dropped the initPage() in data control to the bounded task flow and created the search page using SearchCriteriaVO and SearchResultsVO data control but still i am getting following error.

Can you please help on this or provided step by step in creating Model components, view controller components and creating the layout.

From RFC 2068 Hypertext Transfer Protocol -- HTTP/1.1:
10.4.4 403 Forbidden
The server understood the request, but is refusing to fulfill it. Authorization will not help and the request SHOULD NOT be repeated. If the request method was not HEAD and the server wishes to make public why the request has not been fulfilled, it SHOULD describe the reason for the refusal in the entity. This status code is commonly used when the server does not wish to reveal exactly why the request has been refused, or when no other response is applicable.

Thanks and regards,
Ashish Man Baisyet

My URL is below:
http://127.0.0.1:7101/MySearchApp-ViewController-context-root/faces/adf.task-flow;jsessionid=Q0HcNnTQGVwZHjZhNygt9gRmVD2VLMZLWXRB6l4RJP28qPXgQ5rK!-1774859836?adf.tfId=task-flow-definition&adf.tfDoc=/WEB-INF/my-search-task-flow.xml&_afrLoop=2408250551396&_afrWindowMode=0&_afrWindowId=null

kchokho said...

one of the best sharing experienced.Thank you very much.Really useful for me.

Unknown said...

Hi Jay, Thanks for sharing this technique. I have a business requirement for a search form that uses SelectManyChoice option instead of SelectOneChoice. Can you show a modified example of this project say making JobTitle a multi-selected user option?

Thank you in advance.

Husain said...

خیلی مردی