Using SQLite with Android to power an AutoComplete textbox

Post to Twitter

I haven’t seen a lot of tutorials on using SQLite with Android. Sure there are some out there, but not too many real basic ones. Today I’ll create a simple Android project that uses the built-in SQLite capabilities of Android and then use the data inside the SQLite database to populate values for an autocomplete textbox. Some of the AutoComplete code I’ll use from the Google Android Docs located here.


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.

Post to Twitter

This entry was posted in Android, Java, Open Source. Bookmark the permalink.

12 Responses to Using SQLite with Android to power an AutoComplete textbox

  1. pegasevil says:

    excellent work !!!
    perfect tutorial :-D
    thanks

  2. Cristina says:

    merci!

  3. Lior Ben Oved says:

    Excellent !!! thank you for this useful tutorial

  4. Mur Votema says:

    I saved your page as a bookmark. But now as I read it, I saw one points, which is not good.

    If the country list is big enough, you’ll get problems with performance of your autocompletetextview.

    I guess, another (possibly better) solution would be to take a cursor adapter. I’ll look for that.

    Mur

  5. Dali_ing says:

    Hi,

    Really a very nice tutorial.
    Many thanks and hope you keep giving such tutorials.

    Thanks

  6. Munirasu says:

    Hi,

    It’s amazing man, i hope this will be useful for many people who r trying to learn android…

    Alse I would like thank u a lot… cheer up…

  7. prashanth says:

    Hi,
    I am quite new to android dev…in the above example are the methods removeCountry() and updateCountry() called ?
    Thanks
    Prashanth

  8. Chad Lung says:

    @prashanth,

    Those methods are not called in the example above as they are commented out, but they are there to show how you can modify the data.

    Chad

  9. linisax says:

    I am receiving a NullPointerException at getWritableDatabase() call every time. I am unable to execute this code and not able to figure out why this exception is occuring. Pls advise.

  10. Daevid says:

    Perhaps I’m not seeing the link, but can you post up a complete .zip file with the Eclipse project to these tutorials?

  11. kunal says:

    thanx a lot:D

  12. Mathew says:

    Hi Thanks for this… It is nice to go though. Easy to understand. Thanks for this. May I know how to get Country in a auto complete text box and related states in the next auto complete text box? Is there any sample code for that? If u post it, I will be grateful to you. Thanks in advance.

Comments are closed.