Using SQLite with Android to power an AutoComplete textbox

Like my previous Android article make sure you have the Android SDK and Eclipse tools for Android all setup and configured.
I created a new Android Project in Eclipse and called it “UsingSQLite”. I used Android 1.6 as my target but you can use a newer version of Android if you wish.
The first thing to do is create a new file called “list_item.xml” inside of the “layout” folder.

Add the following XML to it:

<?xml version="1.0" encoding="utf-8"?> <TextView xmlns:android="http://schemas.android.com/apk/res/android"     android:layout_width="fill_parent"     android:layout_height="fill_parent"     android:padding="10dp"     android:textSize="16sp"     android:textColor="#000"> </TextView>

That file contains the TextView that will be used to hold the autocomplete data.
Now go into the “main.xml” file and modify it like so:

<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"     android:orientation="horizontal"     android:layout_width="fill_parent"     android:layout_height="wrap_content"     android:padding="5dp">     <TextView         android:layout_width="wrap_content"         android:layout_height="wrap_content"         android:text="@string/COUNTRY_LABEL" />     <AutoCompleteTextView android:id="@+id/autocompleteCountry"         android:layout_width="fill_parent"         android:layout_height="wrap_content"         android:layout_marginLeft="5dp"/> </LinearLayout>

That gives you a label (well a textview really) that says “Country” and the autocomplete textview. You might also notice I’m pulling some data from the string resources so you need to update the “strings.xml” (located in the “values” folder) to this:

<?xml version="1.0" encoding="utf-8"?> <resources>     <string name="hello">Hello World, UsingSQLite!</string>     <string name="app_name">Using SQLite</string>     <string name="COUNTRY_LABEL">Country</string> </resources>

That it for the XML we need to add and edit. We can move onto the class that will do most of the heavy lifting as far as using the SQLite database. Keep in mind I’ve tailored this class to be specific to simply loading and storing the “Countries” data. You can broaden out the code when your comfortable with it and improve on it and make it more generic if you wish. For now all I want is the ability to put in some country names and get them back to populate the autocomplete textview.
Here is the code for “SQLiteCountryAssistant.java”:

package com.giantflyingsaucer;   import android.database.*; import android.database.sqlite.*; import android.content.ContentValues; import android.content.Context; import android.util.Log;   public class SQLiteCountryAssistant extends SQLiteOpenHelper {     private static final String DB_NAME = "usingsqlite.db";     private static final int DB_VERSION_NUMBER = 1;     private static final String DB_TABLE_NAME = "countries";     private static final String DB_COLUMN_1_NAME = "country_name";       private static final String DB_CREATE_SCRIPT = "create table " + DB_TABLE_NAME +                             " (_id integer primary key autoincrement, country_name text not null);)";       private SQLiteDatabase sqliteDBInstance = null;       public SQLiteCountryAssistant(Context context)     {         super(context, DB_NAME, null, DB_VERSION_NUMBER);     }       @Override     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)     {         // TODO: Implement onUpgrade     }       @Override     public void onCreate(SQLiteDatabase sqliteDBInstance)     {         Log.i("onCreate", "Creating the database...");         sqliteDBInstance.execSQL(DB_CREATE_SCRIPT);     }       public void openDB() throws SQLException     {         Log.i("openDB", "Checking sqliteDBInstance...");         if(this.sqliteDBInstance == null)         {             Log.i("openDB", "Creating sqliteDBInstance...");             this.sqliteDBInstance = this.getWritableDatabase();         }     }       public void closeDB()     {         if(this.sqliteDBInstance != null)         {             if(this.sqliteDBInstance.isOpen())                 this.sqliteDBInstance.close();         }     }       public long insertCountry(String countryName)     {         ContentValues contentValues = new ContentValues();         contentValues.put(DB_COLUMN_1_NAME, countryName);         Log.i(this.toString() + " - insertCountry", "Inserting: " + countryName);         return this.sqliteDBInstance.insert(DB_TABLE_NAME, null, contentValues);     }       public boolean removeCountry(String countryName)     {         int result = this.sqliteDBInstance.delete(DB_TABLE_NAME, "country_name='" + countryName + "'", null);           if(result > 0)             return true;         else             return false;     }       public long updateCountry(String oldCountryName, String newCountryName)     {         ContentValues contentValues = new ContentValues();         contentValues.put(DB_COLUMN_1_NAME, newCountryName);         return this.sqliteDBInstance.update(DB_TABLE_NAME, contentValues, "country_name='" + oldCountryName + "'", null);     }       public String[] getAllCountries()     {         Cursor cursor = this.sqliteDBInstance.query(DB_TABLE_NAME, new String[] {DB_COLUMN_1_NAME}, null, null, null, null, null);           if(cursor.getCount() >0)         {             String[] str = new String[cursor.getCount()];             int i = 0;               while (cursor.moveToNext())             {                  str[i] = cursor.getString(cursor.getColumnIndex(DB_COLUMN_1_NAME));                  i++;              }             return str;         }         else         {             return new String[] {};         }     } }

The SQLite database table is very simple. An auto-incrementing id field and a text field to hold the country name. Because I extend SQLiteOpenHelper I also needed to implement the onCreate and onUpgrade methods. The “onCreate” function simply creates the database if it doesn’t exist.

Log.i("onCreate", "Creating the database..."); sqliteDBInstance.execSQL(DB_CREATE_SCRIPT);

Note: The logging will show up in the “DDMS” perspective in Eclipse under the “LogCat” tab.
Then there are the standard methods to accomplish DB work such as “insertCountry”, “removeCountry”, “updateCountry”, and finally “getAllCountries”. Most of this should be pretty straight forward and I’ve tried to make it as easy to understand as possible even being more verbose than required for the sake of clarity.
Finally going back to the main activity class “UsingSQLite.java”:

package com.giantflyingsaucer;   import android.app.Activity; import android.os.Bundle; import android.util.Log; import android.widget.ArrayAdapter; import android.widget.AutoCompleteTextView;   public class UsingSQLite extends Activity {     private SQLiteCountryAssistant sqlliteCountryAssistant;       @Override     public void onCreate(Bundle savedInstanceState)     {         super.onCreate(savedInstanceState);         setContentView(R.layout.main);           final AutoCompleteTextView textView = (AutoCompleteTextView) findViewById(R.id.autocompleteCountry);           sqlliteCountryAssistant = new SQLiteCountryAssistant(UsingSQLite.this);         sqlliteCountryAssistant.openDB();           // Insert a few countries that begin with "C"         sqlliteCountryAssistant.insertCountry("Cambodia");         sqlliteCountryAssistant.insertCountry("Cameroon");         sqlliteCountryAssistant.insertCountry("Canada");         sqlliteCountryAssistant.insertCountry("Cape Verde");         sqlliteCountryAssistant.insertCountry("Cayman Islands");         sqlliteCountryAssistant.insertCountry("Chad");         sqlliteCountryAssistant.insertCountry("Chile");         sqlliteCountryAssistant.insertCountry("China");           //sqlliteCountryAssistant.removeCountry("Chad");         //sqlliteCountryAssistant.updateCountry("Canada", "Costa Rica");           String[] countries = sqlliteCountryAssistant.getAllCountries();           // Print out the values to the log         for(int i = 0; i < countries.length; i++)         {             Log.i(this.toString(), countries[i]);         }           ArrayAdapter<String> adapter = new ArrayAdapter<String>(this, R.layout.list_item, countries);         textView.setAdapter(adapter);     }       public void onDestroy()     {         super.onDestroy();         sqlliteCountryAssistant.close();     } }

We start off creating an instance of the the “SQLiteCountryAssistant” class and then plug some default countries into it. You’ll also notice there is some commented out code you can fiddle with the test out the remove and update functionality. I print the returned countries out to the log and then pass them onto the AutoComplete TextView via an ArrayAdapter.
Before you run the project make sure to go into the run configuration and make the following changes as this will clear the database each time you run the Android emulator.


Now you can run the project and hopefully see something like this if all went well.