Android: AutoCompleteTextView, SQLite, and Dependent Fields

Update: Subclassing from SimpleCursorAdapter isn’t necessary after all. Please see my follow-up post for a simpler way of using AutoCompleteTextView together with database queries.

I recently integrated an AutoCompleteTextView into a prototype Android application that I’m developing. There isn’t exactly a wealth of documentation on the Web describing AutoCompleteTextView, and what I’ve found didn’t match the scenario I had in mind. Specifically:

  • The choices came from a database, so I needed to use a CursorAdapter; and
  • The AutoCompleteTextView had some dependent fields; changes to the value in the AutoCompleteTextView needed to be reflected in those dependent fields.

For example, let’s use a AutoCompleteTextView for choosing states. When the user chooses a state, I want to update a TextView to show the capital of the chosen state.

AutoCompleteTextView, with no selectionAutoCompleteTextView, selecting Massachusetts

First the form appears with no value in the State Capital field (left); then a state is chosen from the list (right.)

AutoCompleteTextView, updated with capital of Massachusetts.

After a state has been chosen, its capital is also displayed.

Adapting SQLite to AutoCompleteTextView

The documentation for AutoCompleteTextView says that it obtains suggestions from a data adapter. This should be familiar territory for anyone who’s used ListView, which also obtains its list items from a data adapter. Not surprisingly, AutoCompleteTextView uses a ListView internally.

However, ListView and AutoCompleteTextView require different interactions with their adapters. A standard ListView will typically need to fetch the list of items once, and only once. On the other hand, AutoCompleteTextView issues a query for matching choices whenever the user enters new text into the field. (In fact, the AutoCompleteTextView won’t issue a query until the user has typed some text. Hence the CursorAdapter‘s constructor can take null for the Cursor parameter.)

SimpleCursorAdapter wasn’t designed for this kind of usage pattern; in particular, it doesn’t know how you want to query for matching choices. So, you must derive from SimpleCursorAdapter, or from its base class, CursorAdapter. Your subclass must supply two methods that are essentially stubbed out in CursorAdapter:

  • convertToString, which supplies the String value that is entered into the AutoCompleteTextView when a choice is made; and
  • runQueryOnBackgroundThread. In this method, you invoke a query for choices that match the text entered by the user, and return a Cursor that provides the results.

Let’s take a closer look at implementing the derived CursorAdapter class.

public CharSequence convertToString(Cursor cursor)

This method receives the Cursor positioned to a specific row, and returns a String label or representation for that row.

Incidentally, if you derive from SimpleCursorAdapter without overriding convertToString, the choices shown in the drop-down will look fine. However, when an item in the list is chosen, the string entered in the AutoCompleteTextView field will be gibberish like “android.database.sqlite.SQLiteCursor@43e8b260“.

(This shows that SimpleCursorAdapter bypasses convertToString when building the choice list for an AutoCompleteTextView. Instead, it uses the from and to arguments in its constructor to pull the label text directly from the database. It also demonstrates that SimpleCursorAdapter does rely on convertToString when it comes time to update the field. This inconsistency seems like a bug in SimpleCursorAdapter.)

If you derive directly from CursorAdapter without implementing convertToString, then the behavior is consistent: you get the gibberish in the list and in the field value.

Perhaps convertToString should have been declared abstract, since the base class implementation doesn’t seem to be very helpful.

public Cursor runQueryOnBackgroundThread(CharSequence constraint)

Just to clarify, “On Background Thread” means that this method is called in a background thread — it does not mean that you have to spawn that thread within this method.

This method runs a query to get choices that match the input. It’s up to the application to determine what constitutes a match. (In this example, I’m searching for states whose names begin with the given letters.)

The constraint parameter is the input that the user has typed in. It may be null if the user has typed, and then erased, input text. In this case, all results should be returned.

Note that you must not call changeCursor within this method, because changeCursor is not thread-safe. The UI will invoke changeCursor from the main thread after this method returns the new Cursor.

But wait, there’s more!

It might seem… well, simpler, to derive the new Adapter class from SimpleCursorAdapter. However, deriving from CursorAdapter requires only two additional methods to be implemented, newView and bindView, both of which are straightforward. Hence for this example, I’m using CursorAdapter as the base class. Later, I’ll describe the changes needed to use SimpleCursorAdapter as the base.

public View newView(Context context, Cursor cursor, ViewGroup parent)

newView returns a newly constructed View suitable for displaying a single item in the choice list. The usual method for doing this is to use the Inflater, which is given the resource ID of a layout that describes the new View. It’s not necessary to create your own layout file; Android provides a standard layout that’s appropriate for this purpose, identified by android.R.layout.simple_dropdown_item_1line.

Note: Many examples of newView on the web call setText on the view before returning. This unnecessarily duplicates the code in bindView. Nor is it necessary to invoke bindView explicitly: after we return the new view, bindView will be invoked to display the value.

public void bindView(View view, Context context, Cursor cursor)

This is called to display the label for the current row. If newView uses android.R.layout.simple_dropdown_item_1line for layout, then bindView will be called with a TextView as the View argument.

Updating dependent fields

To update dependent fields, start by creating a class that implements OnItemClickListener. (Here, it’s the Adapter class; it could also be your Activity, or some other class.) This interface defines a callback that’s invoked when the user makes a choice. The setOnItemClickListener method in AutoCompleteTextView associates the listener with the field.

The callback method, onItemClick, receives a position parameter that identifies the row number within the result set, starting from 0. For example, if the third state, “Arizona”, is chosen, the value of position will be 2. There’s also an AdapterView<?> parameter, whose purpose is a bit obscure. This is the ListView belonging to the AutoCompleteTextView. (This may be the only situation in which that ListView is directly exposed to the caller.)

We can then get the cursor from the AdapterView<?> parameter, and position it to the correct row, by calling getItemAtPosition. Note that getItemAtPosition returns an Object, so a cast is necessary. (It doesn’t seem to be documented anywhere that listView.getItemAtPosition will return a Cursor in this case.)

In the full example, this logic is seen in the line:

Cursor cursor = (Cursor) listView.getItemAtPosition(position)

The result would probably be the same if we replaced the above line with:

            Cursor cursor = getCursor();
            cursor.moveToPosition(position);

I went with the first approach because: 1) It’s (arguably) more in keeping with the Adapter’s contract. (There doesn’t seem to be any guarantee that changeCursor will be called by the main thread; without that call, getCursor() certainly won’t work.) And 2) it’s simpler, if by “simpler” we mean one less line of code.

The code

Now let’s have a look at the code. Here’s the Activity class, SelectState, with its nested Adapter class, ItemAutoTextAdapter. (The other class needed for this example, AutoCompleteDbAdapter, is available below.)

ZIP files are available for the full example, using either CursorAdapter and SimpleCursorAdapter as the base classes.
package org.oowb.AutoCompleteExample;

import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.os.Bundle;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.AdapterView;
import android.widget.AutoCompleteTextView;
import android.widget.Button;
import android.widget.CursorAdapter;
import android.widget.TextView;

/**
 * A simple Android Activity to demonstrate: 
 * 
 * 1) How to use an AutoCompleteTextView with a CursorAdapter
 * 
 * 2) How to access the cursor row for the user's choice, to obtain 
 *    additional data from that row when an item is selected.
 * 
 * @author Dan Breslau
 * 
 */
public class SelectState extends Activity {
    /**
     * Specializes CursorAdapter to supply choices to a AutoCompleteTextView.
     * Also implements OnItemClickListener to be notified when a choice is made,
     * and uses the choice to update other fields on the Activity form.
     */
    class ItemAutoTextAdapter extends CursorAdapter
            implements android.widget.AdapterView.OnItemClickListener {

        private AutoCompleteDbAdapter mDbHelper;

        /**
         * Constructor. Note that no cursor is needed when we create the
         * adapter. Instead, cursors are created on demand when completions are
         * needed for the field. (see
         * {@link ItemAutoTextAdapter#runQueryOnBackgroundThread(CharSequence)}.)
         * 
         * @param dbHelper
         *            The AutoCompleteDbAdapter in use by the outer class
         *            object.
         */
        public ItemAutoTextAdapter(AutoCompleteDbAdapter dbHelper) {
            // Call the CursorAdapter constructor with a null Cursor.
            super(SelectState.this, null);
            mDbHelper = dbHelper;
        }

        /**
         * Invoked by the AutoCompleteTextView field to get completions for the
         * current input.
         * 
         * NOTE: If this method either throws an exception or returns null, the
         * Filter class that invokes it will log an error with the traceback,
         * but otherwise ignore the problem. No choice list will be displayed.
         * Watch those error logs!
         * 
         * @param constraint
         *            The input entered thus far. The resulting query will
         *            search for states whose name begins with this string.
         * @return A Cursor that is positioned to the first row (if one exists)
         *         and managed by the activity.
         */
        @Override
        public Cursor runQueryOnBackgroundThread(CharSequence constraint) {
            if (getFilterQueryProvider() != null) {
                return getFilterQueryProvider().runQuery(constraint);
            }

            Cursor cursor = mDbHelper.getMatchingStates(
                    (constraint != null ? constraint.toString() : null));

            return cursor;
        }

        /**
         * Called by the AutoCompleteTextView field to get the text that will be
         * entered in the field after a choice has been made.
         * 
         * @param Cursor
         *            The cursor, positioned to a particular row in the list.
         * @return A String representing the row's text value. (Note that this
         *         specializes the base class return value for this method,
         *         which is {@link CharSequence}.)
         */
        @Override
        public String convertToString(Cursor cursor) {
            final int columnIndex = cursor.getColumnIndexOrThrow("state");
            final String str = cursor.getString(columnIndex);
            return str;
        }

        /**
         * Called by the ListView for the AutoCompleteTextView field to display
         * the text for a particular choice in the list.
         * 
         * @param view
         *            The TextView used by the ListView to display a particular
         *            choice.
         * @param context
         *            The context (Activity) to which this form belongs;
         *            equivalent to {@code SelectState.this}.
         * @param cursor
         *            The cursor for the list of choices, positioned to a
         *            particular row.
         */
        @Override
        public void bindView(View view, Context context, Cursor cursor) {
            final String text = convertToString(cursor);
            ((TextView) view).setText(text);
        }

        /**
         * Called by the AutoCompleteTextView field to display the text for a
         * particular choice in the list.
         * 
         * @param context
         *            The context (Activity) to which this form belongs;
         *            equivalent to {@code SelectState.this}.
         * @param cursor
         *            The cursor for the list of choices, positioned to a
         *            particular row.
         * @param parent
         *            The ListView that contains the list of choices.
         * 
         * @return A new View (really, a TextView) to hold a particular choice.
         */
        @Override
        public View newView(Context context, Cursor cursor, ViewGroup parent) {
            final LayoutInflater inflater = LayoutInflater.from(context);
            final View view =
                    inflater.inflate(android.R.layout.simple_dropdown_item_1line,
                            parent, false);
 
           return view;
        }

        /**
         * Called by the AutoCompleteTextView field when a choice has been made
         * by the user.
         * 
         * @param listView
         *            The ListView containing the choices that were displayed to
         *            the user.
         * @param view
         *            The field representing the selected choice
         * @param position
         *            The position of the choice within the list (0-based)
         * @param id
         *            The id of the row that was chosen (as provided by the _id
         *            column in the cursor.)
         */
        @Override
        public void onItemClick(AdapterView<?> listView, View view, int position, long id) {
            // Get the cursor, positioned to the corresponding row in the result set
            Cursor cursor = (Cursor) listView.getItemAtPosition(position);

            // Get the state's capital from this row in the database.
            String capital = cursor.getString(cursor.getColumnIndexOrThrow("capital"));

            // Update the parent class's TextView
            mStateCapitalView.setText(capital);
        }
    }

    private TextView mStateCapitalView;
    private AutoCompleteTextView mStateNameView;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        AutoCompleteDbAdapter dbHelper = new AutoCompleteDbAdapter(this);
        setContentView(R.layout.selectstate);
        Button confirmButton = (Button) findViewById(R.id.confirm);
        confirmButton.setOnClickListener(new View.OnClickListener() {
            public void onClick(View view) {
                setResult(RESULT_OK);
                finish();
            }
        });

        mStateCapitalView = (TextView) findViewById(R.id.state_capital);
        mStateNameView = (AutoCompleteTextView) findViewById(R.id.state_name);

        // Create an ItemAutoTextAdapter for the State Name field,
        // and set it as the OnItemClickListener for that field.
        ItemAutoTextAdapter adapter = this.new ItemAutoTextAdapter(dbHelper);
        mStateNameView.setAdapter(adapter);
        mStateNameView.setOnItemClickListener(adapter);
    }
}


Here’s the AutoCompleteDbAdapter class. It’s less interesting for this example, so the code is hidden by default. Click on the “show source” link to view it.

package org.oowb.AutoCompleteExample;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Simple database access helper class.
 * 
 * @author Dan Breslau
 */
public class AutoCompleteDbAdapter {
    /**
     * List of states and capitals.
     */
    private static final String[][] States = {
            { "Alabama", "Montgomery" },
            { "Alaska", "Juneau" },
            { "Arizona", "Phoenix" },
            { "Arkansas", "Little Rock" },
            { "California", "Sacramento" },
            { "Colorado", "Denver" },
            { "Confusion", "\"C\"" },
            { "Connecticut", "Hartford" },
            { "Delaware", "Dover" },
            { "Florida", "Tallahassee" },
            { "Georgia", "Atlanta" },
            { "Hawaii", "Honolulu" },
            { "Idaho", "Boise" },
            { "Illinois", "Springfield" },
            { "Indiana", "Indianapolis" },
            { "Iowa", "Des Moines" },
            { "Kansas", "Topeka" },
            { "Kentucky", "Frankfort" },
            { "Louisiana", "Baton Rouge" },
            { "Maine", "Augusta" },
            { "Maryland", "Annapolis" },
            { "Massachusetts", "Boston" },
            { "Michigan", "Lansing" },
            { "Minnesota", "St. Paul" },
            { "Mississippi", "Jackson" },
            { "Missouri", "Jefferson City" },
            { "Montana", "Helena" },
            { "Nebraska", "Lincoln" },
            { "Nevada", "Carson City" },
            { "New Hampshire", "Concord" },
            { "New Jersey", "Trenton" },
            { "New Mexico", "Santa Fe" },
            { "New York", "Albany" },
            { "North Carolina", "Raleigh" },
            { "North Dakota", "Bismarck" },
            { "Ohio", "Columbus" },
            { "Oklahoma", "Oklahoma City" },
            { "Oregon", "Salem" },
            { "Pennsylvania", "Harrisburg" },
            { "Rhode Island", "Providence" },
            { "South Carolina", "Columbia" },
            { "South Dakota", "Pierre" },
            { "Tennessee", "Nashville" },
            { "Texas", "Austin" },
            { "Utah", "Salt Lake City" },
            { "Vermont", "Montpelier" },
            { "Virginia", "Richmond" },
            { "Washington", "Olympia" },
            { "West Virginia", "Charleston" },
            { "Wisconsin", "Madison" },
            { "Wyoming", "Cheyenne" }
    };

    private static final String DATABASE_NAME = "capitals";
    private static final String TABLE_NAME = "state";
    private static final int DATABASE_VERSION = 1;

    private class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            final String DATABASE_CREATE_STATES =
                    "create table " + TABLE_NAME
                            + "(_id integer primary key autoincrement"
                            + ", state text not null"
                            + ", capital text not null)";

            db.execSQL(DATABASE_CREATE_STATES);
            populateWithData(db);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int old, int new) {
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
            onCreate(db);
        }
    }

    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    private final Activity mActivity;

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param activity
     *            the Activity that is using the database
     */
    public AutoCompleteDbAdapter(Activity activity) {
        this.mActivity = activity;
        mDbHelper = this.new DatabaseHelper(activity);
        mDb = mDbHelper.getWritableDatabase();
    }

    /**
     * Closes the database.
     */
    public void close() {
        mDbHelper.close();
    }

    /**
     * Return a Cursor that returns all states (and their state capitals) where
     * the state name begins with the given constraint string.
     * 
     * @param constraint
     *            Specifies the first letters of the states to be listed. If
     *            null, all rows are returned.
     * @return Cursor managed and positioned to the first state, if found
     * @throws SQLException
     *             if query fails
     */
    public Cursor getMatchingStates(String constraint) throws SQLException {

        String queryString =
                "SELECT _id, state, capital FROM " + TABLE_NAME;

        if (constraint != null) {
            // Query for any rows where the state name begins with the
            // string specified in constraint.
            //
            // NOTE:
            // If wildcards are to be used in a rawQuery, they must appear
            // in the query parameters, and not in the query string proper.
            // See http://code.google.com/p/android/issues/detail?id=3153
            constraint = constraint.trim() + "%";
            queryString += " WHERE state LIKE ?";
        }
        String params[] = { constraint };

        if (constraint == null) {
            // If no parameters are used in the query,
            // the params arg must be null.
            params = null;
        }
        try {
            Cursor cursor = mDb.rawQuery(queryString, params);
            if (cursor != null) {
                this.mActivity.startManagingCursor(cursor);
                cursor.moveToFirst();
                return cursor;
            }
        }
        catch (SQLException e) {
            Log.e("AutoCompleteDbAdapter", e.toString());
            throw e;
        }

        return null;
    }

    /**
     * Populates the database with data on states and state capitals.
     * 
     * @param db
     *            The database to be populated; must have the appropriate table
     *            ("state") and columns ("state" and "values") already set up.
     */
    private void populateWithData(SQLiteDatabase db) {
        try {
            db.beginTransaction();
            ContentValues values = new ContentValues();
            // Populate the database with the state/capital city
            // pairs found in the States array.
            for (String[] s : States) {
                values.put("state", s[0]);
                values.put("capital", s[1]);

                db.insert(TABLE_NAME, "state", values);
            }
            db.setTransactionSuccessful();
        }
        finally {
            db.endTransaction();
        }
    }
}

Using SimpleCursorAdapter

As mentioned above, changing the Adapter’s base class to SimpleCursorAdapter is fairly simple. Here’s a summary of the changes needed:

  • The SimpleCursorAdapter constructor requires a resource ID for the layout used for items in the list. As before, we are using android.R.layout.simple_dropdown_item_1line for this layout.
  • The constructor for SimpleCursorAdapter also requires parameters named from and to. These are arrays indicating which column(s) in the database hold the relevant data, and which field(s) in the View will display that data.
  • When the SimpleCursorAdapter is used to support an AutoCompleteTextView, only one column/field pair is used. The column, of course, is the column that supplies the names (here, “state”.) But how do we identify the field in the to array? Help comes to us through android.R.id.text1, a resource that is used to identify the first (and only) TextField within the standard item layout (android.R.layout.simple_dropdown_item_1line.)
    This results in the following definitions for the from and to parameters. (I defined these in the outer SelectState class, because an inner class isn’t allowed to have static data.)
        final static int[] to = new int[] { android.R.id.text1 };
        final static String[] from = new String[] { "state" };
    
  • The constructor is changed to call its superclass as follows:
  • super(SelectState.this, 
            android.R.layout.simple_dropdown_item_1line, 
            null, from, to);
    
  • The newView and bindView methods can be removed.
, , ,

9 Comments

Leave a Reply to Nicolas

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>