In part because it’s lightweight and in the public domain, SQLite is an obvious choice for many projects where there are constraints on space, power, and cost. For those and other reasons, it’s built into a variety of operating systems and is used on many devices.

This chapter shows you some of the key elements of using SQLite with Android. It’s built into android.database.sqlite, so it’s there for you to use. The basics of using SQLite are the same whether you’re using PHP to build a web site (see Chapters 6 and 10) or you’re using Core Data on iOS or OS X (see Chapters 8 and 12).

Integrating SQLite with Any Operating System, Framework, or Language

No matter what development platform you’re using, integrating SQLite in your app is always the same (and, indeed, this applies to any database).

1. Design your database. This is not a SQLite issue. See the sidebar, “The Critical Database Design Step.”
2. Connect your app to the database at runtime.
3. Use the connection to add, delete, insert, or update data.
4. Use the results of the operation in your app. This may consist of refreshing the interface by adding or removing items, or it may consist of working with the data in a new item or anything else you and your users require. This is not a SQLite issue.

There is a sort of progression in the integration of SQLite with PHP, Android/Java, and Core Data for iOS and OS X. With PHP, you are typically working with very visible SQLite code, and, as you will see, it is frequently procedural code for the most part. When you get to Core Data, you’ll be working with object-oriented code where the framework and its classes use SQLite code behind the scenes: you will rarely write SQLite code directly, but you’re using it all the time. Android and Java strike a middle ground so that the SQLite code is more visible than it is in Core Data, but it’s not as visible as it is when you’re using PHP.

Although there are object-oriented ways of writing PHP code, by and large you are working in a procedural environment. However, in today’s world, the recommended best practice for integrating PHP with a database is to use the PHP data object (PDO) extension. That is an object-oriented class which encapsulates the code that you need to work with for a database. There are versions for SQLite and other major databases so your PDO code is relatively easy to port.

Chapter 6 showed you the coding basics of using SQLite with PHP. Here is the summary of the steps. This is only a summary. In practice, you’ll flesh out the foreach loop and you’ll probably replace or die with a try/catch block.

$sqlite = new PDO(’sqlite:sqlitephp.sqlite’);

$query = $sqlite->prepare (...SQLite query...);

$query->execute() or die ("Can’t execute");

$result  = $query->fetchAll() or die ("Can’t fetchAll");

foreach ($result as $row) {
    ...work with each result row
}

Using Android and SQLite

The Android NotePad example is an excellent example of the integration of SQLite with Android. You can download it from https://android.googlesource.com/platform/development/+/05523fb0b48280a5364908b00768ec71edb847a2/samples/NotePad/src/com/example/android/notepad/NotePadProvider.java.

The NotePadProvider code in that example extends ContentProvider which encapsulates the basic SQLite functionality that you need. This is the sort of code that you’ll need in your own app. You can read the code, but here are some key points to look for. You’ll need to implement them in your own app with your own values and strings (like the names of your columns, table, and database).

Using the Static Values

If you’re new this environment, you may have to do a bit of searching to find the many static variables that are used. Here’s a quick guide to where they may be.

Static Values May Be in the APIs

The android.provider.BaseColumns API is used by Android content providers throughout the system. Structured data is used throughout Android and many other operating systems (the Cocoa and Cocoa Touch table classes provide somewhat similar functionality). In the example code, two static values from BaseColumns are used frequently. Both are strings. _COUNT is the number of rows in a directory, and _ID is a unique id for a row.

Static Values May Be in Imported Files.

NotePad.java contains the NotePad class. That class itself contains the NoteColumns class. It contains these static values (among others):

public static final String TITLE = "title";
public static final String NOTE = "note";
public static final String CREATED_DATE = "created";
public static final String MODIFIED_DATE = "modified";

Static Values May Be in the Main File

Here are some static values from the NoteBookProvider class in NoteBookProvider.java.

private static final String TAG = "NotePadProvider";
private static final String DATABASE_NAME = "notepad.db";
private static final int DATABASE_VERSION = 2;
private static final String NOTES_TABLE_NAME = "notes";
private static HashMap<String, String> sNotesProjectionMap;
private static HashMap<String, String> sLiveFolderProjectionMap;
private static final int NOTES = 1;
private static final int NOTE_ID = 2;
private static final int LIVE_FOLDER_NOTES = 3;
private static final UriMatcher sUriMatcher;

Extend SQliteOpenHelper

You’ll need to declare a class that extends SQLiteOpenHelper and implement your own onCreate function. Following is the code you need to work with in the example:

private static class DatabaseHelper extends SQLiteOpenHelper {
  DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE " + NOTES_TABLE_NAME + " ("
      + NoteColumns._ID + " INTEGER PRIMARY KEY,"
      + NoteColumns.TITLE + " TEXT,"
      + NoteColumns.NOTE + " TEXT,"
      + NoteColumns.CREATED_DATE + " INTEGER,"
      + NoteColumns.MODIFIED_DATE + " INTEGER"
      + ");");
  }
  // The code above will create this where COLid is the value of
  // NoteColumns._ID at runtime
  // CREATE TABLE notes (COLid INTEGERPRIMARYKEY, title TEXT,
  //   note TEXT, created INTEGER, modified INTEGER);

  @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 notes");
    onCreate(db);
  }
}
private DatabaseHelper mOpenHelper;

The line shown in bold is easily missed. It’s used throughout the example code in this file: It’s the local version of SQLiteOpenHelper. With this structure, if you reuse this code, you can make changes within DatabaseHelper (e.g., your own string names) while doing very little to the rest of the code.

Next is code from the example that uses the boilerplate code you’ll reuse. Note the use of mOpenHelper which is DatabaseHelper. As you can see, it builds on what has already been constructed. If you examine the full code in the example, you’ll see that most of the code deals with the data to be stored rather than the SQLite interface.

The code in bold is the code that actually updates the database. The db.insert method constructs the necessary SQLite syntax using the static values described previously in this chapter. The code directly below the bold code takes the return value from db.insert and puts it into a local variable called rowId. That’s exactly what it is—the unique id of the new row. If the process fails, -1 is returned.

@Override
public Uri insert(Uri uri, ContentValues initialValues) {
  // Validate the requested uri
  if (sUriMatcher.match(uri) != NOTES) {
    throw new IllegalArgumentException("Unknown URI " + uri);
  }
  ContentValues values;
  if (initialValues != null) {
    values = new ContentValues(initialValues);
  } else {
    values = new ContentValues();
  }

  Long now = Long.valueOf(System.currentTimeMillis());
  // Make sure that the fields are all set
  if (values.containsKey(NoteColumns.CREATED_DATE) == false) {
    values.put(NoteColumns.CREATED_DATE, now);
  }
  if (values.containsKey(NoteColumns.MODIFIED_DATE) == false) {
    values.put(NoteColumns.MODIFIED_DATE, now);
  }
  if (values.containsKey(NoteColumns.TITLE) == false) {
    Resources r = Resources.getSystem();
    values.put(NoteColumns.TITLE, r.getString(android.R.string.untitled));
  }
  if (values.containsKey(NoteColumns.NOTE) == false) {
    values.put(NoteColumns.NOTE, “”);
  }

  SQLiteDatabase db = mOpenHelper.getWritableDatabase();
  long rowId = db.insert(NOTES_TABLE_NAME, NoteColumns.NOTE, values);
  if (rowId > 0) {
     Uri noteUri = ContentUris.withAppendedId(NoteColumns.CONTENT_URI, rowId);
     getContext().getContentResolver().notifyChange(noteUri, null);
     return noteUri;
  }
  throw new SQLException("Failed to insert row into " + uri);
}

Summary

This chapter shows you the basics of working with the SQLite built-in database in Android. Although there’s a lot of code in the example shown here, only a few lines of code are actually what you need to write for the SQLite interface. When you reuse the code for your own purposes, most of the code you write will be related to your own app and its data rather than to the database.