How to override sort behaviour in TableView

Question

My TableView populates data from database. When user clicks a column header, it sort the data based on it. This feature is out of the box. However, I have too many records to populate them at a time. Say I have 1000 records and the table only shows 500. When I sort it based on a column, it only shorts the 500 already populated record.

I'd like to override the sorting behaviour so when user clicks a column header, it will reload the data from database and provide necessary information for "order by" clause in the query. My problem is, I don't know

1. How to turn off existing sorting behaviour. I can set column's sortable property to false, but I'm afraid it makes the header not clickable.
2. How to attach listener to listen to column header click event. Is tableView.getSortOrder().addListener() the right way?

Answer 1

I got an ugly solution that only solves a half of the problem. I can attach a listener to column header click event, but can't remove default TableView sorting behaviour.

private void initTable() {
    ...
    ...
    //listen to sorting type (ASC/DESC) change
    SortTypeChangeListener sortTypeChangeListener = new SortTypeChangeListener();
    clmName.sortTypeProperty().addListener(sortTypeChangeListener);
    clmGender.sortTypeProperty().addListener(sortTypeChangeListener);
    reload();

    //listen to sortorder change
    tblMember.getSortOrder().addListener(new ListChangeListener<TableColumn<VOMember, ?>>() {
        @Override
        public void onChanged(Change<? extends TableColumn<VOMember, ?>> change) {
            reload();
        }
    });
}

private void reload() {
  /**
     * Get sorted columns and sorting versus (ASC/DESC)
     */
    List<String> lstSortedColumn = new ArrayList<String>();
    List<String> lstSortedType = new ArrayList<String>();
    for (TableColumn<VOMember, ?> tc : tblMember.getSortOrder()) {
        PropertyValueFactory valFactory = (PropertyValueFactory) tc.getCellValueFactory();
        valFactory.getProperty();
        lstSortedColumn.add(valFactory.getProperty());
        lstSortedType.add(tc.getSortType().name());
    }

/**
     * Retrieve data from database. Pass the sorting information
     */        
    List<VOMember> lstMember = controller.retrieve(lstSortedColumn, lstSortedType);
    ObservableList<VOMember> data = FXCollections.observableList(lstMember);
    tblMember.setItems(data);
}

class SortTypeChangeListener implements InvalidationListener {

    @Override
    public void invalidated(Observable o) {
        /**
         * If the column is not in sortOrder list, just ignore.
         * It avoids intermittent duplicate reload() calling
         */
        TableColumn col = (TableColumn) ((SimpleObjectProperty) o).getBean();
        if (!tblMember.getSortOrder().contains(col)) {
            return;
        }

        reload();
    }
}

I'd like to hear your view/comment on this.