Sunday, December 2, 2012

CursorJoiner and MatrixCursor

Sometimes You may want to join two database cursors together (for example, You're using a CursorAdapter to bind data to a ListView, but Your adapter needs data from additional database tables and You're unable to do a JOIN query).

Android offers few ways to join two cursors, they are explained here - http://stackoverflow.com/questions/5701545/when-to-use-cursorjoiner-matrixcursor-mergecursor

I needed to join two cursors horizontally and had a very hard time to find any examples for doing it, so here we go -

Let's say we have two cursors that we want to join, the first thing to do is to create a new MatrixCursor for the result.

MatrixCursor m = new MatrixCursor(new String[] { "_id", "colA", "colB"} );

The next step is to create a CusorJoiner


CursorJoiner joiner = new CursorJoiner(cursorA, new String[] { "keyColA" },
                            cursorB, new String[] { "keyColB" });

where 'cursorA' and 'cursorB' are the two cursors that we want to join and 'keyColA' and 'keyColB' are the key columns that we want to join on, in cursorA and cursorB respectively.

The last thing to do is to iterate through the CursorJoiner results and decide what to do if the key exists in the left cursor, the right cursor or both:


for (CursorJoiner.Result joinerResult: joiner) {
    switch (joinerResult) {
        case LEFT:
            break;
                         
         case RIGHT:
             break;
                         
          case BOTH:
              break;
     }
}

What we want to do here is to add rows to our MatrixCursor, for example:


m.addRow(new Object[] {
                            1,
                            cursorA.getInt(cursorA.getColumnIndex("colA")),
                            cursorB.getString(cursorB.getColumnIndex("colB"))
                    });

In the end we get a MatrixCursor that is a result of our two joined cursors, which we can pass to a CursorAdapter and bind in a ListView.

Remember that we defined the MatrixCursor's columns names when creating the MatrixCursor so those names must be used later for accessing data in that cursor.

Please note that the cursors must already be sorted on the specified key columns in ascending order, according to CursorJoiner's documentation - "The cursors must already be sorted on each of the specified columns in ascending order. This joiner only supports the case where the tuple of key column values is unique."

another useful example - https://groups.google.com/forum/?fromgroups=#!topic/android-developers/Rw3ToXA4SOs