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", "joe@example.com", 0); addUser("Mary Jones", "mary@example.com", 0); addUser("Sue Bloggs", "sue@example.com", 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! If you’ve liked this tutorial, please let me know by leaving your comments and feedback on Twitter or Google+.
Join the free Plymouth Software newsletter today to get news, tutorials, hints and tips delivered straight to your inbox.
If you like to test out the latest and greatest builds of