Teo is a Software Engineer from Romania with experience in Java and C#.NET.  He is  a Google API Guru and Student Ambassador as a top 'Hall of Fame' contributor to the   Google Desktop  gadget gallery and knowledge base. He also writes free productivity tools for Android.  Among current research interests is the interface/functionality connection
in client-side apps and AR. More info at Teo's [Desktop and Mobile Software]

Using and Optimizing Databases on Android

06/28/2010 2:15 am By Teodor Filimon | Articles: 7

SQLite databases are the recommended way of storing quantitatively important data on the Android operating system. Because of the way user focus is organized on this platform, there are several kinds of contexts databases can be used in, e.g. activities (Activity class), services (Service class) or widgets. The first two inherit the Context abstract class, while context references are passed into the latter, but these, along with optimizations, will be discussed in the next sections.

  1. Introduction

Context is actually an interface to global information about the application environment, the implementation being provided by Android itself. The existence of Context references helps us determine their scope and even what we can do to optimize classes which are constructed using such references [1]. An Activity is, in a large sense, the logical unit of users' interaction with the phone. Usually, only one Activity can have focus [2]. A Service is similar, but it doesn't target the UI and is designed for longer-running operations; it's a long-lived component of an Android application [3].

SQLite is an easy to use database system; there are certain helper classes to aid in database creation and version management, such a class being SQLiteOpenHelper [4]. You have to keep in mind that your app's users might have different database versions when upgrading to the latest app version, so managing structural changes by implementing the onUpgrade(...) method is recommended.

2. Modelling & Creating the Database

Figuring out the best object-oriented model can depend on various factors. For example, you might want to focus on so called real-time usage needs, or you might want a trade-off with security features. Before explaining this choice, we first need to know how the database is created [5]:

  private class DatabaseHelper extends SQLiteOpenHelper {

    DatabaseHelper(Context context) {
      super(context, DB_NAME, null, DATABASE_VERSION);
      mCtx = context;
      res = context.getResources();

    public void onCreate(SQLiteDatabase db) {
      Resources r = res;

      // creating the tag and entry tables and inserting a default tag
      db.execSQL("CREATE TABLE " + DB_TAG_TABLE + " (" + KEY_ROWID
          + " integer primary key autoincrement, " + KEY_NAME
          + " text not null);");
      db.execSQL("INSERT INTO " + DB_TAG_TABLE + " (" + KEY_NAME
          + ") VALUES ('" + r.getString(R.string.tag1) + "')");

As can be seen above, actual SQL code is passed to the execSQL method of the wrapper class. In fact, this snippet of code already contains an important optimization: the general isolation of upgrade operations which can be replicated for future users without being explicitly included in the SQL creation code. Details about the advantages of a custom upgrade method will be discussed in the following sections.

Classes such as DatabaseHelper are usually wrapped in other classes, becoming an inner class of the wrapper. This way, other helper methods can be implemented around the database helper. Before it's actually used, it's important to know that an SQLiteDatabase will be doing the hard work. An instance of it can be optained from the mentioned database helper, in an open open method (context use can be optimized) [5]:

  public ToDoDB open() throws SQLException {
    mDbHelper = new DatabaseHelper(mCtx);
    mDb = mDbHelper.getWritableDatabase();
    return this;

Similarly, a closing method is needed - either for releasing resources or for freeing the database up for a lower level handling (more about the latter in the following sections) [5].

  public void close() {

But first an object model should be established. Some developers often execute their queries surrounded by the opening and closing methods. Thus, the default state, in that case, is a closed database. In theory, this increases security and modularity, also allowing for a permission system within a single app. This approach might be excusable with sparse accesses, but what is the CPU impact when these accesses occur periodically, or following every user action? Keep in mind that CPU activity translates not only into low response times but also into battery consumption on mobile phones. Therefore, if performance becomes an issue, and it rarely doesn't, an open state should be considered default. The database can then be opened in the onCreate or onResume events of the activity, and closed in onDestroy. Similar events could be implemented / overridden in services and widgets, but this raises a lifecycle issue. Database instances will be different in these cases and will only live as long as the contexts will, but their cohabitation could cause problems. The singleton pattern can be employed here: if our database wrapper becomes a singleton, it will only have one true instance, forcing references (and not instances) to have a short life. This also eliminates the ultimately redundant overhead of creating parallel instances which do the same thing.

However, a Context needs to be passed to the constructor, and passing short-lived contexts to long-lived singletons wouldn't be appropriate. This is why instead of using the local context, getApplicationContext() should be called on it; this will return the context of the single, global Application object of the current process. An instance of our own database wrapper would be obtained like this:

  sDbHelper = ToDoDB.getInstance(getApplicationContext());

And the public static getInstance function would look like this (the actual constructor should remain private, as it is a singleton):

  public static final ToDoDB getInstance(Context c) {
    return sInst != null ? sInst : (sInst = new ToDoDB(c).open());

This approach relies on Android to close the actual database, since explicitly closing it, for example, in a main activity, might prevent the app's service to use it, or other such combinations. This way, the database will be closed only if it isn't used anymore.

Fig. 1: Non-Singleton vs. Singleton approach

  3. Constraints

Some features might have certain constraints. For example, an app might offer the possibility to import a database from an external source. If this happens at a filesystem level, by overwriting files, the database will first have to be closed (released). After copying the files, it must be reopened, so it's available to the app again [5] (the copy method can be implemented using standard Java):

  public static final void importBackupSD(final Context c) {
    try {
      Utils.copy(new File("/sdcard/Tag-ToDo_data/database_backup"), new File(
    } catch (Exception e) {
      Utils.showDialog(R.string.notification, R.string.import_fail, c);
    sDbHelper = ToDoDB.getInstance(c);

Databases have, of course, different versions (e.g. when the app adds a feature, it might also need to change or add fields). This can be done in onUpgrade [5]:

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      // upgrade to db v74 (corresponding to app v1.2.0) or bigger;
      // 4 columns need to be added for entry dates (and other possible
      // future extra options).
      if (oldVersion < 74 && newVersion >= 74) {
        try {
          db.execSQL("ALTER TABLE " + DB_ENTRY_TABLE + " ADD "
              + KEY_EXTRA_OPTIONS + " INTEGER");
        } catch (Exception e) {
          // if we are here, it means there has been a downgrade and
          // then an upgrade, we don't need to delete the columns, but
          // we need to prevent an actual exception
        try {
          db.execSQL("ALTER TABLE " + DB_ENTRY_TABLE + " ADD " + KEY_DUE_YEAR
              + " INTEGER");
        } catch (Exception e) {
        try {
          db.execSQL("ALTER TABLE " + DB_ENTRY_TABLE + " ADD " + KEY_DUE_MONTH
              + " INTEGER");
        } catch (Exception e) {
        try {
          db.execSQL("ALTER TABLE " + DB_ENTRY_TABLE + " ADD " + KEY_DUE_DATE
              + " INTEGER");
        } catch (Exception e) {

      // upgrade to db v75 (corresponding to app v1.3.0) or bigger;
      // a column needs to be added for written notes
      if (oldVersion < 75 && newVersion >= 75) {
        try {
          db.execSQL("ALTER TABLE " + DB_ENTRY_TABLE + " ADD "
              + KEY_WRITTEN_NOTE + " TEXT");
        } catch (Exception e) {

However, certain SQLite exceptions might occur. For example, if a database with an older version has been imported, and the app, of a newer version, queries for nonexistent field, there will be a force-close (the app will crash). The code above is useful in this situation as well because it allows us to easily repair the database by forcing missing subsequent upgrades until the current version is reached; onUpgrade being a method, not just an event, can be called programatically and, furthermore, its signature allows for successive and independent calls. These calls can be grouped in a method implemented in our DatabaseHelper class:

    public void upgrade(SQLiteDatabase db){
      onUpgrade(db, 73, 74);
      onUpgrade(db, 74, 75);
      onUpgrade(db, 75, 76);
      onUpgrade(db, 76, 78);
      onUpgrade(db, 78, 79);
      onUpgrade(db, 81, 82);
      onUpgrade(db, 85, 86);
      onUpgrade(db, 91, 92);

Repairing the database can be done in 2 ways, one simpler and one more complicated. The simple way involves calling the upgrade method and ensuring it is called only once upon the exception, so as not to enter an infinite loop in case the DB version isn't the cause. This way, the database self-repairs based on its own design. A more full-proof way of handling such exceptions could be generating and executing SQL commands automatically based on the exception text, by extracting the names of problematic fields or tables and adding them to the data with a default value, but the extra effort in this second case might not be worth it.

  4. Flags

Using flags can be a straightforward design decision, but their advantages are worthwhile to mention. Flags usually have a passive nature, meaning that on change they don't execute a particular action. They can be a very useful tool to customize user interaction by affecting query returns (e.g. to-do list users might want to sort their tasks, some alphabetically, others by priority, etc., which would be done by optionally appending an 'ORDER BY' clause to the SQL code). Setters can be implemented in our own wrapper while its inner methods can access it directly.

5. Events & Synchronization

Analyzing the mentioned ideas from the synchronization point of view reveals a few differences as well. When the default state is closed, you can consider the system synchronous (even if minimally) because specific methods need to be called before and after accessing the database (e.g. open and close). In other words, the application can't unconditionally access at will, no matter the calling location, unless it opens the database first. With an open default state designed after the singleton pattern, this system is basically asynchronous, eliminating the necessity of calling the closing method and allowing any type of access from any location.

Although remotely related to databases from the documentation point of view, the connection with the UI and the way events propagate are very important, especially since some can be used as so called control signals. Events flow in a bottom up manner, and can be consumed (stopped) or allowed to continue along the propagation chain through the boolean return value, as seen in the following diagram:

Fig. 2: Event propagation

The way events can be used as control signals is translating user action into different use for the same UI elements. For example, a LinearLayout could be used to list all the tasks in a to-do list, but the same layout would be the best choice for enabling a task choice (e.g. moving a task under another task). Another example would be using a Spinner dialog for a similar double purpose: choosing tags from a list to show their content or choosing them to specify them as new hosts for previously selected tasks.

Fig. 3: Control signal example

Where preconditions and postconditions are concerned, there aren't any except the ones imposed by design in the actual access methods implemented by the developer in the wrapper. Also, there aren't any data type compatibility issues if parameters are properly included in the SQL or if Android's special classes are used [5]:

  final ContentValues args = new ContentValues();
              args.put(KEY_NOTE_IS_AUDIO, 1);
              db.update(DB_ENTRY_TABLE, args, KEY_NAME + " = '" + taskName
                  + "'", null);

This example updates a field which satisfies a certain condition with a new integer value.

6. Optimizations

One of the possible optimizations is using an abstract mother class for different kinds of tailored database wrappers. For example, in an alarm listener it might not make any sense to instantiate the entire wrapper with all its feaures; maybe a stripped down version would work better.

Another optimization is encoding multiple values into a single one. This can be done not only for security reasons, but for decreasing the amount of stored data (extra effort will be transfered to CPU). For example, a date - composed of year, month and day - can be encoded as an integer value:

  public int getDueDate(String task) {
    final Cursor entry = mDb.query(DB_ENTRY_TABLE, new String[] { KEY_ROWID,
        + " = '" + task + "'", null, null, null, null);
    // for now, assuming we have a task named like this
    final int e = 372 * entry.getInt(entry.getColumnIndex(KEY_DUE_YEAR)) + 31
        * entry.getInt(entry.getColumnIndex(KEY_DUE_MONTH))
        + entry.getInt(entry.getColumnIndex(KEY_DUE_DATE));
    return e;

You can then decode the needed part using the DIV or MOD operators (/ and % in Java). 

7. Conclusion

SQLite databases on Android are a modern way of managing data on phones. Because of the multiple purposes mobile devices have, wrapper and helper classes have been and can be created as part of the platform to ensure high quality data management. Also, the Android operating system is constantly evolving providing compiler and performance optimizations that improve database use even further.


[1] Android: Context [WWW] http://developer.android.com/reference/android/content/Context.html

[2] Android: Activity [WWW] http://developer.android.com/reference/android/app/Activity.html

[3] Android: Service [WWW] http://developer.android.com/reference/android/app/Service.html

[4] Android: SQLiteOpenHelper [WWW] http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html

[5] Filimon T.: Tag-ToDo-List [APP] http://code.google.com/p/tag-todo-list

Your rating: None Average: 3.5 (2 votes)