How to implement autocomplete using the cursor

Question

I have a SQLite database containing 2 tables of 4000+ rows, each of which is used for autocomplete. I saw very simple examples that use an array of strings to provide autocomplete or they use a contact list to do the same. Obviously none of them work in my case. How to use my own SQLite database with my own autocomplete data for autocomplete. Should I create content providers? How? Please give me some examples because I could not find them. I managed to override SQLiteOpenHelper to copy the database from the assets folder to the / data / data / MY_PACKAGE / databases / folder on Android. I created a custom CursorAdapter that uses my custom SQLiteOpenHelper and returns the cursor from runQueryOnBackgroundThread . I get weird errors about some _id column. I added the _id column to my tables. I also don't understand what the Filterable interface does, and when my data is filtered. What methods / classes do I need to override? Thanks.

Answer 1

He works.

You need SQLiteOpenHelper from here . You basically need to copy your database to a specific folder from your resource folder. Then you need a custom CursorAdapter that uses your own SQLiteOpenHelper.

Here is the onCreate method for my activity.

 @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.search); KeywordsCursorAdapter kwadapter = new KeywordsCursorAdapter(this, null); txtKeyword = (AutoCompleteTextView)this.findViewById(R.id.txtKeyword); txtKeyword.setAdapter(kwadapter); txtCity = (AutoCompleteTextView)this.findViewById(R.id.txtCity); btnSearch = (Button)this.findViewById(R.id.btnSearch); btnSearch.setOnClickListener(this); } 

Here is a pointer. You can pass a null value to the cursor when constructing.

 public class KeywordsCursorAdapter extends CursorAdapter { private Context context; public KeywordsCursorAdapter(Context context, Cursor c) { super(context, c); this.context = context; } //I store the autocomplete text view in a layout xml. @Override public View newView(Context context, Cursor cursor, ViewGroup parent) { LayoutInflater inflater = (LayoutInflater)context.getSystemService(Context.LAYOUT_INFLATER_SERVICE); View v = inflater.inflate(R.layout.keyword_autocomplete, null); return v; } @Override public void bindView(View view, Context context, Cursor cursor) { String keyword = cursor.getString(cursor.getColumnIndex("keyword")); TextView tv = (TextView)view.findViewById(R.id.txtAutocomplete); tv.setText(keyword); } //you need to override this to return the string value when //selecting an item from the autocomplete suggestions //just do cursor.getstring(whatevercolumn); @Override public CharSequence convertToString(Cursor cursor) { //return super.convertToString(cursor); String value = ""; switch (type) { case Keywords: value = cursor.getString(DatabaseHelper.KEYWORD_COLUMN); break; case Cities: value = cursor.getString(DatabaseHelper.CITY_COLUMN); break; } return value; } @Override public Cursor runQueryOnBackgroundThread(CharSequence constraint) { //return super.runQueryOnBackgroundThread(constraint); String filter = ""; if (constraint == null) filter = ""; else filter = constraint.toString(); //I have 2 DB-s and the one I use depends on user preference SharedPreferences prefs = PreferenceManager.getDefaultSharedPreferences(context); //String selectedCountryCode = prefs.getString("selectedCountry", "GB"); String selectedCountryCode = prefs.getString(context.getString(R.string.settings_selected_country), "GB"); selectedCountryCode += ""; //Here i have a static SQLiteOpenHelper instance that returns a cursor. Cursor cursor = MyApplication.getDbHelpers().get(selectedCountryCode.toLowerCase()).getKeywordsCursor(filter); return cursor; } } 

Here is the part that returns the cursor: it's just a selection with a similar condition.

 public class DatabaseHelper extends SQLiteOpenHelper { ... public synchronized Cursor getKeywordsCursor (String prefix) { if (database == null) database = this.getReadableDatabase(); String[] columns = {"_id", "keyword"}; String[] args = {prefix}; Cursor cursor; cursor = database.query("keywords", columns, "keyword like '' || ? || '%'", args, null, null, "keyword", "40"); int idcol = cursor.getColumnIndexOrThrow("_id"); int kwcol = cursor.getColumnIndexOrThrow("keyword"); while(cursor.moveToNext()) { int id = cursor.getInt(idcol); String kw = cursor.getString(kwcol); Log.i("keyword", kw); } cursor.moveToPosition(-1); return cursor; } ... } 

You can also create a custom content provider, but in this case it will be just another useless class that you need to override.