First steps with databases in Android

On Android most all of the examples out there assume you want to create and access your own database and will not to access an independent, preloaded database with your Android application. Not so for me. I’m working on writing a Bible or Scripture application for Android and would not even be remotely interested in creating the entire database from scratch when the application was initially started the first time. I did, however, find a sqlite database with the LDS Standard Works already created. So, all I have to do is create the program to query and display the scriptures. Eventually I’ll also add the ability to create bookmarks, highlight, and add notes to the scriptures, but first we’ll get the reading done. I also figure that some people may not want the LDS Standard Works, so I plan on distributing a Holy Bible version of the database. Easy enough, provided you can access the sqlite database in a place other than the default location for Android (/data/data/<package name>/databases)

Creating a connection to the database is pretty straight forward using the SQLiteDatabase and Cursor classes. In this case we are going to open the scriptures.db file which is on the root directory of the sdcard and we’re only going to read from it.

1
2

mDatabase = SQLiteDatabase.openDatabase("/sdcard/scriptures.db", null, 
      SQLiteDatabase.OPEN_READONLY);

What about getting information from the database? If you remember JDBC than the rest is very similar.

1
2
3
4
5
6
7
8

Cursor cursor = mDatabase.query(BOOKS_TABLE_NAME, new String[] {"book_id", 
        "volume_id", "book_title", "book_title_short"}, 
        null, null, null, null, "book_id");
if (cursor != null) {
  while(cursor.moveToNext(){
    Log.i(TAG, cursor.getString(cursor.getColumnIndex("book_title")));
  }
}

The result? A list of all the books in the database printed to the Android CatLog. One problem with this: If you kill the emulator process and don’t exit out of it gracefully, you end up having to restart Eclipse in order to see future messages in the CatLog view when you run subsequent emulator sessions.

Those are the basics. There’s a lot more that you can do from there, with such helpers as the ListActivity, and the SimpleCursorAdapter, but that will get you started. Of course, eventually we’ll want to get rid of the hard coded location of the database, and instead let the user decide what database to use as well as set a number of other preferences, but we’ll save that for another day.