Using AutocompleteTextView with SQLite and CursorAdapter

I needed to make an AutocompleteTextView in an app using data from the database.

There is also an example on Android Documentation how to use AutoCompleteTextView. It's working fine until the number of entries is small. But if you have many of them (as it was in my case) everything starts working slower and slower.

I also needed to have my own filtering.

So I was looking for a solution for these problems. And there is one, its name is CursorAdapter.

The idea is simple: you don't need to store all results of your query, you just have a pointer on your data - the cursor to access data must be shown right now.

Our simple layout autocomplete.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent">
    <AutoCompleteTextView
        android:id="@+id/filter"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"/>
</LinearLayout>

I created a small activity class that just shows AutocompleteTextView and doing nothing else. I use as data an existing database table from an app with some stations in it.

public class AutoCompleteActivity extends Activity
{
    private StationDBAdapter mDBAdabter;  // database adapter / helper
    private StationAdapter mCursorAdapter;  // cursor adapter
    private Cursor mItemCursor;    // and the cursor itself
   
    @Override
    protected void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.autocomplete);
       
        initCursorAdapter();
        initItemFilter();       
    }
   
    // initialize the cursor adapter
 private void initCursorAdapter()
    {
        mDBAdabter = new StationDBAdapter(this);
       
        mItemCursor = mDBAdabter.getStationCursor("");       
        startManagingCursor(mItemCursor);
       
        mCursorAdapter = new StationAdapter(getApplicationContext(), mItemCursor);
               
    }
   
    // initialize AutocompleteTextView
 private void initItemFilter()
    {
        AutoCompleteTextView item_filter = (AutoCompleteTextView) findViewById(R.id.filter);
        item_filter.setAdapter(mCursorAdapter);
        item_filter.setThreshold(1);
    }   
}

As you see, I'm using in the activity class above the cursor adapter. And here is my implementation.
You must implement constructor, two abstract method of parent class CursorAdapter, override implementation of runQueryOnBackgroundThread.

public class StationAdapter extends CursorAdapter
{
    private StationDBAdapter dbAdapter = null;

    public StationAdapter(Context context, Cursor c)
    {
        super(context, c);
        dbAdapter = new StationDBAdapter(context);
        dbAdapter.open();
    }
   
    @Override
    public void bindView(View view, Context context, Cursor cursor)
    {
        String item = createItem(cursor);       
        ((TextView) view).setText(item);       
    }
   
    @Override
    public View newView(Context context, Cursor cursor, ViewGroup parent)
    {
        final LayoutInflater inflater = LayoutInflater.from(context);
        final TextView view = (TextView) inflater.inflate(R.layout.list_item, parent, false);
       
        String item = createItem(cursor);
        view.setText(item);
        return view;
    }

    @Override
    public Cursor runQueryOnBackgroundThread(CharSequence constraint)
    {
        Cursor currentCursor = null;
       
        if (getFilterQueryProvider() != null)
        {
            return getFilterQueryProvider().runQuery(constraint);
        }
       
        String args = "";
       
        if (constraint != null)
        {
            args = constraint.toString();       
        }

        currentCursor = dbAdapter.getStationCursor(args);

        return currentCursor;
    }
   
    private String createItem(Cursor cursor)
    {
        String item = cursor.getString(1);       
        return item;
    }
   
    public void close()
    {
        dbAdapter.close();
    }
}

I also want to give you a code fragement from a Database-helper-class with the query method to show an important detail: you have to have a field with identiefier _id in your query results, otherwise you will get an exception from the AbstractCursor-class.

ERROR/AndroidRuntime(1455): Caused by: java.lang.IllegalArgumentException: column '_id' does not exist
ERROR/AndroidRuntime(1455): at android.database.AbstractCursor.getColumnIndexOrThrow(AbstractCursor.java:314)
...

...
public Cursor getStationCursor(String args)
{       
 String sqlQuery = "";
 Cursor result = null;
   
 sqlQuery  = " SELECT _id" + ", stationName ";
 sqlQuery += " FROM stations";
 sqlQuery += " WHERE stationName LIKE '%" + args + "%' ";
 sqlQuery += " ORDER BY stationName";
   
 if (mDB == null)
 {
  open();
 }

 if (mDB!=null)
 {           
  result = mDB.rawQuery(sqlQuery, null);
 }
 return result;
}

...

Hope, it can help you by creating your own AutocompleteTextView