Writing custom cursor adapter to use data from a SQLITE database with AutoCompleteTextView

In this post i am going to discuss how to implement a custom cursor adapter that take the data from a sqlite database and populate the  AutoCompleteTextView on the fly.

First let us write our database code which return a cursor. ( Here i only display the related method to return cursor. You have to write a new class extending  "android.database.sqlite.SQLiteOpenHelper" and write the database initialization and open logic in the class. )

1:  public synchronized Cursor getAutoCompleteCursor(String prefix){  
2:         Cursor c = null;  
3:         if(prefix.compareTo("")==0){  
4:              c = myDataBase.query("dict", new String[]{ID_COLUMN_NAME},   
5:                     ID_COLUMN_NAME+" like '' || ? || '%'",   
6:                     new String[]{"*"},   
7:                     null,   
8:                     null,   
9:                     null);  
10:         }else{  
11:              c = myDataBase.query("tableName",   
12:                        new String[]{COLUMN_NAME},   
13:                     ID_COLUMN_NAME+" like '' || ? || '%'",   
14:                     new String[]{prefix},   
15:                     null,   
16:                     null,   
17:                     null);  
18:         }  
19:         if(c == null & c.getCount() <= 0){  
20:              new Exception("Cursor " + (c==null?"null":" fill with wrong data"));  
21:         }  
22:         return c;  
23:    }  

Here "tableName" is the name of the table and "COLUMN_NAME" is the name of the column in table you want to bind with auto complete view.

My database is a very large one so when query for empty string it returns a large number of rows. This will lead to throw an exception in GPU level (creating a view larger than 40000 rows). So i used a conditional check to verify the empty string and at the initial point i query for a string that is not in database to retrieve a empty cursor and on the second letter enter and onward it will query for the prefix and get suggestion list. Another method i used to overcome this memory issue is to use a threshold in  AutoCompleteTextView so it will start to display suggestions only after the number of characters exceed threshold.

Then we write a class extending CursorAdapter class and implementing the filterable interface to include the view generation logic with the listening to key events in background and query the database to obtain suggestion words.

1:  
2:  import android.content.Context;  
3:  import android.database.Cursor;  
4:  import android.view.LayoutInflater;  
5:  import android.view.View;  
6:  import android.view.ViewGroup;  
7:  import android.widget.AutoCompleteTextView;  
8:  import android.widget.CursorAdapter;  
9:  import android.widget.Filterable;  
10:  import android.widget.TextView;  
11:  public class CustomCursorAdaptor extends CursorAdapter implements Filterable {  
12:       private DatabaseHelper dbHelper;  
13:       private LayoutInflater inflater ;  
14:       public CustomCursorAdaptor(Context context, Cursor c) {  
15:            super(context, c);  
16:            this.inflater = (LayoutInflater)context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);  
17:            this.dbHelper = DatabaseHelper.getDatabaseHelper(context);  
18:       }  
19:       @Override  
20:       public void bindView(View view, Context context, Cursor cursor) {            
21:            String keyword = cursor.getString(cursor.getColumnIndex(DatabaseHelper.ID_COLUMN_NAME));  
22:      TextView tv = (TextView) view.findViewById(R.id.sugestionTextView);  
23:      tv.setText(keyword);  
24:       }  
25:       @Override  
26:       public View newView(Context context, Cursor cursor, ViewGroup parent) {  
27:            return inflater.inflate(R.layout.list_item, null);            
28:       }  
29:     @Override
31: public CharSequence convertToString(Cursor cursor) {
32:  
33:  return cursor.getString(cursor.getColumnIndex(DatabaseHelper.DICT_ID_COLUMN_NAME));
34: }
35:       @Override  
36:       public Cursor runQueryOnBackgroundThread(CharSequence constraint) {  
37:            //return super.runQueryOnBackgroundThread(constraint);  
38:            if(getFilterQueryProvider() != null){  
39:                 return getFilterQueryProvider().runQuery(constraint);  
40:            }  
41:            String filter = "";  
42:            if(constraint != null){  
43:                 filter = constraint.toString();  
44:            }  
45:            return dbHelper.getIdCursor(filter);  
46:       }  
47:  }  

Here at the construct i initialize the LayoutInflater and obtain the reference to my DatabaseHelper class which was written by extending the SQLiteOpenHelper and included the getAutoCompleteCursor method.

We have to override two abstract methods in the class which are newView and bindView.

In newView  write our code to use the inflater and populate a view return it to current cursor position. Here i used this method to populate my custom written "list_item" layout and return a view of that layout.

1:  <?xml version="1.0" encoding="utf-8"?>  
2:  <TextView xmlns:android="http://schemas.android.com/apk/res/android"  
3:    android:id="@+id/sugestionTextView"  
4:    android:layout_width="fill_parent"  
5:    android:layout_height="fill_parent"  
6:    android:padding="10dp"  
7:    android:textSize="16sp"  
8:    android:textColor="#000">  
9:  </TextView>  

"list_item.xml" file

In bindView we used to populate an existing view that is being reused with data in our cursor. Here i get a string from the cursor, create a text view by finding a sugestionTextView which is located in my "list_item" xml file and set the text view with the string i obtained from the cursor.

In the method runQueryOnBackgroundThreadhas been used to run a background thread each time a new character been entered to AutoCompleteTextView and obtain a cursor with the prefix text that is in the AutoCompleteTextView at that point.

Finally in the Activity class write the following code inside onCreate method to initialize the new CursorAdapter implementation class and set the adapter to AutoCompleteTextView.

1:  autoText = (AutoCompleteTextView) findViewById(R.id.autoCompleteEnglishWordText);  
2:  autoText.setThreshold(2);  
3:  CustomCursorAdaptor cursurAdaptor = new CustomCursorAdaptor(this, null);  
4:  autoText.setAdapter(cursurAdaptor);