Friday, June 3, 2011

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

4 comments:

linisax said...

thanks for posting this solution. I have a similar problem wherein I want to use data entered by users in every subsequent session of the app to be used for AutoComplete filtering. Is the code for StationDBAdapter class missing in the post?

Mur 'AT' Votema said...

Yes and no. It's just a helper class for working with db. the similar one as in notepad example in android documentation. So it wouldn't be really usefull to include its code.

Joey said...

This is a nice tutorial, thanks for posting it. I would have to echo the previous comment though. If you included that StationDBAdapter, even as a skeleton, the post would be perfect. Coming at sqlite within android somewhat suddenly, so every detail is valuable.

Anyway, appreciate your efforts here. Thanks again!

Mur 'AT' Votema said...

Hi Joey, thank you for your comment. I will add StationDBAdapter structure soon :)

Post a Comment