How to use ComboBox filtering with large number of items

Vaadin 6 / 7 / 8
1

Hi,
on my current project I’m using PostgreSql database. I have table with names of places and it have 6000+ records.

I try to use ComboBox with JpaContainer, BeanItemContainer, LazyContainer, but performance was terrible beacause ComboBox always load items in memory for filtering.

Then I write AutoSuggest widget to handle filtering. But, while I was writing AutoSuugest widget I realize that I basically doing rewrite of Vaadin ComboBox, so why just not make ComboBox to behave as I wont?

I find great Maddon (
https://github.com/mstahv/maddon
) and there I find simple FilterableListContainer. I decided to use that Container implementation as my starting point. Also, because of nature on Indexed Container i need to modify my sql statements to use PostgreSql window functions (
http://www.postgresql.org/docs/9.3/static/tutorial-window.html
).

So, I make AbstractComboBoxContainer (attached), and then for all ComboBoxes I just extending it. In the end there is only 4 functions to implement.

[code]
public class CdPlacesContainer extends AbstractComboBoxContainer {

public CdPlacesContainer() {
    super(CdLookups.class);
}

@Override
public int size() {
    /*
     * Check for filter and then count
     */
    if (getContainerFilters().isEmpty()) {
        /*
         * SQL to execute
         * 
         * SELECT count(1) FROM cd_places
         */            
    } else {
        /*
         * It has always just ONE filter
         */
        SimpleStringFilter filter = (SimpleStringFilter) getContainerFilters().toArray(new Filter{})[0]

;
String placeName = filter.getFilterString().toLowerCase() + “%”;
/*
* SQL to execute
*
* SELECT count(1) FROM cd_places
* WHERE lower(place_name) like ${placeName}
*/
}
}

@Override
public boolean containsId(Object itemId) {
    WrapDynaBean wrap = new WrapDynaBean(itemId);
    long primaryKey = (long) wrap.get("id");
    
    /*
     * SQL To Execute
     * SELECT * FROM cd_places WHERE id = ${id}
     * 
     * Check if row exists
     */
}

@Override
public int indexOfId(Object itemId) {
    if (itemId == null) {
        return -1;
    }
    long id = ((CdLookups) itemId).getId();
    /*
     * Integer index =
     * To find index, execute SQL
     * 
     *  
     * SELECT c.position 
     * FROM (SELECT t.*, ROW_NUMBER() OVER(ORDER BY t.place_name) AS position
     * FROM cd_places t) c
     * WHERE id = ${id)    
     */
     // PostgreSql row_number starts at 1 !!!
    return (index != null) ? index - 1 : -1;
}


@Override
public List<?> getItemIds(int startIndex, int numberOfItems) {
    int pgStartIndex = startIndex + 1;
    
    /*
     * Check for filter and then count
     */
    if (getContainerFilters().isEmpty()) {
        /*
         * SQL to execute
         * 
         * SELECT c.* 
         * FROM (SELECT t.*, ROW_NUMBER() OVER(ORDER BY t.place_name) AS position
         * FROM cd_places t) c
         * WHERE position >= ${pgStartIndex}             
         * LIMIT ${numberOfItems}
         */            
    } else {
        /*
         * It has always just ONE filter
         */
        SimpleStringFilter filter = (SimpleStringFilter) getContainerFilters().toArray(new Filter{})[0]

;
String placeName = filter.getFilterString().toLowerCase() + “%”;
/*
* SQL to execute
*
* SELECT c.*
* FROM (SELECT t.*, ROW_NUMBER() OVER(ORDER BY t.place_name) AS position
* FROM cd_places t) c
* WHERE lower(c.place_name) like ${placeName}
* AND position >= ${pgStartIndex}
* LIMIT ${numberOfItems}
*/
}

}

}
[/code]And now I can finally use Vaadin ComboBox for autosuggest filtering with good performance.

16454.java (7.48 KB)