Android AutocompleteTextView with Database Data as Suggestions

Our code example for today is about a very useful Android widget called AutocompleteTextView. Specifically, we’ll code an Android AutocompleteTextView with database data as drop-down suggestions.

If you’re not yet familiar with AutocompleteTextView, this widget looks like an EditText but shows completion suggestions automatically while the user is typing.

The list of suggestions is displayed in a drop down menu from which the user can choose an item to replace the content of the edit box with.

I recently played with this widget for my project, but it requires that the auto-complete suggestions must come from SQLite database. I know this could be helpful for your projects too.

In this post, we will cover the following content:

1.0 Android AutocompleteTextView with Database Video Demo
2.0 Android AutoComplete EditText Program Files
3.0 Android AutocompleteTextView with Database Example Codes
3.1 to 3.6 Main Files Description with Codes Explained via Comments

1.0 Android AutocompleteTextView with Database Video Demo

Visualizing our code output is important so here’s a video I shoot for you to show the code’s final output.

2.0 Android AutoComplete EditText Program Files

Our example project for today contains only 5 java files. Click the file names below to read its purpose and see the code inside.

  1. activity_main.xml
  2. MainActivity.java
  3. CustomAutoCompleteView.java
  4. DatabaseHandler.java
  5. CustomAutoCompleteTextChangedListener.java
  6. MyObject.java

3.0 Android AutocompleteTextView with Database Example Codes

3.1 activity_main.xml – see how the AutoCompleteTextView widget was put in the XML layout.

The tag looks like com.example.autocompletetextviewdb.CustomAutoCompleteView because it was referenced to our CustomAutoComputeView.java file.

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

    <TextView
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:text="" />

    <com.example.autocompletetextviewdb.CustomAutoCompleteView
        android:id="@+id/myautocomplete"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:completionThreshold="1" >
    </com.example.autocompletetextviewdb.CustomAutoCompleteView>

</LinearLayout>

3.2 MainActivity.java – this is where we initialize everything and insert sample data to the database.

package com.example.autocompletetextviewdb;

import java.util.List;

import android.os.Bundle;
import android.app.Activity;
import android.widget.ArrayAdapter;

public class MainActivity extends Activity {

    /*
     * Change to type CustomAutoCompleteView instead of AutoCompleteTextView 
     * since we are extending to customize the view and disable filter
     * The same with the XML view, type will be CustomAutoCompleteView
     */
    CustomAutoCompleteView myAutoComplete;
    
    // adapter for auto-complete
    ArrayAdapter<String> myAdapter;
    
    // for database operations
    DatabaseHandler databaseH;
    
    // just to add some initial value
    String[] item = new String[] {"Please search..."};
    
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        
        try{
            
            // instantiate database handler
            databaseH = new DatabaseHandler(MainActivity.this);
            
            // put sample data to database
            insertSampleData();
            
            // autocompletetextview is in activity_main.xml
            myAutoComplete = (CustomAutoCompleteView) findViewById(R.id.myautocomplete);
            
            // add the listener so it will tries to suggest while the user types
            myAutoComplete.addTextChangedListener(new CustomAutoCompleteTextChangedListener(this));
            
            // set our adapter
            myAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_dropdown_item_1line, item);
            myAutoComplete.setAdapter(myAdapter);
        
        } catch (NullPointerException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public void insertSampleData(){
        
        // CREATE
        databaseH.create( new MyObject("January") );
        databaseH.create( new MyObject("February") ); 
        databaseH.create( new MyObject("March") );
        databaseH.create( new MyObject("April") );
        databaseH.create( new MyObject("May") );
        databaseH.create( new MyObject("June") );
        databaseH.create( new MyObject("July") );
        databaseH.create( new MyObject("August") );
        databaseH.create( new MyObject("September") );
        databaseH.create( new MyObject("October") );
        databaseH.create( new MyObject("November") );
        databaseH.create( new MyObject("December") );
        databaseH.create( new MyObject("New Caledonia") ); 
        databaseH.create( new MyObject("New Zealand") );
        databaseH.create( new MyObject("Papua New Guinea") );
        databaseH.create( new MyObject("COFFEE-1K") );
        databaseH.create( new MyObject("coffee raw") );
        databaseH.create( new MyObject("authentic COFFEE") );
        databaseH.create( new MyObject("k12-coffee") );
        databaseH.create( new MyObject("view coffee") );
        databaseH.create( new MyObject("Indian-coffee-two") );
        
    }
    
    // this function is used in CustomAutoCompleteTextChangedListener.java
    public String[] getItemsFromDb(String searchTerm){
        
        // add items on the array dynamically
        List<MyObject> products = databaseH.read(searchTerm);
        int rowCount = products.size();
        
        String[] item = new String[rowCount];
        int x = 0;
        
        for (MyObject record : products) {
            
            item[x] = record.objectName;
            x++;
        }
        
        return item;
    }

}

3.3 CustomAutoCompleteView.java – since we want the AutocompleteTextView to work with a database, we must customize by extending it.

package com.example.autocompletetextviewdb;

import android.content.Context;
import android.util.AttributeSet;
import android.widget.AutoCompleteTextView;

public class CustomAutoCompleteView extends AutoCompleteTextView {

    public CustomAutoCompleteView(Context context) {
        super(context);
        // TODO Auto-generated constructor stub
    }
    
    public CustomAutoCompleteView(Context context, AttributeSet attrs) {
        super(context, attrs);
        // TODO Auto-generated constructor stub
    }

    public CustomAutoCompleteView(Context context, AttributeSet attrs, int defStyle) {
        super(context, attrs, defStyle);
        // TODO Auto-generated constructor stub
    }

    // this is how to disable AutoCompleteTextView filter
    @Override
    protected void performFiltering(final CharSequence text, final int keyCode) {
        String filterText = "";
        super.performFiltering(filterText, keyCode);
    }

    /*
     * after a selection we have to capture the new value and append to the existing text
     */
    @Override
    protected void replaceText(final CharSequence text) {
        super.replaceText(text);
    }

}

3.4 DatabaseHandler.java – as the name suggests, this is where we can create the database, tables, manipulate and query the data.

package com.example.autocompletetextviewdb;

import java.util.ArrayList;
import java.util.List;

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

public class DatabaseHandler extends SQLiteOpenHelper {

    // for our logs
    public static final String TAG = "DatabaseHandler.java";

    // database version
    private static final int DATABASE_VERSION = 4;

    // database name
    protected static final String DATABASE_NAME = "NinjaDatabase2";

    // table details
    public String tableName = "locations";
    public String fieldObjectId = "id";
    public String fieldObjectName = "name";

    // constructor
    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // creating table
    @Override
    public void onCreate(SQLiteDatabase db) {

        String sql = "";

        sql += "CREATE TABLE " + tableName;
        sql += " ( ";
        sql += fieldObjectId + " INTEGER PRIMARY KEY AUTOINCREMENT, ";
        sql += fieldObjectName + " TEXT ";
        sql += " ) ";

        db.execSQL(sql);

    }

    // When upgrading the database, it will drop the current table and recreate.
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        String sql = "DROP TABLE IF EXISTS " + tableName;
        db.execSQL(sql);

        onCreate(db);
    }

    // create new record
    // @param myObj contains details to be added as single row.
    public boolean create(MyObject myObj) {

        boolean createSuccessful = false;

        if(!checkIfExists(myObj.objectName)){
                    
            SQLiteDatabase db = this.getWritableDatabase();
            
            ContentValues values = new ContentValues();
            values.put(fieldObjectName, myObj.objectName);
            createSuccessful = db.insert(tableName, null, values) > 0;
            
            db.close();
            
            if(createSuccessful){ 
                Log.e(TAG, myObj.objectName + " created.");
            }
        }
        
        return createSuccessful;
    }
    
    // check if a record exists so it won't insert the next time you run this code
    public boolean checkIfExists(String objectName){
        
        boolean recordExists = false;
                
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery("SELECT " + fieldObjectId + " FROM " + tableName + " WHERE " + fieldObjectName + " = '" + objectName + "'", null);
        
        if(cursor!=null) {
            
            if(cursor.getCount()>0) {
                recordExists = true;
            }
        }

        cursor.close();
        db.close();
        
        return recordExists;
    }

    // Read records related to the search term
    public List<MyObject> read(String searchTerm) {

        List<MyObject> recordsList = new ArrayList<MyObject>();

        // select query
        String sql = "";
        sql += "SELECT * FROM " + tableName;
        sql += " WHERE " + fieldObjectName + " LIKE '%" + searchTerm + "%'";
        sql += " ORDER BY " + fieldObjectId + " DESC";
        sql += " LIMIT 0,5";

        SQLiteDatabase db = this.getWritableDatabase();

        // execute the query
        Cursor cursor = db.rawQuery(sql, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {

                // int productId = Integer.parseInt(cursor.getString(cursor.getColumnIndex(fieldProductId)));
                String objectName = cursor.getString(cursor.getColumnIndex(fieldObjectName));
                MyObject myObject = new MyObject(objectName);

                // add to list
                recordsList.add(myObject);

            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();

        // return the list of records
        return recordsList;
    }

}

3.5 CustomAutoCompleteTextChangedListener.java – this is where the program requeries the database each time a user types a character on the AutocompleteTextView.

package com.example.autocompletetextviewdb;

import android.content.Context;
import android.text.Editable;
import android.text.TextWatcher;
import android.util.Log;
import android.widget.ArrayAdapter;

public class CustomAutoCompleteTextChangedListener implements TextWatcher{

    public static final String TAG = "CustomAutoCompleteTextChangedListener.java";
    Context context;
    
    public CustomAutoCompleteTextChangedListener(Context context){
        this.context = context;
    }
    
    @Override
    public void afterTextChanged(Editable s) {
        // TODO Auto-generated method stub
        
    }

    @Override
    public void beforeTextChanged(CharSequence s, int start, int count,
            int after) {
        // TODO Auto-generated method stub
        
    }

    @Override
    public void onTextChanged(CharSequence userInput, int start, int before, int count) {

        // if you want to see in the logcat what the user types
        Log.e(TAG, "User input: " + userInput);

        MainActivity mainActivity = ((MainActivity) context);
        
        // query the database based on the user input
        mainActivity.item = mainActivity.getItemsFromDb(userInput.toString());
        
        // update the adapater
        mainActivity.myAdapter.notifyDataSetChanged();
        mainActivity.myAdapter = new ArrayAdapter<String>(mainActivity, android.R.layout.simple_dropdown_item_1line, mainActivity.item);
        mainActivity.myAutoComplete.setAdapter(mainActivity.myAdapter);
        
    }

}

3.6 MyObject.java – this is used for inserting sample data and other operations related to the database.

package com.example.autocompletetextviewdb;

public class MyObject {

    public String objectName;

    // constructor for adding sample data
    public MyObject(String objectName){
        
        this.objectName = objectName;
    }

}

On the next post, we’ll have this code with a custom ArrayAdapter so you can style the drop down that appears, see you! Feel free to drop a comment on this Android AutocompleteTextView with database example.

39 replies
  1. Nagaraj
    Nagaraj says:

    Hi @ninjazhai:disqus Is there a way that my Edittext in my application act as a search engine..i am trying to do something similar to suggestions drop down in google..but data should be taken from my server..please help me..

    Reply
    • ninjazhai
      ninjazhai says:

      Wow, thanks for that comment @mech4rhork, glad to know this code is easy to adapt, sorry for the late reply though..

      Reply
  2. Nicholas
    Nicholas says:

    Hey I was just wondering how i might use the Text that was selected as a string. the myAutocomplete.getText.toString() is causing a crash

    Reply
    • ninjazhai
      ninjazhai says:

      Hey @disqus_lOW1foOcGT:disqus , what exactly are you trying to do? have you tried looking into the onTextChanged() method of CustomAutoCompleteTextChangedListener .java class.

      Reply
  3. Sergio
    Sergio says:

    dear friend. I thank you for the code and the tutorial.
    I am trying to implement your code in my app, but, when I enter the text, with some accents, the letters with accents are not shown.
    Do you have any ideas on what could cause this?
    Thanks again.
    Sérgio

    Reply
    • ninjazhai
      ninjazhai says:

      You are welcome my friend Sergio, I’m glad our code above helped you! Sorry for the late reply, but the good thing is you already found out that the emulator is the cause of that problem!

      Reply
  4. Sergio
    Sergio says:

    Sorry, The problem was caused by the emulator. I tested it on an Android Phone and it works.
    Thanks again, very much for the tutorial and code.

    Sérgio

    Reply
  5. Amol C
    Amol C says:

    Dear friend, thank you for the code. Tested for recordset having more than 90,000 entries, and it works fine. gr8

    Reply
    • ninjazhai
      ninjazhai says:

      Wow, thanks for sharing your findings @amolc:disqus , glad to know it works with that large set of data! :D

      Reply
  6. Dede Mirda
    Dede Mirda says:

    Thank you for your code :)

    But, could u please teach me how to get the result with explaination?
    I mean, when we search “Cofee” then the result is show up, we can touch “Cofee” and get information about it.

    Same as a Dictionary on mobile application.

    Please help me.. i want to build a dictionary for my thesis. And i dnt know how can it be?
    :)

    Dede mirda, Indonesia

    Reply
  7. Stig
    Stig says:

    How about using this kind of retrieving database ?
    Cursor c=db.rawQuery(“SELECT * FROM gasoline ” +
    “WHERE name='”+title+”‘;”, null);

    Reply
  8. Dave Rorjk
    Dave Rorjk says:

    Thanks for the tutorial. I have 20,000 records in table.. The above method works but autocomplete is very slow and sometimes app freezes while deleting text fastly, because it queries the database and repopulates the autocompletetextview list… can u throw some light on it.. Any Idea’s… Many Thanks

    Reply
  9. Manish Shakya
    Manish Shakya says:

    its give error java.lang.ClassNotFoundException: Didn’t find class “com.example.autocompletetextviewdb.CustomAutoCompleteView”

    Reply
  10. Ed Burch
    Ed Burch says:

    This looks like a good solution. However, it does no work with appcompatactivty. Any other suggestions on how to use a AutoCompleteTextView backed by a database for suggestions? The myobject can not be resolved.

    Reply
  11. to0x
    to0x says:

    Thanks for this tutorial. It works for me with 140,000 records in sqlite table, but i had to make a LIMIT clause. Also I used a Fragment in my activity so i had to share vars with parent Activity.

    Reply
  12. Alexandra Page
    Alexandra Page says:

    I’m trying to implement this where there’s no test data being entered as the data is already in a table in the DB, do you have any suggestions on how I can do this/what functions i don’t need to include? I’m new to Java and I’m really struggling…

    Reply
  13. Mike Dalisay
    Mike Dalisay says:

    Hello @alexandra_page:disqus, do you mean you do not want to insert the sample data? That’s ok, you just have to change the values in DatabaseHandler class and fit it to your needs (change database name, fields, etc.).

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *