How to implement complex queries using a Content Provider?

Question

I am asking this because I am not quite sure of how to work with Android Content Providers. I have a subset of my database with 8 tables and I need to create complex queries to get some of the data. My content provider works fine with simple queries. For example, I have a table Person on my PersonModel.java class and I get the data using:

String [] projection = {PersonModel.C_FIRST_NAME, PersonModel.C_LAST_NAME};
Cursor cursor = context.getContentResolver().query(
            MyProvider.CONTENT_URI_PERSONS, projection, null,
            null, null);

and it works perfectly.

On MyProvider I have a bunch of CONTENT_URI constants, on for each of my tables.

public class MyProvider extends ContentProvider {
    MyDbHelper dbHelper;
    SQLiteDatabase db;


    private static final String AUTHORITY = "com.myapp.models";

    //Paths for each tables
    private static final String PATH_PROFILE_PICTURES = "profile_pictures";
    private static final String PATH_PERSONS = "persons";
    private static final String PATH_USERS = "users";
    ....

    //Content URIs for each table
    public static final Uri CONTENT_URI_PROFILE_PICTURES = Uri
        .parse("content://" + AUTHORITY + "/" + PATH_PROFILE_PICTURES);
    public static final Uri CONTENT_URI_PERSONS = Uri.parse("content://"
        + AUTHORITY + "/" + PATH_PERSONS);
    public static final Uri CONTENT_URI_USERS = Uri.parse("content://"
        + AUTHORITY + "/" + PATH_USERS);
    ...


    private static final int PROFILE_PICTURES = 1;
    private static final int PROFILE_PICTURE_ID = 2;
    private static final int PERSONS = 3;
    private static final int PERSON_ID = 4;
    private static final int USERS = 5;
    private static final int USER_ID = 6;

    private static final UriMatcher sURIMatcher = new UriMatcher(
        UriMatcher.NO_MATCH);
    static {
    sURIMatcher.addURI(AUTHORITY, PATH_PROFILE_PICTURES, PROFILE_PICTURES);
    sURIMatcher.addURI(AUTHORITY, PATH_PROFILE_PICTURES + "/#",
            PROFILE_PICTURE_ID);
    sURIMatcher.addURI(AUTHORITY, PATH_PERSONS, PERSONS);
    sURIMatcher.addURI(AUTHORITY, PATH_PERSONS + "/#", PERSON_ID);
    sURIMatcher.addURI(AUTHORITY, PATH_USERS, USERS);
    sURIMatcher.addURI(AUTHORITY, PATH_USERS + "/#", USER_ID);
    ...
    }



  public Cursor query(Uri uri, String[] projection, String selection,
        String[] selectionArgs, String sortOrder) {

    // Uisng SQLiteQueryBuilder instead of query() method
    SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();

    // check if the caller has requested a column which does not exists
    //checkColumns(projection);

    int uriType = sURIMatcher.match(uri);

    switch (uriType) {
    case PROFILE_PICTURES:
        queryBuilder.setTables(ProfilePictureModel.TABLE_PROFILE_PICTURE);
        break;
    case PROFILE_PICTURE_ID:
        // Adding the ID to the original query
        queryBuilder.appendWhere(ProfilePictureModel.C_ID + "="
                + uri.getLastPathSegment());
    case PERSONS:
        queryBuilder.setTables(PersonModel.TABLE_PERSON);
        break;
    case PERSON_ID:
        // Adding the ID to the original query
        queryBuilder.appendWhere(PersonModel.C_ID + "="
                + uri.getLastPathSegment());
    case USERS:
        queryBuilder.setTables(UserModel.TABLE_USER);
        break;
    case USER_ID:
        // Adding the ID to the original query
        queryBuilder.appendWhere(UserModel.C_ID + "="
                + uri.getLastPathSegment());

    default:
        throw new IllegalArgumentException("Unknown URI: " + uri);

    }

    db = dbHelper.getWritableDatabase();
    Cursor cursor = queryBuilder.query(db, projection, selection,
            selectionArgs, null, null, sortOrder);
    // make sure that potential listeners are getting notified
    cursor.setNotificationUri(getContext().getContentResolver(), uri);

 }

That is a small part of my content provider. So my questions are:

1) How do I implement a rawQuery() in my content provider? or how do I use properly my queryBuilder?, let's say I want to execute this query using several tables, renaming them and also passing the p1.id as a parameter?

SELECT p1.first_name, p1_last_name 
FROM Person p1, Person P2, Relationship r 
WHERE p1.id = ? AND 
      p1.id = r.relative_id AND
      p2.id = r.related_id;

I tried so by doing this: On my query() method (shown above) I have a new case, called GET_RELATIVES:

case GET_RELATIVES:
        db = dbHelper.getWritableDatabase();
        queryBuilder.setTables(PersonModel.TABLE_PERSON + " p1, "
                + PersonModel.TABLE_PERSON + " p2, "
                + RelationshipModel.TABLE_RELATIONSHIP + " r");
        queryBuilder.appendWhere("p2."+PersonModel.C_ID + "=" + uri.getLastPathSegment());
        queryBuilder.appendWhere("p2."+PersonModel.C_ID + "=" + "r.related_id");
        queryBuilder.appendWhere("p1."+PersonModel.C_ID + "=" + "r.relative_id");

so I defined a new PATH, CONTENT URI and add it to the UriMatcher, like this:

private static final String PATH_GET_RELATIVES = "get_relatives";
public static final Uri CONTENT_URI_GET_RELATIVES = Uri
        .parse("content://" + AUTHORITY + "/"
                + PATH_GET_RELATIVES);
private static final int GET_RELATIVES = 22;

private static final UriMatcher sURIMatcher = new UriMatcher(
    UriMatcher.NO_MATCH);
static {
...
sURIMatcher.addURI(AUTHORITY, PATH_GET_RELATIVES, GET_RELATIVES);
}

but this does not seem to work so I think I'm probably defining something wrong on my content provider or inside the query method.

2) I am not quite sure what is the point on having for each table a constant called TABLE_ID and adding it to the switch-case. What is that used for? How do I call it?

Hope anyone can help me with this, thanks in advance!

Koger

Answer 1

I actually found the answer to my question in the most obvious place: the android documentation.

First Question: Implement a rawQuery. Did it like this:

Inside of my switch-case in the content provider I added a new URI, which for me is a JOIN between to tables, so I created a new ContentUri constant for it, a new ID, and registered it on the UriMatcher and then wrote the rawQuery. So MyProvider now looks a litte bit like this:

public class MyProvider extends ContentProvider {
...
// JOIN paths
    private static final String PATH_RELATIONSHIP_JOIN_PERSON_GET_RELATIVES = 
            "relationship_join_person_get_relatives";
...
public static final Uri CONTENT_URI_RELATIONSHIP_JOIN_PERSON_GET_RELATIVES = Uri
            .parse("content://" + AUTHORITY + "/"
                    + PATH_RELATIONSHIP_JOIN_PERSON_GET_RELATIVES);
...
    private static final int RELATIONSHIP_JOIN_PERSON_GET_RELATIVES = 21;
private static final UriMatcher sURIMatcher = new UriMatcher(
            UriMatcher.NO_MATCH);
    static {
...
//JOINS
        sURIMatcher.addURI(AUTHORITY, PATH_RELATIONSHIP_JOIN_PERSON_GET_RELATIVES + "/#",
                RELATIONSHIP_JOIN_PERSON_GET_RELATIVES);
...

public Cursor query(Uri uri, String[] projection, String selection,
            String[] selectionArgs, String sortOrder) {

        // Uisng SQLiteQueryBuilder instead of query() method
        SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();

        // check if the caller has requested a column which does not exists
        //checkColumns(projection);

        int uriType = sURIMatcher.match(uri);

        switch (uriType) {
        ...
        case RELATIONSHIP_JOIN_PERSON_GET_RELATIVES:
            db = dbHelper.getWritableDatabase();
            String[] args = {String.valueOf(uri.getLastPathSegment())};
            Cursor cursor = db.rawQuery(
                    "SELECT p1.first_name, p1.last_name " +
                    "FROM Person p1, Person p2, Relationship r " +
                    "WHERE p1.id = r.relative_id AND " +
                    "p2.id = r.related_id AND " + 
                    "p2.id = ?", args);
            cursor.setNotificationUri(getContext().getContentResolver(), uri);
            return cursor;
        ...
}

And to call the query() method and pass the id ad a parameter I did this in my controller:

String[] projection = { PersonModel.C_FIRST_NAME,
                PersonModel.C_LAST_NAME };
        Cursor cursor = context.getContentResolver().query(
                ContentUris.withAppendedId(
                        AkdemiaProvider.CONTENT_URI_RELATIONSHIP_JOIN_PERSON_GET_RELATED, id), 
                        projection, null, null, null);

Second question: Having the TABLE_ID constant is useful to have a query for each table passing an id as a parameter, I didn't know how to call the query method passing such id and this is how the Android Developer Documentation explains how to do so using ContentUris.withAppendedId

// Request a specific record.


Cursor managedCursor = managedQuery(
                ContentUris.withAppendedId(Contacts.People.CONTENT_URI, 2),
                projection,    // Which columns to return.
                null,          // WHERE clause.
                null,          // WHERE clause value substitution
                People.NAME + " ASC");   // Sort order.

I you guys want to see the whole documentation go to this link.

Hope this helps to anyone else having the same problem to understand ContentProvider, ContentUris and all that :)

Koger

You are awesome, Saved my Day, Made My Day. – Semang

@Carla Stabile what if I need to pass string to do Join for multiple tables having same string as field. – Anthropometry

Answer 2

Below code worked for me. Inside your Application's Content Provider:

public static final String PATH_JOIN_TWO_TABLES = "my_path";

    public static final Uri URI_JOIN_TWO_TABLES =
            Uri.parse("content://" + AUTHORITY + "/" + PATH_JOIN_TWO_TABLES);

    private static final int ID_JOIN_TWO_TABLES = 1001;

    private static final UriMatcher sURIMatcher = new UriMatcher(
            UriMatcher.NO_MATCH);

    static {
        sURIMatcher.addURI(AUTHORITY,
                PATH_JOIN_TWO_TABLES + "/#", ID_JOIN_TWO_TABLES );
    }

    @Nullable
    @Override
    public Cursor query(@NonNull Uri uri, String[] projection, String selection,String[] selectionArgs,
                        String sortOrder, CancellationSignal cancellationSignal) {

        int uriType = sURIMatcher.match(uri);
            switch (uriType) {

                case ID_JOIN_TWO_TABLES:
                    return getWritableDatabase()
                            .rawQuery("select * from " +
                                    "table_one" + " LEFT OUTER JOIN "
                                    + "table_two" + " ON ("
                                    + "table_one.ID"
                                    + " = " + "table_two.id" + ")", null);
            }
        return super.query(uri, projection, selection, selectionArgs, sortOrder, cancellationSignal);
    }

And while making the Query inside your Activity or Fragment:

 Cursor cursor = getActivity().getContentResolver()
                .query(ContentUris.withAppendedId(MYContentProvider.URI_JOIN_TWO_TABLES, MyContentProvider.ID_JOIN_TWO_TABLES), null, null, null, null);

Hope it works for you.

Nicobarese

I fail to see the difference between your answer and the one from the OP dated Aug 9 '14 https://mcmap.net/q/995186/-how-to-implement-complex-queries-using-a-content-provider Am I missing something ? – Rahman

Answer 3

For simple queries use selectionArgs in ContentProvider. It works like below

String[] args = { "first string", "[email protected]" };
Cursor cursor = db.query("TABLE_NAME", null, "name=? AND email=?", args, null);

Having the TABLE_ID inside the to create a different queries for each table.

Refer following class for all multiple table in content providers

1. Vogella Tutorial 1
2. Vogella Tutorial 2
3. Best practices for exposing multiple tables using content providers in Android

Munn

Thank you for answering. I actually did the Vogella tutorials to make my content provider, but if you see there is no complex query there and that is my main problem. Do you have any idea of how to do that? – Koger

It has most of the code for queries that's need. Complex query is something that's your business logic. For that look for examples of either selectionArgs as I mentioned above or Look for examples for SqliteQueryBuilder. This link should help programcreek.com/java-api-examples/…. Please accept the answer if it helps. – Munn