Start by creating a new ToDoDBAdapter class. It will be used to manage your database interactions. Create private variables to store the SQLiteDatabase object and the Context of the calling application. Add a constructor that takes an application Context, and create static class variables for the name and version of the database, as well as a name for the to-do item table.

package com.paad.todolist;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteException;

import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class ToDoDBAdapter {
  private static final String DATABASE_NAME = "todoList.db";
  private static final String DATABASE_TABLE = "todoItems";
  private static final int DATABASE_VERSION = 1;

  private SQLiteDatabase db;
  private final Context context;

  public ToDoDBAdapter(Context _context) {
      this.context = _context;
  }
}

Create public convenience variables that define the column names: this will make it easier to find the correct columns when extracting values from query result Cursors.

public static final String KEY_ID = "_id";
public static final String KEY_TASK = "task";
public static final String KEY_CREATION_DATE = "creation_date";

Create a new taskDBOpenHelper class within the ToDoDBAdapter that extends SQLiteOpenHelper. It will be used to simplify version management of your database. Within it, overwrite the onCreate and onUpgrade methods to handle the database creation and upgrade logic.

private static class toDoDBOpenHelper extends SQLiteOpenHelper {

  public toDoDBOpenHelper(Context context, String name,
                          CursorFactory factory, int version) {
    super(context, name, factory, version);
  }

  // SQL Statement to create a new database.
  private static final String DATABASE_CREATE = "create table " +
    DATABASE_TABLE + " (" + KEY_ID + " integer primary key autoincrement, " +
    KEY_TASK + " text not null, " + KEY_CREATION_DATE + " long);";

  @Override
  public void onCreate(SQLiteDatabase _db) {
    _db.execSQL(DATABASE_CREATE);
  }

  @Override
  public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) {
    Log.w("TaskDBAdapter", "Upgrading from version " +
                           _oldVersion + " to " +
                           _newVersion + ", which will destroy all old data");

    // Drop the old table.
    _db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
    // Create a new one.
    onCreate(_db);
  }
}

Within the ToDoDBAdapter class, add a private variable to store an instance of the toDoDBOpenHelper class you just created, and assign it within the constructor.

private toDoDBOpenHelper dbHelper;

public ToDoDBAdapter(Context _context) {
  this.context = _context;
  dbHelper = new toDoDBOpenHelper(context, DATABASE_NAME,
                                  null, DATABASE_VERSION);
}

Still in the adapter class, create open and close methods that encapsulate the open and close logic for your database. Start with a close method that simply calls close on the database object.

public void close() {
  db.close();
}

The open method should use the toDoDBOpenHelper class. Call getWritableDatabase to let the helper handle database creation and version checking. Wrap the call to try to provide a readable database if a writable instance can't be opened.

public void open() throws SQLiteException {
  try {
    db = dbHelper.getWritableDatabase();
  } catch (SQLiteException ex) {
    db = dbHelper.getReadableDatabase();
  }
}

Add strongly typed methods for adding, removing, and updating items.

// Insert a new task
public long insertTask(ToDoItem _task) {
  // Create a new row of values to insert.
  ContentValues newTaskValues = new ContentValues();
  // Assign values for each row.
  newTaskValues.put(KEY_TASK, _task.getTask());
  newTaskValues.put(KEY_CREATION_DATE, _task.getCreated().getTime());
  // Insert the row.
  return db.insert(DATABASE_TABLE, null, newTaskValues);
}

// Remove a task based on its index
public boolean removeTask(long _rowIndex) {
  return db.delete(DATABASE_TABLE, KEY_ID + "=" + _rowIndex, null) > 0;
}

// Update a task
public boolean updateTask(long _rowIndex, String _task) {
  ContentValues newValue = new ContentValues();
  newValue.put(KEY_TASK, _task);
  return db.update(DATABASE_TABLE, newValue, KEY_ID + "=" + _rowIndex, null) > 0;
}

Now add helper methods to handle queries. Write three methods — one to return all the items, another to return a particular row as a Cursor, and finally one that returns a strongly typed ToDoItem.

public Cursor getAllToDoItemsCursor() {
  return db.query(DATABASE_TABLE,
                  new String[] { KEY_ID, KEY_TASK, KEY_CREATION_DATE},
                  null, null, null, null, null);
}

public Cursor setCursorToToDoItem(long _rowIndex) throws SQLException {
  Cursor result = db.query(true, DATABASE_TABLE,
                           new String[] {KEY_ID, KEY_TASK},
                           KEY_ID + "=" + _rowIndex, null, null, null,
                           null, null);
  if ((result.getCount() == 0) || !result.moveToFirst()) {
    throw new SQLException("No to do items found for row: " + _rowIndex);
  }
  return result;
}

public ToDoItem getToDoItem(long _rowIndex) throws SQLException {
  Cursor cursor = db.query(true, DATABASE_TABLE,
                           new String[] {KEY_ID, KEY_TASK},
                           KEY_ID + "=" + _rowIndex, null, null, null,
                           null, null);
  if ((cursor.getCount() == 0) || !cursor.moveToFirst()) {
    throw new SQLException("No to do item found for row: " + _rowIndex);
  }

  String task = cursor.getString(TASK_COLUMN);
  long created = cursor.getLong(CREATION_DATE_COLUMN);

  ToDoItem result = new ToDoItem(task, new Date(created));
  return result;
}

That completes the database helper class. Return the ToDoList Activity and update it to persist the to-do list array. Start by updating the Activity's onCreate method to create an instance of the toDoDBAdapter and open a connection to the database. Also include a call to the populateTodoList method stub.

ToDoDBAdapter toDoDBAdapter;

public void onCreate(Bundle icicle) {
  [ ... existing onCreate logic ... ]

  toDoDBAdapter = new ToDoDBAdapter(this);

  // Open or create the database
  toDoDBAdapter.open();

  populateTodoList();
}

private void populateTodoList() { }

Create a new instance variable to store a Cursor over all the to-do items in the database. Update the populateTodoList method to use the toDoDBAdapter instance to query the database, and call startManagingCursor to let the Activity manage the Cursor. It should also make a call to updateArray, a method that will be used to repopulate the to-do list array using the Cursor.

Cursor toDoListCursor;

private void populateTodoList() {
  // Get all the todo list items from the database.
  toDoListCursor = toDoDBAdapter. getAllToDoItemsCursor();
  startManagingCursor(toDoListCursor);

  // Update the array.
  updateArray();
}

private void updateArray() { }

Now implement the updateArray method to update the current to-do list array. Call requery on the result Cursor to ensure it's fully up to date, then clear the array and iterate over the result set. When the update is complete call notifyDataSetChanged on the Array Adapter.

private void updateArray() {
  toDoListCursor.requery();

  todoItems.clear();

  if (toDoListCursor.moveToFirst())
    do {
      String task = toDoListCursor.getString(ToDoDBAdapter.TASK_COLUMN);
      long created = toDoListCursor.getLong(ToDoDBAdapter.CREATION_DATE_COLUMN);

      ToDoItem newItem = new ToDoItem(task, new Date(created));
      todoItems.add(0, newItem);
    } while(toDoListCursor.moveToNext());

  aa.notifyDataSetChanged();
}

To join the pieces together, modify the OnKeyListener assigned to the text entry box in the onCreate method, and update the removeItem method. Both should now use the toDoDBAdapter to add and remove items from the database rather than modifying the to-do list array directly.

As a final step, override the onDestroy method of your activity to close your database connection.

@Override
public void onDestroy() {
  super.onDestroy();

  // Close the database
  toDoDBAdapter.close();
}

First open the Earthquake project and create a new EarthquakeProvider class that extends ContentProvider. Include stubs to override the onCreate, getType, query, insert, delete, and update methods.

package com.paad.earthquake;

import android.content.*;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.text.TextUtils;
import android.util.Log;

public class EarthquakeProvider extends ContentProvider {

  @Override
  public boolean onCreate() {
  }

  @Override
  public String getType(Uri url) {
  }

  @Override
  public Cursor query(Uri url, String[] projection, String selection,
                      String[] selectionArgs, String sort) {
  }

  @Override
  public Uri insert(Uri _url, ContentValues _initialValues) {
  }

  @Override
  public int delete(Uri url, String where, String[] whereArgs) {
  }

  @Override
  public int update(Uri url, ContentValues values,
                    String where, String[]wArgs) {
  }
}

Publish the URI for this provider. This URI will be used to access this Content Provider from within other application components via the ContentResolver.

public static final Uri CONTENT_URI =
  Uri.parse("content://com.paad.provider.earthquake/earthquakes");

Create the database that will be used to store the earthquakes. Within the EarthquakeProvider create a new SQLiteDatabase instance and expose public variables that describe the column names and indexes. Include an extension of SQLiteOpenHelper to manage database creation and version control.

// The underlying database
private SQLiteDatabase earthquakeDB;

private static final String TAG = "EarthquakeProvider";
private static final String DATABASE_NAME = "earthquakes.db";
private static final int DATABASE_VERSION = 1;
private static final String EARTHQUAKE_TABLE = "earthquakes";

// Column Names
public static final String KEY_ID = "_id";
public static final String KEY_DATE = "date";
public static final String KEY_DETAILS = "details";
public static final String KEY_LOCATION_LAT = "latitude";
public static final String KEY_LOCATION_LNG = "longitude";
public static final String KEY_MAGNITUDE = "magnitude";
public static final String KEY_LINK = "link";

// Column indexes
public static final int DATE_COLUMN = 1;
public static final int DETAILS_COLUMN = 2;
public static final int LONGITUDE_COLUMN = 3;
public static final int LATITUDE_COLUMN = 4;
public static final int MAGNITUDE_COLUMN = 5;
public static final int LINK_COLUMN = 6;

// Helper class for opening, creating, and managing database version control
private static class earthquakeDatabaseHelper extends SQLiteOpenHelper {
  private static final String DATABASE_CREATE =
    "create table " + EARTHQUAKE_TABLE + " ("
    + KEY_ID + " integer primary key autoincrement, "
    + KEY_DATE + " INTEGER, "
    + KEY_DETAILS + " TEXT, "
    + KEY_LOCATION_LAT + " FLOAT, "
    + KEY_LOCATION_LNG + " FLOAT, "
    + KEY_MAGNITUDE + " FLOAT), "
    + KEY_LINK + " TEXT);";

  public earthquakeDatabaseHelper(Context context, String name,
                                  CursorFactory factory, int version) {
    super(context, name, factory, version);
  }

  @Override
  public void onCreate(SQLiteDatabase db) {
    db.execSQL(DATABASE_CREATE);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");

    db.execSQL("DROP TABLE IF EXISTS " + EARTHQUAKE_TABLE);
    onCreate(db);
  }
}

Create a UriMatcher to handle requests using different URIs. Include support for queries and transactions over the entire dataset (QUAKES) and a single record matching a quake index value (QUAKE_ID).

// Create the constants used to differentiate between the different URI
// requests.
private static final int QUAKES = 1;
private static final int QUAKE_ID = 2;

private static final UriMatcher uriMatcher;

// Allocate the UriMatcher object, where a URI ending in 'earthquakes' will
// correspond to a request for all earthquakes, and 'earthquakes' with a
trailing '/[rowID]' will represent a single earthquake row.
static {
  uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
  uriMatcher.addURI("com.paad.provider.Earthquake", "earthquakes", QUAKES);
  uriMatcher.addURI("com.paad.provider.Earthquake", "earthquakes/#", QUAKE_ID);
}

Override the getType method to return a string for each of the URI structures supported.

@Override
public String getType(Uri uri) {
  switch (uriMatcher.match(uri)) {

    case QUAKES: return "vnd.android.cursor.dir/vnd.paad.earthquake";
    case QUAKE_ID: return "vnd.android.cursor.item/vnd.paad.earthquake";
    default: throw new IllegalArgumentException("Unsupported URI: " + uri);
  }
}

Override the provider's onCreate handler to create a new instance of the database helper class, and open a connection to the database.

@Override
public boolean onCreate() {
  Context context = getContext();

  earthquakeDatabaseHelper dbHelper = new earthquakeDatabaseHelper(context,
    DATABASE_NAME, null, DATABASE_VERSION);
  earthquakeDB = dbHelper.getWritableDatabase();
  return (earthquakeDB == null) ? false : true;
}

Implement the query and transaction stubs. Start with the query method, which should decode the request being made based on the URI (either all content or a single row), and apply the selection, projection, and sort-order criteria parameters to the database before returning a result Cursor.

@Override
public Cursor query(Uri uri,
                    String[] projection,
                    String selection,
                    String[] selectionArgs,
                    String sort) {

  SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

  qb.setTables(EARTHQUAKE_TABLE);

// If this is a row query, limit the result set to the passed in row.

  switch (uriMatcher.match(uri)) {
    case QUAKE_ID: qb.appendWhere(KEY_ID + "=" + uri.getPathSegments().get(1));
                   break;
    default      : break;
  }

  // If no sort order is specified sort by date / time
  String orderBy;
  if (TextUtils.isEmpty(sort)) {
    orderBy = KEY_DATE;
  } else {
    orderBy = sort;
  }

  // Apply the query to the underlying database.
  Cursor c = qb.query(earthquakeDB,
                      projection,
                      selection, selectionArgs,
                      null, null,
                      orderBy);

  // Register the contexts ContentResolver to be notified if
  // the cursor result set changes.
  c.setNotificationUri(getContext().getContentResolver(), uri);

  // Return a cursor to the query result.
  return c;
}

Now implement methods for inserting, deleting, and updating content. In this case the process is an exercise in mapping Content Provider transaction requests to their database equivalents.

@Override
public Uri insert(Uri _uri, ContentValues _initialValues) {
  // Insert the new row, will return the row number if
  // successful.
  long rowID = earthquakeDB.insert(EARTHQUAKE_TABLE, "quake", _initialValues);

  // Return a URI to the newly inserted row on success.
  if (rowID > 0) {
    Uri uri = ContentUris.withAppendedId(CONTENT_URI, rowID);
    getContext().getContentResolver().notifyChange(uri, null);
    return uri;
  }
  throw new SQLException("Failed to insert row into " + _uri);
}

@Override
public int delete(Uri uri, String where, String[] whereArgs) {
  int count;

switch (uriMatcher.match(uri)) {
    case QUAKES:
      count = earthquakeDB.delete(EARTHQUAKE_TABLE, where, whereArgs);
      break;

    case QUAKE_ID:
      String segment = uri.getPathSegments().get(1);
      count = earthquakeDB.delete(EARTHQUAKE_TABLE, KEY_ID + "="
                                  + segment
                                  + (!TextUtils.isEmpty(where) ? " AND ("
                                  + where + ')' : ""), whereArgs);
      break;

    default: throw new IllegalArgumentException("Unsupported URI: " + uri);
  }

  getContext().getContentResolver().notifyChange(uri, null);
  return count;
}

@Override
public int update(Uri uri, ContentValues values, String where, String[]
whereArgs) {
  int count;
  switch (uriMatcher.match(uri)) {
    case QUAKES: count = earthquakeDB.update(EARTHQUAKE_TABLE, values,
                                             where, whereArgs);
                 break;

    case QUAKE_ID: String segment = uri.getPathSegments().get(1);
                   count = earthquakeDB.update(EARTHQUAKE_TABLE, values, KEY_ID
                           + "=" + segment
                           + (!TextUtils.isEmpty(where) ? " AND ("
                           + where + ')' : ""), whereArgs);
                   break;

    default: throw new IllegalArgumentException("Unknown URI " + uri);
  }

  getContext().getContentResolver().notifyChange(uri, null);
  return count;
}

With the Content Provider complete, register it in the manifest by creating a new <provider> node within the application tag.

<provider android:name=".EarthquakeProvider"
          android:authorities="com.paad.provider.earthquake" />

Within the Earthquake Activity, update the addNewQuake method. It should use the application's Content Resolver to insert each new Earthquake into the provider. Move the existing array control logic into a separate addQuakeToArray method.

private void addNewQuake(Quake _quake) {
  ContentResolver cr = getContentResolver();
  // Construct a where clause to make sure we don't already have this
  // earthquake in the provider.
  String w = EarthquakeProvider.KEY_DATE + " = " + _quake.getDate().getTime();

  // If the earthquake is new, insert it into the provider.
  if (cr.query(EarthquakeProvider.CONTENT_URI, null, w, null, null).getCount()==0){
    ContentValues values = new ContentValues();

    values.put(EarthquakeProvider.KEY_DATE, _quake.getDate().getTime());
    values.put(EarthquakeProvider.KEY_DETAILS, _quake.getDetails());
    double lat = _quake.getLocation().getLatitude();
    double lng = _quake.getLocation().getLongitude();
    values.put(EarthquakeProvider.KEY_LOCATION_LAT, lat);
    values.put(EarthquakeProvider.KEY_LOCATION_LNG, lng);
    values.put(EarthquakeProvider.KEY_LINK, _quake.getLink());
    values.put(EarthquakeProvider.KEY_MAGNITUDE, _quake.getMagnitude());

    cr.insert(EarthquakeProvider.CONTENT_URI, values);
    earthquakes.add(_quake);

    addQuakeToArray(_quake);
  }
}

private void addQuakeToArray(Quake _quake) {
  if (_quake.getMagnitude() > minimumMagnitude) {
    // Add the new quake to our list of earthquakes.
    earthquakes.add(_quake);

    // Notify the array adapter of a change.
    aa.notifyDataSetChanged();
  }
}

Create a new loadQuakesFromProvider method that loads all the earthquakes from the Earthquake Provider, and inserts them into the Array List using the addQuakeToArray method created in Step 1.

private void loadQuakesFromProvider() {
  // Clear the existing earthquake array
  earthquakes.clear();

  ContentResolver cr = getContentResolver();

// Return all the saved earthquakes
  Cursor c = cr.query(EarthquakeProvider.CONTENT_URI, null, null, null, null);

  if (c.moveToFirst())
    {
      do {
        // Extract the quake details.
        Long datems = c.getLong(EarthquakeProvider.DATE_COLUMN);
        String details = c.getString(EarthquakeProvider.DETAILS_COLUMN);
        Float lat = c.getFloat(EarthquakeProvider.LATITUDE_COLUMN);
        Float lng = c.getFloat(EarthquakeProvider.LONGITUDE_COLUMN);
        Double mag = c.getDouble(EarthquakeProvider.MAGNITUDE_COLUMN);
        String link = c.getString(EarthquakeProvider.LINK_COLUMN);

        Location location = new Location("dummy");
        location.setLongitude(lng);
        location.setLatitude(lat);

        Date date = new Date(datems);

        Quake q = new Quake(date, details, location, mag, link);
        addQuakeToArray(q);
      } while(c.moveToNext());
  }
}

Call loadQuakesFromProvider from onCreate to initialize the earthquake List View at start-up.

@Override
public void onCreate(Bundle icicle) {
  super.onCreate(icicle);
  setContentView(R.layout.main);

  earthquakeListView = (ListView)this.findViewById(R.id.earthquakeListView);

  earthquakeListView.setOnItemClickListener(new OnItemClickListener() {

    @Override
    public void onItemClick(AdapterView _av, View _v, int _index, long arg3) {
      selectedQuake = earthquakes.get(_index);
      showDialog(QUAKE_DIALOG);
    }
  });

  int layoutID = android.R.layout.simple_list_item_1;
  aa = new ArrayAdapter<Quake>(this, layoutID , earthquakes);
  earthquakeListView.setAdapter(aa);

  loadQuakesFromProvider();

  updateFromPreferences();
  refreshEarthquakes();
}

Finally, make a change to the refreshEarthquakes method so that it loads the saved earthquakes from the provider after clearing the array, but before adding any new quakes received.

private void refreshEarthquakes() {
  [ ... exiting refreshEarthquakes method ... ]

  // Clear the old earthquakes
  earthquakes.clear();
  loadQuakesFromProvider();

  [ ... exiting refreshEarthquakes method ... ]
}