Android Advanced Cursors

If you have been doing Android development for even a little while, you have probably used the provided SQLite functionality to store and retrieve data in a local database on the Android device.

If not, you can head over to Lars Vogel's excellent tutorial website to see how it all works, or to get a refresher.

For many applications, the basic Cursor is all you need. But what about more advanced requirements? What follows is an overview of some of the more advanced cursor options.

CursorJoiner

If you need data from two or more database tables, the most straightforward thing to do is to use a SQL query that joins the two tables. However, sometimes you already have sources for two separate queries (such as a CursorLoader based on a ContentProvider), and you may wish to just re-use them, without creating a new query (without, e.g., modifying the ContentProvider).

This is where CursorJoiner comes in handy. Unlike the other options listed below, CursorJoiner doesn't produce a new cursor. Instead, it provides an Iterable interface over the joined cursors.

If the only reason that you want to join the two tables is to produce a Cursor, say, for use by a CursorAdapter, then you should probably just stick with writing SQL that joins the two tables. However, if you just want to iterate the data once, then CursorJoiner can be very helpful.

One of the more interesting uses that I've seen for CursorJoiner is for determining what has changed when a LoaderManager.LoaderCallbacks<Cursor> implementation gets an updated Cursor in it's onLoadFinished method.

You can use the CursorJoiner to join the 'old' cursor and the 'new' cursor on their primary key, and then compare them. As you iterate the CursorJoiner, you get a CursorJoiner.Result enum that indicates whether the current row exits in the left cursor, the right cursor, or both.

Here is some sample code that determines what has been added to or deleted from a ContentProvider as a CursorLoader driving a CursorAdapter is updated:

01

@Override

02	public void onLoadFinished(Loader<cursor> loader, Cursor updatedCursor) {

03	if(adapter.getCursor() != null) {

04	Cursor oldCursor = adapter.getCursor();

05	CursorJoiner joiner =

06	new CursorJoiner(oldCursor,

07	new String[] {Thing.ID},

08	updatedCursor,

09	new String[] {Thing.ID}

10

);

11

12	for(CursorJoiner.Result joinerResult : joiner) {

13	switch (joinerResult) {

14

15	case RIGHT:

16	int thingId = updatedCursor.getInt(

17	updatedCursor.getColumnIndex(Thing.ID)

18

);

19

20	String thingName = updatedCursor.getString(

21	updatedCursor.getColumnIndex(Thing.NAME)

22

);

23

24	Log.d(TAG, "new Thing added - id: " + thingId

25	+ ", name: " + thingName);

26

break;

27

28	case LEFT:

29	int thingId = updatedCursor.getInt(

30	oldCursor.getColumnIndex(Thing.ID)

31

);

32

33	String thingName = updatedCursor.getString(

34	oldCursor.getColumnIndex(Thing.NAME)

35

);

36

37	Log.d(TAG, "Thing deleted - id: "

38	+ thingId + ", name: " + thingName);

39

break;

40

41	case BOTH:

42	int thingId = updatedCursor.getInt(

43	oldCursor.getColumnIndex(Thing.ID)

44

);

45

46	String thingName = updatedCursor.getString(

47	oldCursor.getColumnIndex(Thing.NAME)

48

);

49

50	Log.d(TAG, "Thing unchanged - id: "

51	+ thingId + ", name: " + thingName);

52

break;

53

}

54

}

55	updatedCursor.moveToFirst();

56	adapter.swapCursor(updatedCursor);

57

}

MatrixCursor

MatrixCursor is useful if you have a collection of data that is not in the database, and you want to create a cursor for it.  You simply construct it with an array of column names, and optionally an initial capacity. Then, you can add one row at a time to it by passing either an array of objects or an Iterable to its addRow() method. Then use it like any other cursor:

1	String[] columnNames = {"Column1", "Column2"};

2	MatrixCursor matrixCursor = new MatrixCursor(columnNames);

3	matrixCursor.addRow(new String[]{"value1", "value2"});

4	adapter.swapCursor(matrixCursor);

MergeCursor

MergeCursor allows you to present two or more cursors as a single cursor. You could get almost the same effect by doing a sql UNION query. However, MergeCursor allows you to have different columns in the various cursors, and still merge them.

This is very useful if you are presenting a list of heterogeneous items. I've used it with Emil Sjölander's StickyListHeaders library, using a different cursor for each 'section', all presented to the StickyListHeader implementation as a single cursor via a MergeCursor.

One thing to be aware of when presenting heterogeneous items through a MergeCursor - you'll probably want to add a pseudo-column to each of the cursors to make it easy for your ListAdapter (or whatever will be using the cursor) to be able to determine which type of data it's dealing with on each row. A seemingly little-known trick here is that you can include literal columns when creating a cursor using the ContentProvider's query method (and similar methods in other database classes). Just include the literal in the projection parameter to the query:

1	String projection = {Thing.ID, Thing.NAME, "'TYPE_THING' as TYPE"};

2	Cursor c = qBuilder.query(mDb,

3	projection,

4	selection,

5	selectionArgs,

6

groupBy,

7

having,

8	sortOrder);

CursorWrapper

Finally, CursorWrapper is a Wrapper class for Cursor that delegates all of its calls to the actual cursor object. As the documentation stated, the primary use for this class is to extend a cursor while overriding only a subset of its methods.

One example for the use of CursorWrapper is when you already have a cursor, but only want to present the first few rows of the cursor to a CursorAdapter. Override the getCount() method to return a number smaller than the actual cursor's count, and the adapter will only present that number of results. This is similar to what you would have gotten had you added a LIMIT clause to the sql that produced the cursor.

With this collection of advanced Cursor tricks, you should be able to accomplish whatever you want with Cursors and Adapters.