In practice there are front end applications where the entire data set cannot be available at once from the underlying service (e.g. a Search Engine) or it is impractical to wait for the entire data set – for example a big and quite remote database. In these cases if the underlying service supports pagination we could use an OutlineView displaying a moving window of records, and add external pagination controls. Or, depending on the application requirement it can be that only the first 1000 records are to be displayed and the rest ignored.
Whichever the option is we may wish further functionality from the application such as sorting. But to correctly accomplish sorting we need to do this via the underlying service which has access to the whole data set. We could add some extra sorting panel besides the OutlineView and control from there the sorting options and trigger the sorting.
But we like the natural way OutlineView behaves when sorting the local data by the header column and their combinations so we consider the end-user would have a better experience if triggering the service sorting by the use of the OutlineView table header controls. So we decided we want to invoke the service sorting by the use of the OutlineView table header and to display the service response of that sorting in the OutlineView.
For simplicity, assuming a pagination of 10 in a Customer View with 57 records, if you sort ascending by “Last name”, in the first page we wanted to see the first 10 records based on the last name order from the entire set:
while moving to the 6th page we expect to see the last records, including the null ones, without any further action on the table header:
Back to the first page when we switch to the descending sorting we don’t want those 10 records from the first page reversed, because there are far more records in the database – we want to see the last 10 records (with non-null Last name) reversed, i.e. those starting with Chinese and Z:
As OutlineView would be sorting just the local slice of records, using the internal sorting algorithm, several delicate aspects were encountered and solved and briefly that is what we accomplished:
- Intercepting the column header sort actions.
- Getting all the sorting information (column, order) to create the service request.
- Recreating the tree and table with the data received. The data in the current page can change entirely in the complete (external) sorting.
- What happens to local sorting on the above data as sorting algorithm/comparators can differ and we didn’t want to modify the OutlineView code. We need to make local sorting invariable (an Identity function using an empty comparator)…but still null values were shown first in OutlineView – we found a solution for this as well.
- Persistence – what happens after we close and reload the OutlineView component as the sorting properties are persistent.
To exemplify we will be considering an SQL-like syntax for querying the remote service regarding the sorting options, that is via the Service API we will send
ORDER BY COL1 ASC, COL2 DESC, COL3 ASC ...
For example in the snapshot below where we group three columns for sorting,
we want to collect "last_name ASC NULLS LAST, first_name DESC NULLS LAST, company DESC NULLS LAST" as the sorting option where last_name, first_name and company are the column names in the underlying service model.
We then send this as part of the query to the service and repopulate the OutlineView with the response. However as the OutlineView is sorting again, there could be differences such as in the case awareness, nulls sorting, collation and others, hence we are involving an identity (equality) nested comparator and for null values in the child factory we transform them into the empty string.
Now let’s explain by the code how we achieved the steps.
First of all the OutlineView and the columns sortable properties must be set on true.
We installed a MouseListener on the table header by using the OutlineView method getOutline().getTableHeader().addMouseListener(...). Note that the recordsTable is our org.openide.explorer.view.OutlineView.
The listener’s mouseClicked method will be called after the internal Etable MouseListener is doing its job and deciding how each column will be sorted, and we actually simulate what is happening internally getting the sort attributes out. This is how we are resolving item #1 – intercepting the column header sort actions. We are also installing the identity comparator if not already installed.
recordsTable.getOutline().getTableHeader().addMouseListener(new MouseAdapter() {
@Override
public void mouseClicked(MouseEvent e) {
if (e.getButton() == MouseEvent.BUTTON3 || e.getClickCount() != 1) {
// The other buttons are used for sorting; we are not interested.
return;
}
int column = recordsTable.getOutline().columnAtPoint(e.getPoint());
if (column < 0) {
return;
}
// Although we could receive the event for column resizing nothing will change because no column is found as sorted.
TableColumnModel tcm = recordsTable.getOutline().getColumnModel();
if (tcm instanceof ETableColumnModel) {
ETableColumnModel etcm = (ETableColumnModel) tcm;
TableColumn tc = tcm.getColumn(column);
if (tc instanceof ETableColumn) {
ETableColumn etc = (ETableColumn) tc;
if (etc.getNestedComparator() != equalityComparator) {
etc.setNestedComparator(equalityComparator);
}
// In the future we may improve and keep an evidence of only the sorting columns without looping through them each time.
customizeSorting();
// For now it is not worth to do work for keeping the selection as we are multipaged.
recordsTable.getOutline().clearSelection();
}
}
// Here we call the external service doing the sorting and recreating the nodes to providing the new slice.
applyOptions();
}
});
An important method in the code above which is getting the sorting parameters (item #2) is customizeSorting() – it is depicted below. Besides calling it from the MouseListener, we also call it when our Top Component is opened (componentOpened()) together with calling a method to set the custom nested Equality compartor as well, after reading the persistence settings of the OutlineView to ensure that the OutlineView remains sorted in that particular configuration after reopening (item #5). Basically we identify that a column is being clicked for sorting purposes and see its sorting state and rank and add it to the previous sorting states. For now at the first version, we actually loop through all the visible columns and do this for all columns with isSorted() returning true.
protected void customizeSorting() {
// Identify the columns in the external service
String[] columns = getColumnOrderBy();
// Default column used for sorting
int sortedColumnIndex = getDefaultOrderByColumn();
// Default column used for sorting
String sortedOrderType = getDefaultOrderType();
// Member variable used as well by the applyOption() method.
orderBy = "";
boolean sortedFound = false;
try {
int numCols = recordsTable.getOutline().getColumnModel().getColumnCount();
ETableColumn[] orderByCols = new ETableColumn[numCols + 1];
// Hidden columns are not here. Possibly in the future versions of NB.
for (int i = 0; i < numCols; i++) {
ETableColumn etc = (ETableColumn) recordsTable.getOutline().getColumnModel().getColumn(i);
if (etc.isSorted()) {
if (etc.getSortRank() < orderByCols.length) {
// We remap the column to take into account the real ranking.
orderByCols[etc.getSortRank()] = etc;
}
}
}
for (int i = 1; i < orderByCols.length; i++) {
if (orderByCols[i] != null) {
// This means the column is sorted.
ETableColumn etc = orderByCols[i];
sortedColumnIndex = etc.getModelIndex();
if (etc.isAscending()) {
// sortedOrderType = "ASC NULLS FIRST";
sortedOrderType = "ASC NULLS LAST";
} else {
sortedOrderType = "DESC NULLS LAST";
}
if (sortedFound) {
orderBy += ", ";
} else {
sortedFound = true;
}
if (sortedColumnIndex < columns.length) {
// As there could be multiple columns in the real model bounded to the same column we need to add the sortedOrderType to each.
// For the usual case a line as the one commented below suffices
// orderBy += columns[sortedColumnIndex] + " " + sortedOrderType;
orderBy += DatabaseLicenseOperations.createCorrectOrderBy(columns[sortedColumnIndex], sortedOrderType);
}
}
}
if (!sortedFound) {
// Default column (which usually is the node column) is used.
if (sortedColumnIndex < columns.length) {
// As there could be multiple columns in the real model bounded to the same column we need to add the sortedOrderType to each.
// For the usual case a line as the one commented below suffices
//orderBy = columns[sortedColumnIndex] + " " + sortedOrderType;
orderBy = DatabaseLicenseOperations.createCorrectOrderBy(columns[sortedColumnIndex], sortedOrderType);
}
}
} catch (LicenseException ex) {
ex.printStackTrace();
}
}
The columns array keeps a mapping between the table column index and the column(s) in the service/database corresponding to it. Based on this the ORDER BY value is created using the helper DatabaseLicenseOperations.createCorrectOrderBy(...) method to eventually split the multi-field columns and add the orderType to each database column. For example, in a view for a product version we could have a single logical column named Version made up of "major, minor" database level fields. We need to transform it into "major ASC NULLS LAST, minor ASC NULLS LAST".
The internal Table sorting will effectively and visually take place after we return from the MouseListener method. Because we recreate the nodes and properties, using the new records slice in the external sorted order, via the call to the applyOptions() method in the mouse listener method, these new values are used by OutlineView for its sorting and display. In this way we resolve item #3). The only drawback (which we can afford in terms of execution time) is that sorting will also happen in the OutlineView – so there are two sorting operations on the data, the internal one being only for the exact slice of records (not for the entire data set). Because internal sorting order can differ by the external sorting order from the service/database we set the custom nested comparator defined below, resolving item #4:
/**
* Equality Comparator so that the sorting order is kept the same
* as the one from the service/database.
*/
static protected class EqualityComparator<T> implements Comparator<T> {
@Override
public int compare(T o1, T o2) {
// Always o1 = o2 so that the order from the service/DB is used.
return 0;
}
}
/**
* Used for referring the same nested comparator for all the columns.
*/
static protected EqualityComparator<Object> equalityComparator = new EqualityComparator<Object>();
We also set the Update Selection on Sort on false, as there could be rows from the other pages now present and the selection does not make too much sense between pages.
recordsTable.getOutline().setUpdateSelectionOnSort(false);
There was still one more thing to resolve, namely the null values. In case of a null value in a column, the internal sorting is returning from the comparator method before calling the nested comparator when seeing null values. Hence the nulls will always add in the first positions when sorting ascending, respectively in the end when sorting descending and we wanted to be able to list null values at the end for any sorting order, actually controlling this from the external service/database sorting.
If we used the empty string instead null then the nested comparator will be called and through our equality comparator we can keep the order from the database. Hence we actually converted the null value into empty strings in the method getValue() of our PropertySupport.ReadOnly.getValue() implementation that our Properties Sheet set is made of.
This setup can as well be used without pagination or with other data set availability scenarios in case the externalized sorting is necessary just as being the requirement by itself, for example related to i18n aspects such as collation, transliteration. There are also cases when sorting such as by ranking is done via internal attributes in the underlying service and these are not publicly exposed. To obtain the correct sorting order the service sorting operation should be used.