Android: Getting Started with Databases and ContentProviders Part 1
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:
// src/com/example/MyActivity.java
public class MyActivity extends Activity {
private MyDatabaseHelper mDatabaseHelper;
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
mDatabaseHelper = new MyDatabaseHelper(this);
}
}
// src/com/example/MyDatabaseHelper.java
public class MyDatabaseHelper extends SQLiteOpenHelper {
public static final String TABLE_USERS = "users";
public static final String COL_ID = BaseColumns._ID;
public static final String COL_NAME = "name";
public static final String COL_EMAIL = "email";
public static final String COL_DOB = "date_of_birth";
private static final String DATABASE_NAME = "my_app.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:
// src/com/example/MyActivity.java
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
:
// src/com/example/MyDatabaseHelper.java
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:
// 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:
// res/layout/my_activity.xml
<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:
// src/com/example/MyActivity.java
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
:
// 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
:
// 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.
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 - I hope you enjoyed this post. If you find it helpful and want to support further writing and tutorials like this one, please consider supporting my work with a coffee!
Support ☕️