Android – Sqlite database with ContentProvider and LoaderManager

If you follow my previous posts, you may already know that we discussed how to make an application with database in Android before in Android, Dependency Injection (IOC) with roboguice and MVVM (Model-View-ViewModel) pattern. Although the main topic in that article relates to Dependency Injection but we had also a working example which fully supports all CRUD actions for Sqlite database. However, in practice, it shouldn’t work like that because the synchronous read/write actions will block the user interface and your apps will freeze. When your database grows over time, the query time gets longer and you app will completely freeze during the query get executed. Therefore, in this post, I would like to make a sample for illustrating how a database application can work in asynchronous mode.

1. Prerequisites

Before getting started, be sure that you’ve already know what roboguice is and how it works. If not, please read this post first before continuing Android, Dependency Injection (IOC) with roboguice and MVVM (Model-View-ViewModel) pattern.
Here is not prerequisites, but if you have a slow Android emulator, you can find how to speed your Android emulator up at Android – How to speed up Android emulator?

2. User interface

The demo app is simple as it should be. We are going to make a “To Do” management app. The app allows us to compose text describing what we have to do or what task we have to complete. We can also edit and delete item when finishing. The demo app consists of only 2 activities: one for showing all “To Do” and one for composing/editing item.

2.1 All “To Do” items

All “To Do” activity contains only one single ListView control for showing all “To Do” items in list format. In order to reuse the control or to optimize the display format of our app on many devices (such as phones, tablets…), I suggest that we make our UI in a custom ListViewFragment and load it during run-time.

A Fragment represents a behavior or a portion of user interface in an Activity. You can combine multiple fragments in a single activity to build a multi-pane UI and reuse a fragment in multiple activities. You can think of a fragment as a modular section of an activity, which has its own lifecycle, receives its own input events, and which you can add or remove while the activity is running (sort of like a “sub activity” that you can reuse in different activities).(From Google)

Thank to dynamically loading, the design code of the activity contains almost nothing but a container for children control

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
              android:orientation="vertical"
              android:layout_width="fill_parent"
              android:layout_height="fill_parent"
        >
</LinearLayout>

All

The fragment will be added to activity during run-time by getSupportFragmentManager(), create a instance of Fragment and add to FragmentManager

FragmentManager fragmentManager = getSupportFragmentManager();
FragmentTransaction fragmentTransaction = fragmentManager.beginTransaction();
allToDoItemsFragment= new AllToDoItemsFragment();
fragmentTransaction.add(android.R.id.content,allToDoItemsFragment);
fragmentTransaction.commit();

A context menu is also available for edit/delete current selected “To Do” item.

Context menu for editing/deleting item

2.2 Single “To Do” item

Single “To Do” fragment allows user to compose new or edit a selected “To Do” item. This fragment contains only one simple EditText control.

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
              android:orientation="vertical"
              android:layout_width="match_parent"
              android:layout_height="match_parent">

    <EditText
            android:layout_width="fill_parent"
            android:layout_height="fill_parent"
            android:inputType="textMultiLine"
            android:ems="10"
            android:id="@+id/editTextToDoItem"/>
</LinearLayout>

Single

The Fragment will be also added on the fly

Intent intent = getIntent();
Integer currentToDoItemId = intent.getIntExtra(ToDoItem._ID, 0);
FragmentManager fragmentManager = getSupportFragmentManager();
FragmentTransaction fragmentTransaction = fragmentManager.beginTransaction();
newToDoItemsFragment = new NewToDoItemsFragment(currentToDoItemId);
fragmentTransaction.add(android.R.id.content,newToDoItemsFragment);
fragmentTransaction.commit();

3. Database

The default supported database system of Android is Sqlite. Android provides classes as well as APIs so that we can easily create a new database, update its structure and query data from it. In one application, you can have many databases as you want and it’s same for number of tables in a database. If you already have experience with MS SQL or MySQL before, then Sqlite is almost the same. There are, of course, differences between them but the basic queries are very similar. To use database in your Android app, you need to use 2 classes: SQLiteOpenHelper and SQLiteDatabase.

3.1 SQLiteOpenHelper

SQLiteOpenHelper is a helper class to manage database creation and version management. With this class, you can build up database structure, define data type of columns, add relationship to tables, apply changes and manage version. The class is defined by abstract, so to use it, you have create a subclass implementing onCreate(SQLiteDatabase), onUpgrade(SQLiteDatabase, int, int) and optionally onOpen(SQLiteDatabase). The class takes care of opening the database if it exists, creating it if it does not, and upgrading it as necessary. Database actions will be executed in transactions to make sure the database is always in a sensible state. In our demo, our derived SQLiteOpenHelper class looks like following

public class DbOpenHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "Dictionary.db";
    public static final int DATABASE_VERSION = 1;

    private DbOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public static DbOpenHelper instance;

    public static DbOpenHelper getInstance(Context context)
    {
        if (instance == null)
        {
            instance = new DbOpenHelper(context.getApplicationContext());
        }
        return instance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //To change body of implemented methods use File | Settings | File Templates.
        db.execSQL(new Migration_0001().getSQLQuery());
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //To change body of implemented methods use File | Settings | File Templates.
        switch (newVersion)
        {

        }
    }
}

In derived class, we override only 2 functions onCreate and onUpgrade. The onCreate event will be triggered when Android checks if our application requires a database which still doesn’t exist. In this event handler, we normally define the first version of our database structure. In our demo, the Migration_0001 just contains one simple query to create a table for “To Do” item.

public class Migration_0001 extends Migration {
    public Migration_0001() {
        steps.add("CREATE TABLE " + ToDoItem.TABLE + " (" + ToDoItem._ID + " INTEGER PRIMARY KEY, " + ToDoItem.DESCRIPTION + " TEXT, " + ToDoItem.ISFINISHED + " SHORT)");
    }
}

public class Migration {
    protected List<String> steps;

    public Migration() {
        this.steps = new ArrayList<String>();
    }

    public String getSQLQuery()
    {
        StringBuilder result = new StringBuilder();
        for(String step:steps)
        {
            result.append(step + ";" + CoreConstants.ENVIRONMENT_NEWLINE);
        }
        return result.toString();
    }
}

When we extend our apps and want to add more tables as well as columns into database. Just increase the DATABASE_VERSION value, handle the switch statement in onUpgrade() and execute the appropriate SQL queries.

3.2 SQLiteDatabase

We have created the database structure with SQLiteOpenHelper but how can we connect to and use it? SQLiteDatabase is the key. SQLiteDatabase exposes methods to manage a SQLite database. It has methods to create, delete, execute SQL commands, and perform other common database management tasks. That means, the SQLiteOpenHelper is the deepest level where you can interact directly with database structure through SQL queries. You should also only apply changes to your database structure in this level. SQLiteDatabase is one level above SQLiteOpenHelper, which gives us an instance of database and provides some predefined actions so that we can easily work with the databases without writing complex SQL queries. Therefore, in our database context class, we need an instance of SQLiteOpenHelper to create an instance of SQLiteDatabase.

public class DbContext {
    private SQLiteOpenHelper dbHelper;

    public DbContext(Context context) {
        dbHelper = DbOpenHelper.getInstance(context);
    }

    public SQLiteDatabase open()
    {
        return dbHelper.getWritableDatabase();
    }
}

Then, for any CRUD actions, we just need to create an instance of DbContext, open connection to database and make the queries with predefined functions.

4. LoaderManager and ContentProvider

In previous section we defined our user interface and database. In this section we will discuss how to load database asynchronously so that we don’t block the UI when querying database. To accomplish this requirement, we have to combine 2 APIs: LoaderManager and ContentProvider.

4.1 LoaderManager

The LoaderManager API was introduced in Honeycomb, the interface associated with an Activity or Fragment for managing one or more Loader instances associated with it. This helps application to manage long-running operations in conjunction with the Activity or Fragment life-cycle. In our demo app, we also attached a LoaderManager to our “All To Do” fragments. In this activity we will load all “To Do” items, the loading progress can be pretty slow. So we use LoaderManager for managing this long-running actions.

public class AllToDoItemsFragment extends RoboListFragment implements android.support.v4.app.LoaderManager.LoaderCallbacks<Cursor> {

    private SimpleCursorAdapter simpleCursorAdapter;
    private int currentToDoItemId;
    @Override
    public void onActivityCreated(Bundle savedInstanceState) {
        super.onActivityCreated(savedInstanceState);
        getLoaderManager().initLoader(0,null, this);
        registerForContextMenu(getListView());
    }

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        simpleCursorAdapter = new SimpleCursorAdapter(getActivity(), android.R.layout.simple_list_item_1,null,new String[]{ToDoItem.DESCRIPTION},new int[]{android.R.id.text1},0);
        setListAdapter(simpleCursorAdapter);
    }


    @Override
    public android.support.v4.content.Loader<Cursor> onCreateLoader(int i, Bundle bundle) {
        return new CursorLoader(getActivity(), ToDoItem.CONTENT_URI, ToDoItem.PROJECTIONS_ALL,null,null,null );

    }

    @Override
    public void onLoadFinished(android.support.v4.content.Loader<Cursor> cursorLoader, Cursor cursor) {
        simpleCursorAdapter.swapCursor(cursor);


    }

    @Override
    public void onLoaderReset(android.support.v4.content.Loader<Cursor> cursorLoader) {
        simpleCursorAdapter.swapCursor(null);
    }
..
}

The code is pretty simple. When the activity is loaded, a new loader will be created or an existing one will be reconnected. When a new loader is created, we initialize a CursorLoader pointing to ContentProvider. The executed action in ContentProvider is exactly where our long-running operation runs. When this operation is finished, the onLoadFinished event will be fired and all we have to do is popup our data from CursorLoader to SimpleCursorAdapter and the list of “To Do” item will be loaded. In short, the LoaderManager just simply put the operation into a task, execute this task in another thread and notify us when the task finished. That’s all.

4.2 Content provider

We “store” our long-running task in LoaderManager and execute it but what does the task do? By calling

...
return new CursorLoader(getActivity(), ToDoItem.CONTENT_URI, ToDoItem.PROJECTIONS_ALL,null,null,null );
...
public static final String[] PROJECTIONS_ALL = {
		TABLE + "." + _ID,
		TABLE + "." +DESCRIPTION,
		TABLE +"."+ ISFINISHED
};

we are making a query to the ContentProvider. The argument Uri contains the location of ContentProvider and string array PROJECTIONS_ALL just to define which columns we would like to read the value out.

public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
	switch (URI_MATCHER.match(uri)) {
		case TODOITEMS:
			SQLiteQueryBuilder sqLiteQueryBuilder = new SQLiteQueryBuilder();
			sqLiteQueryBuilder.setTables(ToDoItem.TABLE);
			SQLiteDatabase sqLiteDatabase = dbContext.open();
			Cursor result = sqLiteQueryBuilder.query(sqLiteDatabase, projection, selection, selectionArgs, null, null, sortOrder);
			result.setNotificationUri(getContext().getContentResolver(), uri);
			return result;
		   
		default:
			throw new IllegalArgumentException("Unknown URI " + uri);
	}

}

When ContentProvider‘s query is called, the ContentProvider will query the database by executing the implemented function query(). When the database’s query finishes, we gives a Cursor of result back, which will be transferred to LoaderManager to notify the progress is complete. In summary, the complete progress works like following:

Activity loads LoaderManager –>
LoaderManager makes a query to ContentProvider –>
ContentProvider takes instance of DbContext –>
DbContext opens a connection to SQLiteDatabase –>
SQLiteDatabase initializes SQLiteOpenHelper, creates database if necessary, executes the queries and get Cursor as result –>
The Cursor will be pushed back to LoaderManager –>
LoaderManager notifies Activity for showing data.

This is complicated process but it allows us to make a query asynchronously to database and therefore we don’t freeze the GUI when database actions are running. The other insert/update/delete actions work in same way. You can take a look into the source code attached at the end of post to understand more how they work.

5. Conclusions

Finally, having a database for our own app is always a nice feature. Working with database in Android is also simple due to a lot of APIs provided by Google. The problem is how we can query data asynchronously, is answered in this post. I hope that you can write now a database application which won’t freeze your UI :). For more code details, you can download the source code from following link SQLiteDatabase demo app.

Leave a Reply

Your email address will not be published. Required fields are marked *