Logo Icon Logo
A Crowd-sourced Cookbook on Writing Great Android® Apps
GitHub logo Twitter logo OReilly Book Cover Art

Feeding AutoCompleteTextView Using an SQLite Database Query

Author: Jonathan Fuerth
Published? true -- FormatLanguage: W

Problem:

Although the Android documentation contains a complete working example of using AutoCompleteTextView with an ArrayAdapter, just substituting a SimpleCursorAdapter into the example does not work.

Solution:

There are two extra twists to using SimpleCursorAdapter instead of ArrayAdapter:

1. You need to tell the adapter which column to use for filling the text view after the user selects a completion.

2. You need to tell the adapter how to requery based on the user's latest input in the text field. Otherwise, it shows all rows returned by the cursor and the list never shrinks to include the items of actual interest.

Discussion:

The following example code would typically be found in the onCreate() method of the Activity that contains the AutoCompleteTextView. It retrieves the AutoCompleteTextView from its activity's layout, creates a SimpleCursorAdapter, configures that SimpleCursorAdapter to work with the AutoCompleteTextView, then assigns the adapter to the view.

The two important differences from the ArrayAdapter example in the Android dev guide are marked "important difference 1" and "important difference 2" in the code example. They are each covered by a short discussion following the example.

final AutoCompleteTextView itemName = (AutoCompleteTextView) findViewById(R.id.item_name_view);

SimpleCursorAdapter itemNameAdapter = new SimpleCursorAdapter(
        this, R.layout.completion_item, itemNameCursor, fromCol, toView);

// important difference 1
itemNameAdapter.setStringConversionColumn(
        itemNameCursor.getColumnIndexOrThrow(GroceryDBAdapter.ITEM_NAME_COL));

// important difference 2
itemNameAdapter.setFilterQueryProvider(new FilterQueryProvider() {

        public Cursor runQuery(CharSequence constraint) {
            String partialItemName = null;
            if (constraint != null) {
                partialItemName = constraint.toString();
            }
            return groceryDb.suggestItemCompletions(partialItemName);
        }
    });

itemName.setAdapter(itemNameAdapter);

Important difference 1: With ArrayAdapter, there is no need to specify how to convert the user's selection into a String. However, SimpleCursorAdapter supports using one column for the text of the suggestion, and a different column for the text that's fed into the text field after the user selects a suggestion. Although the most common case is to use the same text for the suggestion as you get in the text field after picking it, this is not the default. The default is to fill the text view with the toString() representation of your cursor-something like android.database.sqlite.SQLiteCursor@f00f00d0.

Important difference 2: With ArrayAdapter, the system takes care of filtering the alternatives to display only those strings that start with what the user has typed into the text field so far. The SimpleCursorAdapter is more flexible, but again, the default behaviour is not useful. If you fail to write a FilterQueryProvider for your adapter, the AutoCompleteTextView will simply show the initial set of suggestions no matter what the user types. With the FilterQueryProvider, the suggestions work as expected.

TODO: there is a potential cursor leak in the code example. Update with fix and add a discussion item.

jorge9983 2015-07-24 18:02:31.848 can you explain this line please groceryDb.suggestItemCompletions(partialItemName) where i can find the method suggestItemCompletions? thanks.
unkle_george 2011-10-07 11:05:45.805 setStringConversionColumn is not available when using a CursorAdapter instead of a SimpleCursorAdapter. Here's a workaround that also works with SimpleCursorAdapter. CursorAdapter mAdapter; mAdapter = new SimpleCursorAdapter(this, android.R.layout.simple_dropdown_item_1line, null, new String[]{Tags.keywords.name()}, new int[]{android.R.id.text1}, 0) { @Override public String convertToString(Cursor cursor) { return cursor.getString(1); } }; This will close the cursor: mAdapter.setFilterQueryProvider(new FilterQueryProvider() { public Cursor runQuery(CharSequence constraint) { Cursor cursor = mAdapter.getCursor(); if ( cursor != null ) cursor.close(); }}