Android: Getting Started with Databases and ContentProviders Part 1

Clone the code from GitHub

Android provides powerful database functionality built on SQLite. If you're familiar with SQL from web development, you'll be very comfortable with SQLite. However, what you might find is that it's very easy to deal directly with the database (especially if you're coming from a framework like Rails or Symfony).

Whatever language or platform you're using, it's always good practice to build an abstraction layer between your app's code and its database. In this tutorial miniseries, we'll walk through building a simple app that uses a database abstraction class, MyDatabaseHelper to manage querying, inserting, updating and deleting records from a simple database. In future posts, we'll look at dealing with multiple tables and joins, and finally explore Android's own ContentProvider API to abstract our database in a standard way.

Getting started

Start with a new Android application project (I recommend trying out the new Android Studio preview, especially if you're just starting out with Android), and add the following files:

public class MyActivity extends Activity {

  private MyDatabaseHelper mDatabaseHelper;

  public void onCreate(Bundle savedInstanceState) {

    super.onCreate(savedInstanceState);

    mDatabaseHelper = new MyDatabaseHelper(this);

  }

}```

java// src/com/example/MyDatabaseHelper.java

public class MyDatabaseHelper extends SQLiteOpenHelper {

public static final String TABLE_USERS = "users";

public static final String COLID = BaseColumns.ID;

public static final String COL_NAME = "name";

public static final String COL_EMAIL = "email";

public static final String COLDOB = "dateof_birth";

private static final String DATABASENAME = "myapp.db";

private static final int DATABASE_VERSION = 1;

public MyDatabaseHelper(Context context) {

super(context, DATABASE_NAME, null, DATABASE_VERSION);

}

@Override

public void onCreate(SQLiteDatabase db) {

db.execSQL("CREATE TABLE " + TABLE_USERS + " ("

    + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"

    + COL_NAME + " TEXT NOT NULL,"

    + COL_EMAIL + " TEXT,"

    + COL_DOB + " INTEGER"

    + ");");

}

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS + ";");

onCreate(db);

}

public long insert(String tableName, ContentValues values) {

return getWritableDatabase().insert(tableName, null, values);

}

public int update(String tableName, long id, ContentValues values) {

String selection = COL_ID + " = ?";

String[] selectionArgs = {String.valueOf(id)};

return getWritableDatabase().update(tableName, values, selection, selectionArgs);

}

public int delete(String tableName, long id) {

String selection = COL_ID + " = ?";

String[] selectionArgs = {String.valueOf(id)};

return getWritableDatabase().delete(tableName, selection, selectionArgs);

}

}```

The advantage of this approach is that MyDatabaseHelper becomes a single point of access to the raw database (following good DRY principles). It also provides the opportunity to validate data before it is written to the database. For example, we have added a NOT NULL constraint on the name column. If we tried to save a record to the database without setting a value for name, our app would crash with a SQLiteConstraintException. Let's demonstrate this by adding a few users in our Activity, but leaving one of the names blank:

  public void onCreate(Bundle savedInstanceState) {

    // ...

    addUser(null, null, 0);

    addUser("Joe User", "[email protected]", 0);

    addUser("Mary Jones", "[email protected]", 0);

    addUser("Sue Bloggs", "[email protected]", 0);

  }

 private void addUser(String name, String email, long dateOfBirthMillis) {

    ContentValues values = new ContentValues();

    values.put(MyDatabaseHelper.COL_NAME, name);

    if (email != null) {

      values.put(MyDatabaseHelper.COL_EMAIL, email);

    }

    if (dateOfBirthMillis != 0) {

      values.put(MyDatabaseHelper.COL_DOB, dateOfBirthMillis);

    }

    mDatabaseHelper.insert(MyDatabaseHelper.TABLE_USERS, values);

  }

Running this code, your app will crash with the SQLiteConstraintException we expected:

  ERROR/SQLiteDatabase(19185): Error inserting name=null

      android.database.sqlite.SQLiteConstraintException: users.name may not be NULL (code 19)

      at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)

      at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:775)

      at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)

      ....

Adding Validations

Using our abstract Database helper class though, we can protect against this and handle the error much more elegantly. To do this, we'll add some simple validations at the appropriate points in MyDatabaseHelper:

  public long insert(String tableName, ContentValues values) throws NotValidException {

    validate(values);

    return getWritableDatabase().insert(tableName, null, values);

  }

  public int update(String tableName, long id, ContentValues values) throws NotValidException {

    validate(values);

    String selection = COL_ID + " = ?";

    String[] selectionArgs = {String.valueOf(id)};

    return getWritableDatabase().update(tableName, values, selection, selectionArgs);

  }

  // ...

  protected void validate(ContentValues values) throws NotValidException {

    if (!values.containsKey(COL_NAME) || values.getAsString(COL_NAME) == null || values.getAsString(COL_NAME).isEmpty()) {

      throw new NotValidException("User name must be set");

    }

  }

  public static class NotValidException extends Throwable {

    public NotValidException(String msg) {

      super(msg);

    }

  }```

Back in `MyActivity`, wrap the call to `insert()` in a `try...catch` block to capture the `NotValidException` and show a message to the user:

java// src/com/example/MyActivity.java

private void addUser(String name, String email, long dateOfBirthMillis) {

// ...

try {

  mDatabaseHelper.insert(MyDatabaseHelper.TABLE_USERS, values);

} catch (MyDatabaseHelper.NotValidException e) {

  Toast.makeText(this, e.getMessage(), Toast.LENGTH_SHORT).show();

}

}```

Now if you run your app, it will gracefully handle the error, and continue adding the other user records to the local database.

Querying and Displaying Records

At the moment, we can't see our data though. We'll add a quick ListView to MyActivity to show the list of user's. If one doesn't already exist, create a new layout resource file in your project: res/layouts/my_activity.xml and add a ListView to fill the screen:

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

              android:orientation="vertical"

              android:layout_width="match_parent"

              android:layout_height="match_parent">

    <ListView

        android:layout_width="fill_parent"

        android:layout_height="match_parent"

        android:id="@+id/listView"/>

</LinearLayout>

In MyActivity, make sure that you set the content view to be the new my_activity.xml layout:

  public void onCreate(Bundle savedInstanceState) {

    super.onCreate(savedInstanceState);

    setContentView(R.layout.my_activity);

    // ...

  }```

Next, create a `SimpleCursorAdapter` to retrieve data from the database and display it in our new `ListView`:

java// src/com/example/MyActivity.java

public void onCreate(Bundle savedInstanceState) {

// ...

Cursor c = mDatabaseHelper.query(MyDatabaseHelper.TABLE_USERS, MyDatabaseHelper.COL_NAME);

String[] from = new String[]{MyDatabaseHelper.COL_NAME, MyDatabaseHelper.COL_EMAIL};

int[] to = { android.R.id.text1, android.R.id.text2 };

SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, android.R.layout.simple_list_item_2, c, from, to, 0);

ListView listView = (ListView) findViewById(R.id.listView);

listView.setAdapter(adapter);

// ...

}

Finally, you'll see that we've called a new method, `query()`, on our database helper. The parameters specify the table to query, and how to sort results. This will provide a simple way to retrieve the records from our database, wrapping the `SQLiteOpenHelper`'s own `query()` method. Add the new method to `MyDatabaseHelper`:

java // src/com/example/MyDatabaseHelper.java

public Cursor query(String tableName, String orderedBy) {

String[] projection = {COL_ID, COL_NAME, COL_EMAIL, COL_DOB};

return getReadableDatabase().query(tableName, projection, null, null, null, null, orderedBy);

}```

Of course, we could add other parameters such as search terms to our query(), making it very powerful - and again providing a single point of contact for querying our database.

Running the code, you'll now see the list of names and email addresses as people are added to the database. Note that every time you re-run the app, another set of records will be added as the calls to addUser() are in the activity's onCreate() method.

Screenshot of App Running

As you can see, it's relatively easy to build a powerful abstraction layer between your database and application code, and there are huge advantages in doing so. As your app grows, you'll start to build a library of common methods that will save you time and make your code more robust.

Coming Next

In the next tutorial, we'll add another table to our database and adapt our database helper class to handle multiple tables and joins. In the final part, we'll look at Android's ContentProvider API, which provide a standard way to abstracting and manage access to your your app's raw database.

Thanks for reading! If you've liked this tutorial, please let me know by leaving your comments and feedback on Twitter or Google+.

Get notified when I publish new tutorials and articles Topics include Android, Rails, iOS and Docker. Delivered straight to your inbox. Unsubscribe anytime.