(Note: from 1.1.4pr1 there is a new Filter tag that you may want to use instead of using a filter attribute.
Just like sorting and searching, filtering of data rows is an important, even essential, issue for many database applications.
Filtering a table-selection is the most typical case of filtering. In its simplest case, the Filter is nothing more than a part of the WHERE clause of the SQL SELECT statement used to retrieve the rows.
This kind of filtering is very easy applied in DbForms. The filter criteria may be passed in the filter attribute of the dbform- root tag.
Example 12.3. An example for static filtering
<db:dbform tableName="employee" maxRows="*" followUp="/employees.jsp"
autoUpdate="false"
filter="deptno=101,salary>=30000,name~Peter%">
...
</db: dbform>
The example above restricts the result set to employees of department 101 with salaries higher or equal 30000 and a name like Peter (i.e. Peter Berger, Peter Smith, ...)
The following is a list of the operators that are currently supported by the filter attribute:
Table 12.1. filter operators
| Operator | Meaning | Example |
| = | Equals | aField=value |
| <> | Not Equal | aField<>value |
| < | Smaller then | aField<100 |
| > | Greater then | aField>0 |
| <= | Smaller then or equal | aField<=100 |
| >= | Greater then or equal | aField>=0 |
| ~ | Like | aField~%P% |
By default, filter elements are interpreted by dbforms and ANDed together to build a final where Clause. In certain situations, a logical OR is required between individual elements. This is achieved by using the pipe(|) symbol:
aField<10,aField=1,|aField=3
will render the following where Clause
WHERE aField<10 AND aField=1 OR aField=3
Dynamic filtering is possible as well, as the filter-attribute gets dynamically evaluated by the jsp container.
Example 12.4. Example for dynamic filtering
<%
// scriptlet determines filter
StringBuffer myFilter = new StringBuffer();
myFilter.append("aField=");
myFilter.append(request.getParameter("SomeVal"));
%>
<db:dbform tableName="employee" maxRows="*"
followUp="/employees.jsp" autoUpdate="false"
filter="<%= myFilter.toString() %>"
>
...
</db: dbform>
There are many different reasons why filtering of data is needed. However, in many cases the filtering should not only be applied to data-selection, but also to data manipulating operations such as inserts, updates or deletes.
Scenario:
For example, we want to define a filter to guarantee that certain users may only manipulate data belonging to certain departments: User A may only edit data for employees of department 100 and 200.
The detailed constraints should be defined as follows:
it is forbidden for the user to insert any new employees with a dept.no. other than 100 or 200.
it is forbidden for the user to update employee data where the dept_no field has an assigned value other then 100 or 200.
it is forbidden for the user to delete employees where the dept_no field has a value other then 100 or 200.
We could easily carry out these rules by applying the methods and patterns we learned in the previous chapters:
set a filter attribute on the <dbform> element
use a select box or radio buttons for the dept_no field => so that the user has to choose from a set of valid values and has no chance to enter invalid values.
In many cases, this would be sufficient, but to make things really secure and to prevent malicious users from emulating form request using telnet, etc. we need to define these rules in a more secure way.
This approach is described in the chapter on Application Hookups.
It is also possible to use a
whereClause within a dbform tag for situations where you need more complexity than filtering can handle.
However, the whereClause attribute
can not be used with
orderBy or
filter attributes.
They will be ignored. Order by should be used within the whereClause instead:
See the Tag Library from more info.