Query By Example(QBE) is a powerful feature in ADF, using which user can filter results at runtime.
In this article , we'll see few cases on how to use QBE
An entity, view object and Am based on HR schema, Employee is created.
Create a jsf page, and insert a panel collection inside jsp root tag.
Inside the panel collection drop the View Object from data control as a table.
To turn on QBE, select the check the filter option.
Select the table in structure window, go to properties panel, and from the Appearance section, for the property "Filter visible" , turn it to false.
Run the page. You'll observe a QBE(filter) icon, at the top in toolbar.
Click on it and filterable fields will appear over each column. Key in your filter criteria and hit enter. Table results get filtered.
Explore the table properties, and you'll see FilterModel and QueryListener properties set, which are used internally by the framework to filter results.
Open the pagedef for the above page, and you'll notice a Search Region under the executables which Filter Model and QueryListener points too.
Now say, when user visits the page for the first time, he wants to see only those employee whose salary is greater then 6000.
For this, we need to create a new view criteria in the VO(with Salary > 6000). Mark the auto execute property of the view criteria to true.
Open the above Search Region under the executables in page def, and for the Criteria Attribute, set the value to the name of the view criteria created above.
Now , when you run the page, you'll see all employees with salary greater then 6000 automatically filtered, when you land on the page. You can create any such complex criteria, and filter the table results.
Now lets suppose, you want a choice list at the top, with all departments, and you want to give user a choice to filter using that choice list.
We can use QBE feature to implement this pattern.
First lets create a JobsVO( Read only VO, based on a SQL query).
My query looks like this.
SELECT JOB_ID
FROM
( SELECT NULL AS JOB_ID FROM dual
UNION
SELECT DISTINCT Employees.JOB_ID FROM HR.EMPLOYEES Employees
)
ORDER BY Job_id nulls FIRST
I have a null option, so that i can filter for all departments, when user selects a blank value.
Attach this Vo(JobVO) in AM, and then through data control, drop this view in Panel Collection toolbar facet as a choice list.
For the choicelist, set AutoSubmit to true. Give a proper label,set Required property to false.
Under Adavanced section, you'll find ValueChangeListener. Bind the valuechangeListener to a method in managed bean.
Also bind the table to managed bean.
public void onJobChange(ValueChangeEvent valueChangeEvent) {
// Add event code here...
valueChangeEvent.getComponent().processUpdates(FacesContext.getCurrentInstance());
FacesContext fc = FacesContext.getCurrentInstance();
String job =
(String)fc.getApplication().evaluateExpressionGet(fc, "#{bindings.JobId.inputValue}",
Object.class);
RichTable tbl = this.getMyTable();
FilterableQueryDescriptor filterQD =
(FilterableQueryDescriptor)tbl.getFilterModel();
Map filterCriteria = filterQD.getFilterCriteria();
if (filterCriteria == null) {
filterCriteria = new HashMap<String, Object>();
filterQD.setFilterCriteria(filterCriteria);
}
filterCriteria.put("JobId", job);
QueryEvent queryEvent = new QueryEvent(tbl, filterQD);
MethodBinding queryListener = tbl.getQueryListener();
queryListener.invoke(fc, new Object[] { queryEvent });
}
Choice list returns index, if i directly access, the valueChangeEvent.getNewValue()
However we want the real Job and not index. Therefore the easiest way to get is through attribute binding.
For this first i created a Attribute Binding in page def, for the attribute JobId from JobsVO
Now, since ValueChangeListener is fired before model gets a chance to update its value to the new Job selected, therefore as a first line in method above, we are updating the model, with selected value.
Once this is done, we retrieve the selected job though the EL expression . Now we get hold of Query Descriptor , and in the filter criteria, set the appropriate field and its value. After which we fire the Query event.
Table is filtered and returns the rows with selected job only.
Uploaded project is at
http://groups.google.com/group/adfprojects/web/OBEApplication.zip
Run the untitled1.jspx and experiment.
