Coding for SQLite using Android’s SDK

Listing 5-1 contains code to access an SQLite database. When the activity begins running, the code inserts data, then modifies the data, and then deletes the data. To keep things simple, I intentionally omit much of the fail-safe checking that database code normally has. I also have a dirt-simple user interface. The activity’s screen has only one widget — a text view for displaying data at various stages of processing.

Listing 5-1: Sending Commands to a Database

package com.allmycode.db1;

import android.app.Activity;

import android.content.ContentValues;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.os.Bundle;

import android.widget.TextView;

public class MyActivity extends Activity {

  TextView textView;

  Cursor cursor = null;

  DBHelper helper = null;

  SQLiteDatabase db = null;

  ContentValues values = null;

@Override

public void onCreate(Bundle savedInstanceState) {

  super.onCreate(savedInstanceState);

  setContentView(R.layout.main);

  textView = (TextView) findViewById(R.id.textView1);

  helper = new DBHelper(this, "simple_db", null, 1);

  db = helper.getWritableDatabase();

  values = new ContentValues();

  values.put("name", "Barry");

  values.put("amount", "100");

  db.insert("simpletable", "", values);

  values.clear();

  values.put("name", "Harriet");

  values.put("amount", "300");

  db.insert("simpletable", "", values);

  addToTextView();

  values.clear();

  values.put("amount", "500");

  db.update("simpletable", values, "name='Barry'", null);

  addToTextView();

  db.delete("simpletable", "1", null);

  addToTextView();

}

@Override

public void onDestroy() {

  super.onDestroy();

  helper.close();

}

void addToTextView() {

  cursor =

      db.rawQuery("SELECT * FROM simpletable;", null);

  if (cursor != null && cursor.moveToFirst()) {

      String name;

      do {

        String _id = cursor.getString(0);

        name = cursor.getString(1);

        int amount = cursor.getInt(2);

        textView.append(_id + " " + name + " " + amount

              + " ");

      } while (cursor.moveToNext());

     }

     textView.append("----------- ");

   }

}

In Listing 5-1, I make a meager attempt to be tidy by closing the helper in the activity’s onDestroy method. But in practice, manually closing and reopening database resources can be much more complicated.

The first new and exciting statement in Listing 5-1 is the call to a DBHelper constructor. My DBHelper class (to be unveiled in Listing 5-2) extends the abstract android.database.sqlite.SQLiteOpenHelper class. The purpose of such a class is to manage the creation and modification of an SQLite database. In particular, the code in Listing 5-1 uses the helper to grab hold of an actual database.

Details about the friendly helper class

Listing 5-2 contains my DBHelper code.

Listing 5-2: A Subclass of the SQLiteOpenHelper Class

package com.allmycode.db1;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {

  public DBHelper(Context context, String dbName,

       SQLiteDatabase.CursorFactory factory, int version) {

    super(context, dbName, factory, version);

  }

  @Override

  public void onCreate(SQLiteDatabase db) {

    String createString =

       "CREATE TABLE IF NOT EXISTS simpletable "

           + "( _id INTEGER PRIMARY KEY AUTOINCREMENT, "

           + "name TEXT NOT NULL, "

           + "amount INTEGER NOT NULL);";

    db.execSQL(createString);

}

  @Override

  public void onUpgrade(SQLiteDatabase db,

      int oldVersion, int newVersion) {

    String dropString =

          "DROP TABLE IF EXISTS simpletable;";

    db.execSQL(dropString);

    aonCreate(db);

  }

}

In Listing 5-2, I implement the parent class’s abstract methods (as I must). I also create a constructor that takes the lazy way out, passing all its parameters to the parent constructor.

The most important part of Listing 5-2 is the helper’s onCreate method. You never call this method directly. Instead, Android calls the method on your behalf when you set up a helper the way I do in Listing 5-1.

Android delays the call to the helper’s onCreate method until your code actually uses the database. That bodes well for your app’s performance.

Android hands the onCreate method an SQLite database (a database belonging to the app in which the helper is located). That SQLite database (called db in the onCreate method’s parameter list) has an execSQL method. Listing 5-2 calls the database’s execSQL method, feeding the method an ordinary Java string. Luckily for me, this ordinary Java string happens to be an SQL command.

In Listing 5-2's onCreate method, lots of good things happen without much fanfare. If the database doesn’t already exist, Android creates one. If the database doesn’t already have a table named simpletable, SQLite creates one. If the database already exists and has a simpletable, the onCreate method doesn’t rock the boat.

Databases normally live on after the hosting process terminates. If you run this example’s code in March and then turn off your device for three months, the database still exists (along with any data that you added in March) when you turn on the device again in June.

I don’t cover SQL commands in this book. I’d go crazy trying to cover them all. But fortunately, other authors have covered SQL without going crazy. So to read all about SQL commands, buy either the standard SQL For Dummies or the supersize version SQL All-in-One For Dummies, both by Allen G. Taylor (John Wiley & Sons, Inc.).

SQL is more readable than some other languages, so with or without a thorough introduction, you can probably make sense of most of the SQL commands in this book. The SQL command midway through Listing 5-2, for example, says the following:

If the database doesn’t have a table named simpletable,

  create simpletable with three columns, called

  _id, name, and amount.

    The _id column stores an integer value,

          which serves to identify its row,

          and is incremented by 1 for each newly added row,

    The name column stores a string value

          which cannot be null, and

    The amount column stores an integer value

    which cannot be null.

Android’s SDK wants you to use the name _id for a table’s auto-incremented primary key. I haven’t experimented with other column names, but from what I’ve read, something will break if I try it.

An SQLiteOpenHelper's onUpgrade method deals with new versions of the database. For example, when I modify the database in Listing 5-2 so that each row has an additional column (a Comment column, perhaps), I’m changing the table’s structure. A change of this kind requires me to obliterate the existing table (that is, to drop the table) and to create another table as if from scratch. In a helper’s onUpgrade method, you manage this (admittedly delicate) procedure.

Details about the mainstream SQLite code

In Listing 5-1, after the call to getWritableDatabase, the code performs some fairly commonplace operations — namely, inserting, updating, deleting, and querying.

Inserting

Each call to db.insert adds a row of values to the simpletable. Each value is actually a name/value pair, the name being a database column name and the value being something to stuff into that column.

The put method belonging to the ContentValues class takes two String parameters. In the call values.put("amount", "100"), the first parameter is a column name. The second parameter is the value to be placed into that column in the current row. Notice that the second parameter "100" is a Java String even though the database’s amount column stores an integer. That’s just the way it works. Oh, and while you’re remembering things, don’t forget to call values.clear() between using one set of values and assembling another.

In Listing 5-1, the insert method takes three parameters — a table name, a null column hack, and a set of values constituting the newly created row. The null column hack is a value that you add to deal with the possibility of a completely empty insert. SQLite behaves badly if you try to insert a row containing no data.

As a result of the calls to insert in Listing 5-1, the activity’s screen contains the first two lines in the text view of Figure 5-4.

Using a content resolver

In Listing 5-3, the client app has no direct communication with the provider’s database. Instead, the client app talks to the database through a content resolver.

A content resolver has methods to insert data, update data, delete data, and so on. Each content resolver method takes a URI — a reference to some data offered by a content provider. Here’s what happens when your code calls a content resolver method:

• Android examines the URI’s scheme and finds the content : scheme.

  The content: scheme tells Android to look for a matching content provider.

• Android compares the URI’s authority with the authorities in the intent filters of available content providers. (See Figure 5-9.)

  A content provider must declare one or more authorities in its app’s AndroidManifest.xml document. Listing 5-4 has the AndroidManifest.xml document for this section’s example. Notice that in this example, the app containing the content provider has no activity. The app has no direct interface to the user.

  Listing 5-4: A Content Provider’s XML Element

     <manifest xmlns:android=

              "http://schemas.android.com/apk/res/android"

           package="com.allmycode.db">

   

        <application android:allowBackup="true"

                     android:label="@string/app_name"

                     android:icon="@drawable/ic_launcher"

                     android:theme="@style/AppTheme">

   

           <provider android:name=".MyContentProvider"

                     android:authorities="com.allmycode.db"

                     android:exported="true">

           </provider>

   

        </application>

   

     </manifest>

   

  If an android:authorities attribute contains more than one authority, you separate authorities from one another using semicolons:

  android:authorities="this.is.one;this.is.another"

   

  So far, so good. Here’s what happens when Android finds a matching content provider:

  • Android hands the client’s database-like call (URI and all) to the matching content provider.

    After Android hands the call to a content provider, the ball is in the content provider’s court.

  • The content provider parses the URI’s path to further refine the client app’s request.
  • The content provider uses its own app’s data to fulfill the client app’s request.

Using a cursor loader

To get data from a content provider, Listing 5-3 uses a cursor loader. The CursorLoader constructor has six parameters. The parameters are as follows:

• Context context: In Listing 5-3, the context (this) is the activity itself.

  To read about Android’s Context class, see Chapter 1 in this minibook.

• Uri uri: A reference to some data offered by a content provider.
• String[] projection: A list of columns from the table. (If you pass null, you’re saying “gimme' all the columns.”)
• String selection: An SQL WHERE clause. (See this chapter’s “Updating” section.)
• String[] selectionArgs: The WHERE string can include question marks. The question marks are placeholders for actual values that you place in the selectionArgs array. (See this chapter’s “Updating” section.)
• String sortOrder: The order in which the rows come from the table. (A SQL statement can have an ORDER BY clause. Put that clause, minus the words ORDER BY, in this parameter.) A null value means “use the default order, whatever that is.”

From the constructor call onward, the activity’s thread registers a callback listener (the MyOnLoadCompleteListener) and calls the loader’s startLoading method. This means that the parent thread (the code in Listing 5-3) doesn’t stop and wait for the startLoading() call to return. Instead, the activity’s code continues on its merry way.

In the meantime, the cursor loader does its thing in a separate thread, and the callback listener (OnLoadCompleteListener) listens for the cursor loader to get its results. When at last the loading of the data is complete (that is, the cursor is done being loaded), Android calls the onLoadComplete method inside the OnLoadCompleteListener instance. This onLoadComplete method can do whatever you want done with the cursor’s data. (In Listing 5-3, the onLoadComplete method displays data in your activity’s text view.)

Like a teenager off to college, a CursorLoader instance manages itself by leaving its parent thread, going out on its own, and eventually returning with the desired result. Like any real teenager, a CursorLoader instance, with all its asynchronous behavior, can behave in strange and surprising ways. Be mindful of your use of the CursorLoader class.

The code in Listing 5-3 works with Honeycomb (Android 3.0) and later. Before Honeycomb, Android used a startManagingCursor method instead of the CursorLoader in Listing 5-3. Android’s documentation says that older devices can use the newer CursorLoader code as long as you include a compatibility package along with your app. This means importing the package android.support.v4.content.CursorLoader.

At last! A content provider!

Listing 5-5 contains a content provider for this section’s ongoing example.

Listing 5-5: Look: It’s a Content Provider

package com.allmycode.db;

import android.content.ContentProvider;

import android.content.ContentUris;

import android.content.ContentValues;

import android.content.UriMatcher;

import android.database.Cursor;

import android.database.MatrixCursor;

import android.database.sqlite.SQLiteDatabase;

import android.net.Uri;

public class MyContentProvider extends ContentProvider {

  public static final Uri CONTENT_URI = Uri

      .parse("content://com.allmycode.db/names_amounts");

  public static final Uri SILLY_URI = Uri

    .parse("content://com.allmycode.db/silly_stuff");

  private static final String SIMPLE_DB = "simple_db";

  private static final String SIMPLETABLE = "simpletable";

  DBHelper helper = null;

  SQLiteDatabase db = null;

  ContentValues values = null;

  UriMatcher uriMatcher = null;

  {

    uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);

    uriMatcher.addURI("com.allmycode.db",

                        "names_amounts", 1);

    uriMatcher.addURI("com.allmycode.db",

                        "silly_stuff", 2);

  }

  @Override

  public boolean onCreate() {

    try {

      helper =

        new DBHelper(getContext(), SIMPLE_DB, null, 1);

      db = helper.getWritableDatabase();

      values = new ContentValues();

      return true;

    } catch (Exception e) {

      return false;

    }

  }

  @Override

  public Uri insert(Uri ure, ContentValues values) {

    long id = db.insert(SIMPLETABLE, "", values);

    return ContentUris.withAppendedId(CONTENT_URI, id);

  }

  @Override

  public int update(Uri uri, ContentValues values,

      String whereClause, String[] whereArgs) {

    int numOfChangedRows =

      db.update(SIMPLETABLE, values, whereClause,

            whereArgs);

    return numOfChangedRows;

  }

  @Override

  public int delete(Uri uri, String whereClause,

        String[] whereArgs) {

    int numOfChangedRows =

        db.delete(SIMPLETABLE, whereClause, whereArgs);

    return numOfChangedRows;

  }

  @Override

  public Cursor query(Uri uri, String[] columns,

      String whereClause, String[] whereArgs,

      String sortOrder) {

    Cursor cursor = null;

    int code = uriMatcher.match(uri);

    if (code == 1) {

      cursor =

          db.query(SIMPLETABLE, columns, whereClause,

              whereArgs, null, null, sortOrder);

    } else if (code == 2) {

      String[] columnNames = { "_id", "name", "amount" };

      String[] rowValues = { "Table ", "4 ", "2" };

      MatrixCursor matrixCursor =

            new MatrixCursor(columnNames);

      matrixCursor.addRow(rowValues);

      cursor = matrixCursor;

    }

    return cursor;

  }

  @Override

  public String getType(Uri uri) {

    return null;

  }

}

Listing 5-5 implements the six abstract methods declared in the android.content.ContentProvider class. The implementation code bears a striking resemblance to some of the code in Listing 5-1.

• Both onCreate methods use DBHelper (which extends Android’s SQLiteOpenHelper class) to get a writable database.
• Both listings call the database’s insert, update, and delete methods.

• Both listings issue a query to the database.

  Actually, Listing 5-1 uses the rawQuery method and an SQL command string. In contrast, Listing 5-5 uses the query method with a bunch of parameters. The difference has nothing to do with content providers. It’s just my whim in using different methods to illustrate different ways of issuing a query.

The bottom line is this: A content provider does with its app’s data what an ordinary activity does with its own app’s data.

Listing 5-5 has some features that I choose not to use in Listing 5-1. For example, Android’s update and delete methods return int values. In Listing 5-1, I simply ignore the return values. But in Listing 5-5, I pass each method’s return value back to the client code. (Don’t congratulate me on my diligence. I pass on each value because the content provider’s abstract update and delete methods must have int return values.)

Listing 5-5 also has some features that are unique to content providers. For example, to effectively dish out data, a content provider must manage URIs. Normally, a content provider examines each method’s incoming URI and uses the information to decide on its next move. In Listing 5-5, I keep things simple with only a minor bit of URI handling in the query method.

Outside the Android world, the use of URIs to connect to databases is commonplace. For example, in a Java JDBC program, you may connect to a database with a statement, such as DriverManager.getConnection("jdbc:derby:AccountDatabase"). In this statement’s opaque URI, the scheme jdbc: forwards a request to another scheme — namely, the derby: scheme.

In Listing 5-5, the query method calls on a UriMatcher instance to distinguish one path from another. As it’s defined near the start of Listing 5-5, the UriMatcher instance returns 1 for the names_amounts path and returns 2 for the silly_stuff path.

A return value of 1 makes the query method do its regular old database query. But a return value of 2 does something entirely different. To show that I can do it, I respond to a URI’s silly_stuff path without consulting a real database. Instead, I use arrays to concoct something that looks like a simpletable row. I squish the arrays into a MatrixCursor (a cursor built from an array rather than a database), and I send the cursor back to the client.

The insert method in Listing 5-5 returns a URI. What’s that all about? Each row associated with the content: scheme has its own individual URI. For example, in a run of this section’s code, the first two row insertions have URIs content://com.allmycode.db/names_amounts/1 and content://com.allmycode.db/names_amounts/2. The android.content.ContentUris class’s withAppendedId method fetches the URI for a particular database row. The client can use this row-specific URI to refer to one database row at a time.