[Android] Using the SQLite Database with ListView

The dev guide provides information on how to set up a database for your application. This essentially involves extending SQLiteOpenHelper and overriding its onCreate and onUpgrade methods. Both methods are given a SQLiteDatabase object, which you use to execute the SQL queries to setup the database. In the following example, the a table called names is created with three columns – an id column and first and last columns for storing the first and last names, respectively, and inserts a couple of initial entries.

public class DatabaseHelper extends SQLiteOpenHelper {

	public DatabaseHelper(Context context) {
		super(context, "CursorDemo", null, 1);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE IF NOT EXISTS names ("
				+ BaseColumns._ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, first VARCHAR, last VARCHAR)");
		db.execSQL("INSERT INTO names (first, last) VALUES ('John', 'Doe')");
		db.execSQL("INSERT INTO names (first, last) VALUES ('James', 'Kirk')");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Steps to upgrade the database for the new version ...
	}
}

A note on the dev guide recommends having an id column that has the same name as the BaseColumns._ID constant. The onUpgrade needs to be implemented only in subsequent versions, where the structure of the database has changed. It should perform only the necessary operations to upgrade the database (e.g. create only the new tables, drop ones that are no longer needed, alter tables old tables to match the new one, etc.).

Then, to populate a ListView with data from the database:

  1. Create the instance of your SQLiteOpenHelper and open the database with either getReadableDatabase or getWritableDatabase. You must use getWritableDatabase if you intend to add data to the database.
  2. getReadableDatabase and getWritableDatabase will provide a SQLiteDatabase, similar to the one that was used to create or update the database.
  3. Use one of SQLiteDatabase‘s query methods to obtain a Cursor, which provides access to the result set.
  4. Create a CursorAdapter based on the Cursor.
  5. Set the ListView to use the created CursorAdapter.

The CursorAdapter is an abstract class, requiring the bind and newView to be defined. This allows you to control the view that is used to display the data for an entry in the view. However, in many cases, the SimpleCursorAdapter would be sufficient. This fragment uses the above DatabaseHelper class to provide data for a ListView in a ListActivity.

public class SQLiteDemo extends ListActivity {
	private static final int DIALOG_ID = 100;

	private SQLiteDatabase database;

	private CursorAdapter dataSource;

	private View entryView;

	private EditText firstNameEditor;
	
	private EditText lastNameEditor;
	
    private static final String fields[] = { "first", "last", 
        BaseColumns._ID };

	/** Called when the activity is first created. */
	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		DatabaseHelper helper = new DatabaseHelper(this);
		database = helper.getWritableDatabase();
        Cursor data = database.query("names", fields, 
            null, null, null, null, null);
		
        dataSource = new SimpleCursorAdapter(this, 
            R.layout.row, data, fields,	
            new int[] { R.id.first, R.id.last });

        ...

		setListAdapter(dataSource);
	}

    ...
}

In some cases, the some data will be added, deleted or changed while the view is still displaying. The view will need to be updated to reflect these changes. In Android, you only have to call the Cursor‘s requery method. This will also update the view. The following snippet follows from the above examples. It creates an entry, based on the contents of a couple of EditText fields and updates the view, simply by calling requery.

    ContentValues values = new ContentValues();
    values.put("first", firstNameEditor.getText()
        .toString());
    values.put("last", 
        lastNameEditor.getText().toString());
    database.insert("names", null, values);
    dataSource.getCursor().requery();

A full working example, using the snippets of code in this tutorial, is available here.

Advertisements

16 Responses to [Android] Using the SQLite Database with ListView

  1. edward says:

    hi kah,

    i am new in android and this tutorial really helped me
    now i am tring to using an activity funcion in activity.class as an launcher
    and SQLiteDemo class as its child
    and in manifest file i did all the manipulation that required, but still its not working
    and giving some error and forces me to closs.

    Can you send me/post a completed working version?

    thnks

    edwardpark.1997@gmail.com

    • kahgoh says:

      The full source listing should be available from here.

      • edward says:

        thnks, but i want a new class like activity.class(parent) with one button on it which direct it to your SQLiteDemo class(child), simple intent
        i know how to use intent but its giving me a compilation error…

        my request is, Can you send me/post a completed working version of intent..

        thnk u

  2. Hi, thank you for the wonderful tutorial.
    I Have managed to learn a lot from this. Can you help me out in deleting a row from the database on ClickedItem I have managed to include view.setOnItemClickListener (list view) But how can i delete the row based on Row ID?

    And also should i also refresh the database after deletion with any syntax?

    Please help.
    With love,
    Wesley.

  3. derry says:

    Hi,

    Thanks for the tutorial. I’m having a problem with the line

    @Override
    public void onClick(DialogInterface dialog, int which) {
    

    The error message is

    The method onClick(DialogInterface, int) of type new DialogInterface.OnClickListener(){} must override a superclass method

    Any suggestions or help much appreciated

    Thanks in advance

    • kahgoh says:

      Java 1.5 did not allow @Override annotation on methods implemented from the interface. However, if you are using 1.6 or 1.7, make sure you have set the compiler compliance level to 1.6 or 1.7. First, check the setting under Window -> Preferences -> Java -> Compiler. Then, check the project properties by right clicking on the project folder, select Properties -> Java Compiler. If the “Enable project specific settings” checkbox is unticked or the level is also set 1.6 or 1.7, then it should work too.

  4. aiden fry says:

    Hi,
    Thanks for this tutorial, very useful. I’m having an issue though.
    I am correctly populating the list from the database, however the very first item in the list is populated with the default layout text. It seems as though the list is being populated from item 1 rather than item 0. Any ideas how I would get around this??
    Thanks

    • aiden fry says:

      Fixed, my fault, needed to remove the lines:

      view.setHeaderDividersEnabled(true);
      view.addHeaderView(getLayoutInflater().inflate(R.layout.list_layout, null));

      It’s nice to know that I can do this if needed in the future.
      Thanks

      • kahgoh says:

        Hi,

        Great to hear you managed to find the answer. Originally, I had replied asking for a better idea of what you observed and wanted (its deleted now).

  5. Stephan says:

    Where is list view? Can’t see item click, listadapter or someting like between the codes. What a poor tutorial pofff..

    • kahgoh says:

      Where is list view?

      The demo activity is using a ListActivity (a subclass of Activity), which provides the ListView for free! In other words, I don’t define nor create it explicitly in the code because it is provided automatically.

      Can’t see item click, listadapter or someting like between the codes

      The indirectly implements the ListAdapter!

      I wasn’t sure what you referring to by “item click” or “something” though.

  6. ccpi says:

    Thanks for the useful example! Would like to point out though that allowing the Cursor and DatabaseHelper objects to leak into the activity class feels kind of unclean.

    This is just my opinion but I consider that such usage should be discouraged as it violates the separation of concerns principle: http://en.wikipedia.org/wiki/Separation_of_concerns and http://en.wikipedia.org/wiki/Coupling_(computer_programming) .

    In a real app all the database-related code would have to be located in the DatabaseHelper or some intermediate wrapper. Having db code in the activity class should be discouraged as a bad habit.

    Of course the example is still useful. But to make it even more useful I would recommend moving the db code out of the activity to prevent beginner programmer from following bad practices.

  7. boni says:

    Hi. How do I implement this SimpleCursorAdapter in my app if minSdkVersion is 8? An error tell me that this method call requires API11?
    How can i insert details of an incoming Sms into the db and take an action if a record contains a certain value?

    help me migrate to OOP!! Thank you

  8. Very good tutorial!

    Thanks for the good work!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: