Is it a good practice to use rawQuery in ContentProvider?

Question

I am using my custom ContentProvider to communicate with sqlite database. I would like to display on a list (using ListFragment), data that comes from two tables (with many to many relation). The only solution I can think of for such case is to use rawQuery. And the questions is, if it is a good practice, or should I solve this in some other way?

Example of tables:

Table A: ID, COLUMN_FROM_A

Table B: ID, COLUMN_FROM_B

Joining table AB: ID, FK_ID_A, FK_ID_B

Example of overridden query method in ContentProvider:

@Override
public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
    SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
    Cursor cursor = null;
    int uriType = URIMatcher.match(uri);
    switch (uriType) {
        case TABLE_A_URI:
            queryBuilder.setTables("TABLE_A");
            cursor = queryBuilder.query(databaseHelper.getReadableDatabase(), projection, selection, selectionArgs, null, null, sortOrder);
            break;
        case TABLE_B_URI:
            queryBuilder.setTables("TABLE_B");
            cursor = queryBuilder.query(databaseHelper.getReadableDatabase(), projection, selection, selectionArgs, null, null, sortOrder);
            break;
        case TABLE_JOIN_A_B_URI:
             cursor = databaseHelper.getReadableDatabase().rawQuery("select a.COLUMN_FORM_A, b.COLUMN_FROM_B from TABLE_A a, TABLE_B b, TABLE_AB ab where ab.FK_ID_A=a.ID and ab.FK_ID_B=b.ID", null);
            break;
        default:
            throw new IllegalArgumentException("Unknown URI");
    }

    cursor.setNotificationUri(getContext().getContentResolver(), uri);
    return cursor;
}

score 12 · Accepted Answer · answered Jul 20 '12 at 12:02

12

It's a good and common practice, very appropriate in this case.

I don't foresee any problems, we have used it in many apps.

answered Jul 20 '12 at 12:02

P Varga

19,174
12
70
108

1

The only better solution I can think of is a way of writing such query (using this cross table) without native SQL but using query method from queryBuilder, which in my example is used for single tables. I just haven't found such implementation and I'm not sure if this is even possible. – Piotr Gorajski Jul 20 '12 at 12:37
1

It is also possible, with for example `SQLiteQueryBuilder.setTables("foo LEFT OUTER JOIN bar ON (foo.id = bar.foo_id)")` – P Varga Jul 20 '12 at 14:35
1

Both above answers satisfy me very much. This is what I was looking for. Thank you. – Piotr Gorajski Jul 23 '12 at 08:09

Is it a good practice to use rawQuery in ContentProvider?

1 Answers1