The right way to do databases in Android

Feel free to comment below on what a loser I am. I am sitting in a cabin outside of Yellowstone National Park, writing a blog entry about doing databases on Android. However, I do think I should get a little geek cred as I am posting it from an EDGE connection on T-Mobile across the FroYo wireless tether. But, anyway...

When I first started working with Android, I found a simple tutorial on how to build SQLite databases. A few weeks ago, I was trying to track down the information again for project for my day job. After searching a bit, I was unable to find the original tutorial, but I found one that was a little better. The newer tutorial pointed me toward a much better way to handle databases.

In the Android SDK is a class called SQLiteOpenHelper. This class has two methods that Eclipse will force you to implement. The first is onCreate(), the second is onUpdate().

The onCreate() method is automagically called when you instantiate your descendant class, and the target database doesn't exist. In this method, you should issue all of the necessary SQLite commands to create the tables in your database. But, hold on a second. How can you put tables in the database when you didn't specify the database name? Simple, when the class is instantiated, the database name is passed in. The SQLiteOpenHelper class then takes care of creating the database for you, before the onCreate() method is called.

The onUpdate() method is also really handy and cool. When you instantiate the descendant class, you also pass in a database version number. If the version number you pass in is higher than the version number when the database was created, the onUpdate() method is called with version number stored in the database, and the version number presented when the class is instantiated. onUpdate() is responsible for issuing the correct SQLite commands needed to bring the database up to the schema version specified when the class was instantiated.

Okay, so blah, blah, blah.. Sample code!?

public class DBOpenHelper extends SQLiteOpenHelper {

    private final String MY_DATA_TABLE = "t_Data";
    private final String MY_DATA2_TABLE = "t_Data2";
 
 public DBOpenHelper(Context context, String name, CursorFactory factory,
   int version) {
  super(context, name, factory, version);
 }

 @Override
 public void onCreate(SQLiteDatabase myDB) {

  /* Create a Table in the Database. */
  myDB.execSQL("CREATE TABLE IF NOT EXISTS "
      + MY_DATA_TABLE
      + " (mystring1 VARCHAR, mystring2 VARCHAR);");
  
  myDB.execSQL("CREATE TABLE IF NOT EXISTS "
    + MY_DATA2_TABLE
    + " (mystring3 VARCHAR);");
 }

 @Override
 public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
  // We don't use this just yet.
 }

}

Great! So the database gets created, but how do we actually use it?

Well, I am glad you asked. The SQLiteOpenHelper has methods that will return a handle to the database in either read only, or read/write flavors. So, if we wanted to use our open helper to create the database, and add a record to the second table, it would look something like this :

public void addDbData()
{
     DBOpenHelper dbHelper = new DBOpenHelper(this, "my_database", null, 1);
     SQLiteDatabase myDb = dbHelper.getWriteableDatabase();   // Open for write.

     // Now, use the normal SQLiteDatabase methods to work with the DB.
     myDb.execSQL("insert into " + MY_DATA2_TABLE + " set mystring3='Foo!';");
}

After you call addDbData(), you will now have a new database created with the name 'my_database', with schema version 1. The MY_DATA2_TABLE will also contain a single record containing the string "Foo!".

Two things worth pointing out in the addDbData() method. First, if you only want to read data from your database, you can change "getWriteableDatabase()" to "getReadableDatabase()". Second, always be sure you close your database after you are finished with it! The Java garbage collector won't do it for you, and you will get leak errors sent to your logcat console.

I am not going to get in to the update method of the SQLiteOpenHelper. I leave that as an exercise for you.