SQLite Database From Tutorial To Actual Use

The standard Android training site has a useful guide on getting started with the built-in SQLite database. It gives us as new developers the idea of where to start, and allows us to start writing code with it almost immediately.

However, this guide has a critical gap: it does not mention how much more there is more to using the database than those few sample lines. It does not mention where to go next to find out more. We can easily read the guide and come away with the false impression that this way to access the database is the end of the story… until everything breaks and we need to start googling or looking through the sample applications and find out how much more there is to using the database *correctly*.

Here are the missing steps between the guide and functioning code.

Missing Step #1: Manual Transaction Management

To anybody used to using @Transactional annotations, manual transaction management is not the first thing that comes to mind when coming to Android for the first time. Yet, transaction management is critical in this environment. Here is the code that you actually need to use. Think of this as a standard structure that you’ll need for all of your interactions with the database.

// we can begin the transaction outside of the try block
database.beginTransaction();
try {
  // database insert / update  / etc goes here
  database.setTransactionSuccessful();
}
catch(Exception ex) {
  // log or rethrow as you like
  // this dummy code is here to provide a convenient breakpoint
  String cause = ex.getMessage();
  throw ex;
}
finally {
  // always end the transaction in a finally block!
  // don't leave transactions hanging around!
  database.endTransaction();
}

Missing Step #2: Managing The Database Throughout Activity Lifecycle

In the Activity, the database is opened in the onCreate() method. Note that we must have an implementation of SQLiteOpenHelper per the SQLite guide, and that the Context it takes can actually be the Activity since Activity extends Context.

@Override
protected void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  SQLiteOpenHelper helper = new MyDatabaseOpenHelper(this);
  database = helper.getWritableDatabase();
}

Correspondingly, in the Activity, the SQLiteDatabase that we obtained from SQLiteOpenHelper.getWritableDatabase() is an open resource that must be closed when the activity is destroyed.

@Override
public void onDestroy() {
  super.onDestroy();
  database.close();
}

Conclusion

That’s it! With these extra steps to supplement the official guide, we can write fully functioning code against the SQLite database.

For more information: the official sample apps do include some sample applications that use a database. Presumably BasicSyncAdapter is the best option at this point (the others being in a “legacy” area).

SDK/samples/android-19/legacy/NotePad
SDK/samples/android-19/legacy/SearchableDictionary
SDK/samples/android-19/legacy/ApiDemos
SDK/samples/android-19/connectivity/BasicSyncAdapter/BasicSyncAdapterSample

Even with functioning code, there is more to learn about how to use it *better*, such as how to open databases in a background thread to improve performance, and a full analysis of using an Android ORM library. For now: enjoy using the database on your Android device!

Advertisements

Leave a comment

Filed under Android, Software Engineering

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