Google-like Search and LOVs using Oracle Text and ViewCriteria

Do you know the Google Advanced Search page? Very few people have ever seen the page, let alone used it. Now, why is that? It is simply because the "normal" single-field quick-search (that what we normally just call Google) is so incredibly effective.

OK, that's nice, but how can we create a Google-like search experience in ADF? I created a simple sample application that contains Google-like quick-search and LOVs (using Oracle Text). You will find the application below, including a step-by-step guide how to reproduce it.

Example application

To see it working download the example application and follow the instructions in readme.txt to get it running (you need an Oracle Database 10g or 11g (XE is fine) with an HR schema).

It starts with the following page:

On this page I use Oracle Text search for two different scenarios.

Quick search
Use can use the search box to search for arbitrary departments using the name of the department or the name of the manager.

Select the department you want to see or edit and press enter.

LOVs
To change the manager of a department simply start typing in the manager field. You can use a first name, last name,...

username,...

or even phone number or job title or any combination to find the manager.

Just experiment with it for a moment. Note that it will search for the beginning of words, but it will always search for complete numbers. (Which is not very useful in this specific case, but it was a request by one of my clients and I really like it that way. The reason for it was that in their case only power users search for numbers and they already know the complete number, so they don't want to search for number prefixes, for speed. Anyway, you can turn it off if you want.)

Do it yourself

First prepare the database.

• Make sure that the HR user is allowed to use the ctxsys.ctx_ddl package (execute: "grant execute on ctxsys.ctx_ddl to hr" as user sys)
• Create the procedures and functions that return, for a given rowid, the searchable information for that row (see create_search_package.sql in the sample application).
  Test it with a statement like:
  select ot_search.get_emp_search_item(rowid) from employees
• Create the Oracle Text indices (see create_indices.sql). Note that the index is created on one specific column per table, this is a limitation of the database. It doesn't matter which column you choose as long as it is a varchar2 column.
  Test it with:
  select ot_search.get_dep_search_item(rowid) from departments where contains(department_name, 'ad%') > 0

Then create the model project.

• Copy the base classes: Base*.java and  OracleTextSearchSupport.java from the sample application and set them as the default base classes for all ADF BC. I believe that the source code is fairly self explaining (with even a few lines of documentation).
• Create the default ADF BC components for the Employees and Departments tables (i.e. Entities, ViewObjects and ApplicationModule).
• Add an extra attribute to each ViewObject that queries the searchable information as follows: (see  DepartmentSearchString and EmployeeSearchString in the example application)

• Add the custom properties as explained in BaseViewDefImpl.java to the Oracle Text Index attributes in each ViewObject and to the attributes added in the previous step (see DepartmentName, DepartmentSearchString, LastName and EmployeeSearchString in the example).

Now the Oracle Text Quick Search should already work in the ADF BC tester. Just open the DepartmentsVO in the tester, click the binoculars and search for the string "ad" in the field DepartmentSearchString.

Next, we will add support for LOV's.

• Add a DepartmentsQuickSearch and EmployeesQuickSearch ViewCriteria as follows:

• Change the List UI Hints of the ViewObjects as follows:

• Add a ManagerName attribute to the Departments ViewObject (it will provide the LOV, we cannot use the ManagerId attribute for that). Use the database functions to query the searchable information for the employee with id manager_id like this:
  ot_search.get_emp_search_item(ot_search.get_emp_rowid(manager_id))
• Add an LOV to ManagerName:

Now the Oracle Text Quick Search should already work in LOV's in the ADF BC tester. Open The Departments ViewObject in the tester, go to a department without manager and enter "whal" or "ski" into managername and then press tab. Nice, don't you think?

We continue with the ViewController project.

• Create a new page, e.g. DepartmentDetails.
• Drag 'n Drop a Departments Form. It should automatically contain an LOV as in the example application.
• Now we only have to add an <af:autosuggestbehavior suggesteditems="#{bindings.ManagerName.suggestedItems}"> tag to the LOV.

Now the Oracle Text Quick Search should work in the LOV (ManagerName).

• Clone the Departments ViewUsage in the ApplicationModule. Clone the DepartmentsIterator in the page-bindings (pointing to the new VO usage) and give it a meaningful name, e.g. DepartmentsIteratorQuickSearch.
• Now add the QuickSearchBean from the sample application and configure it as a pageFlowScope managed bean in adfc-config.xml with the following properties:
• iteratorBindingName: The name of the IteratorBinding that is used in the form.
• searchAttribute: The name of the attribute that contains the searchable information.
• searchIteratorBindingName: The name of the IteratorBinding-clone.
• Create a subform with a search field as in the sample application.

That's it. The steps above describe how I created the sample application. I hope it provides enough information for other people to integrate this solution in their own project. If any question arises, please post it as a comment below.

Download the sample application   .