Filtering and sorting the items contained in an SQLContainer is, by design, always performed in the database. In practice this means that whenever the filtering or sorting rules are modified, at least some amount of database communication will take place (the minimum is to fetch the updated row count using the new filtering/sorting rules).
Filtering is performed using the filtering API in Vaadin, which allows for very complex filtering to be easily applied. More information about the filtering API can be found in .
In addition to the filters provided by Vaadin, SQLContainer also
implements the Like
filter as well as the
Between
filter. Both of these map to the equally
named WHERE-operators in SQL. The filters can also be applied on items
that reside in memory, for example, new items that have not yet been
stored in the database or rows that have been loaded and updated, but not
yet stored.
The following is an example of the types of complex filtering that are possible with the new filtering API. We want to find all people named Paul Johnson that are either younger than 18 years or older than 65 years and all Johnsons whose first name starts with the letter "A":
mySQLContainer.addContainerFilter( new Or(new And(new Equal("NAME", "Paul"), new Or(new Less("AGE", 18), new Greater("AGE", 65))), new Like("NAME", "A%"))); mySQLContainer.addContainerFilter( new Equal("LASTNAME", "Johnson"));
This will produce the following WHERE clause:
WHERE (("NAME" = "Paul" AND ("AGE" < 18 OR "AGE" > 65)) OR "NAME" LIKE "A%") AND "LASTNAME" = "Johnson"
Sorting can be performed using standard Vaadin, that is, using the sort
method from the Container.Sortable
interface. The
propertyId
parameter refers to column names.
public void sort(Object[] propertyId, boolean[] ascending)
In addition to the standard method, it is also possible to directly add an
OrderBy
to the container via the
addOrderBy()
method. This enables the developer
to insert sorters one by one without providing the whole array of them at
once.
All sorting rules can be cleared by calling the sort method with null or an empty array as the first argument.